Code: Select all
DECLARE @MonthList TABLE (
MonthNum tinyint,
MonthName varchar(50)
)
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (1, 'January')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (2, 'February')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (3, 'March')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (4, 'April')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (5, 'May')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (6, 'June')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (7, 'July')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (8, 'August')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (9, 'September')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (10, 'October')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (11, 'November')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (12, 'December')
SELECT @TargetYear TargetYear, ml.MonthName, SUM(ISNULL(md.MyValues, 0)) SUMS
FROM MyData md FULL OUTER JOIN @MonthList ml ON MONTH(md.EffectiveDate) = ml.MonthNum
WHERE YEAR(md.EffectiveDate) = @TargetYear
ORDER BY ml.MonthNum
The actual query is a more complex than that, but it's the general idea. Does anyone have a simpler way to create the list of months, possibly getting it into a single select statement? Any clue what the performance hit would be if I were to have a static table in my database instead of creating @MonthList? Thanks.