Multiplication Aggregate Function in Microsoft SQL

Microsoft SQL has many useful aggregate functions such as AVG, SUM, COUNT — but it doesn’t have a an aggregate function for multiplication. This type of aggregate function would be very useful for needs such as calculating a geometric average.

With a little math, it is possible to calculate a multiplication aggregate result. The following line of SQL is an example of an application of this technique for a field that contains percentages:

SELECT (EXP(SUM(LOG(COALESCE(1 + ([FIELD NAME]/100),1)))) – 1) * 100

The approach is not as efficient as a built-in function, but it may offer superior performance to alternatives.

Simple Date Table Script: MS SQL

This script creates a date table for Microsoft SQL 2008 and higher to be used for joins in certain types of date related queries. The script was based on this post but I made several changes including adding ISO week and ISO year fields, converting the key to a Unix Time calculation, and adding an “end of month” flag. The script is not particularly efficient, but it is only intended to be run once so that was not a concern.

Table Structure

CREATE TABLE [dbo].[DateLookup](
[UnixTime] [bigint] NOT NULL,
[DateFull] [datetime] NULL,
[USRegionFormat] [varchar](10) NULL,
[FullYear] [char](4) NULL,
[QuarterNumber] [tinyint] NULL,
[WeekNumber] [tinyint] NULL,
[WeekDayName] [varchar](10) NULL,
[MonthDay] [tinyint] NULL,
[YearDay] [smallint] NULL,
[WeekDay] [tinyint] NULL,
[MonthNumber] [tinyint] NULL,
[ISOYear] [smallint] NULL,
[ISOWeek] [tinyint] NULL,
[MonthName] [nvarchar](12) NULL,
[EndOfMonth] [bit] NULL,
PRIMARY KEY CLUSTERED
(
[UnixTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Script

DECLARE @Date DATETIME
SET @Date = '1/1/1990'

WHILE @Date < '1/1/2100'
BEGIN
INSERT INTO DateLookup
(
UnixTime,
DateFull,
FullYear,
QuarterNumber,
WeekNumber,
WeekDayName,
MonthDay,
YearDay,
USRegionFormat,
[WeekDay],
MonthNumber,
ISOWeek,
ISOYear,
[MonthName],
EndOfMonth
)
SELECT
CAST(DATEDIFF(MINUTE,{d '1970-01-01'}, @Date) AS bigint) * 60, --UnixTime
@Date, -- DateFull
YEAR(@Date), -- FullYear
DATEPART(qq, @Date), --QuarterNumber
DATEPART(ww, @Date), --WeekNumber
DATENAME(dw, @Date), --WeekDayName
DATEPART(dd, @Date), --MonthDay
DATEPART(dy, @Date), --YearDay
CONVERT(VARCHAR(10), @Date, 101), --CharacterDate
DATEPART(dw, @Date), --[WeekDay]
DATEPART(mm, @Date), --MonthNumber

DATEPART(ISO_WEEK,@Date), --ISOWeek

CASE --ISOYear
WHEN DATEPART(ISO_WEEK, @Date) = 1 and DATEPART(MONTH, @Date) = 12
THEN DATEPART(year, @Date) + 1
WHEN DATEPART(ISO_WEEK, @Date) = 53 and DATEPART(MONTH, @Date) = 1
THEN DATEPART(year, @Date) -1
ELSE DATEPART(year, @Date)
END,

DATENAME(month, @Date), --MonthName

CASE --EndOfMonth
WHEN CONVERT(VARCHAR(10), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Date)+1,0)), 101) = CONVERT(VARCHAR(10), @Date, 101)
THEN 1
ELSE 0
END

SET @Date = DATEADD(dd, 1, @Date)
END