April 2, 2012 at 6:11 am
So I've been working on this for a little while now and I just can't get it. Consider it a real big brain fart. I've simplified the problem with fewer tables but still able to duplicate it.
CREATE TABLE #calendar(
[calendarDate] [date] NOT NULL,
[calendarYear] [int] NOT NULL,
[calendarMonth] [int] NOT NULL,
[calendarMonthName] [varchar](15) NOT NULL,
[calendarDay] [int] NOT NULL,
[fiscalYear] [int] NULL,
[fiscalMonth] [int] NULL,
[dayOfWeekName] [varchar](10) NOT NULL,
[calendarDateDescription] [varchar](50) NULL)
INSERT INTO #calendar ([calendarDate], [calendarYear], [calendarMonth], [calendarMonthName], [calendarDay], [fiscalYear], [fiscalMonth], [dayOfWeekName], [calendarDateDescription])
SELECT CAST(0xCA340B00 AS Date), 2011, 10, N'October', 1, 2012, 1, N'Saturday', NULL
UNION ALL SELECT CAST(0xCB340B00 AS Date), 2011, 10, N'October', 2, 2012, 1, N'Sunday', NULL
UNION ALL SELECT CAST(0xCC340B00 AS Date), 2011, 10, N'October', 3, 2012, 1, N'Monday', NULL
UNION ALL SELECT CAST(0xCD340B00 AS Date), 2011, 10, N'October', 4, 2012, 1, N'Tuesday', NULL
UNION ALL SELECT CAST(0xCE340B00 AS Date), 2011, 10, N'October', 5, 2012, 1, N'Wednesday', NULL
UNION ALL SELECT CAST(0xCF340B00 AS Date), 2011, 10, N'October', 6, 2012, 1, N'Thursday', NULL
UNION ALL SELECT CAST(0xD0340B00 AS Date), 2011, 10, N'October', 7, 2012, 1, N'Friday', NULL
UNION ALL SELECT CAST(0xD1340B00 AS Date), 2011, 10, N'October', 8, 2012, 1, N'Saturday', NULL
UNION ALL SELECT CAST(0xD2340B00 AS Date), 2011, 10, N'October', 9, 2012, 1, N'Sunday', NULL
UNION ALL SELECT CAST(0xD3340B00 AS Date), 2011, 10, N'October', 10, 2012, 1, N'Monday', NULL
UNION ALL SELECT CAST(0xD4340B00 AS Date), 2011, 10, N'October', 11, 2012, 1, N'Tuesday', NULL
UNION ALL SELECT CAST(0xD5340B00 AS Date), 2011, 10, N'October', 12, 2012, 1, N'Wednesday', NULL
UNION ALL SELECT CAST(0xD6340B00 AS Date), 2011, 10, N'October', 13, 2012, 1, N'Thursday', NULL
UNION ALL SELECT CAST(0xD7340B00 AS Date), 2011, 10, N'October', 14, 2012, 1, N'Friday', NULL
UNION ALL SELECT CAST(0xD8340B00 AS Date), 2011, 10, N'October', 15, 2012, 1, N'Saturday', NULL
UNION ALL SELECT CAST(0xD9340B00 AS Date), 2011, 10, N'October', 16, 2012, 1, N'Sunday', NULL
UNION ALL SELECT CAST(0xDA340B00 AS Date), 2011, 10, N'October', 17, 2012, 1, N'Monday', NULL
UNION ALL SELECT CAST(0xDB340B00 AS Date), 2011, 10, N'October', 18, 2012, 1, N'Tuesday', NULL
UNION ALL SELECT CAST(0xDC340B00 AS Date), 2011, 10, N'October', 19, 2012, 1, N'Wednesday', NULL
UNION ALL SELECT CAST(0xDD340B00 AS Date), 2011, 10, N'October', 20, 2012, 1, N'Thursday', NULL
UNION ALL SELECT CAST(0xDE340B00 AS Date), 2011, 10, N'October', 21, 2012, 1, N'Friday', NULL
UNION ALL SELECT CAST(0xDF340B00 AS Date), 2011, 10, N'October', 22, 2012, 1, N'Saturday', NULL
UNION ALL SELECT CAST(0xE0340B00 AS Date), 2011, 10, N'October', 23, 2012, 1, N'Sunday', NULL
UNION ALL SELECT CAST(0xE1340B00 AS Date), 2011, 10, N'October', 24, 2012, 1, N'Monday', NULL
UNION ALL SELECT CAST(0xE2340B00 AS Date), 2011, 10, N'October', 25, 2012, 1, N'Tuesday', NULL
UNION ALL SELECT CAST(0xE3340B00 AS Date), 2011, 10, N'October', 26, 2012, 1, N'Wednesday', NULL
UNION ALL SELECT CAST(0xE4340B00 AS Date), 2011, 10, N'October', 27, 2012, 1, N'Thursday', NULL
UNION ALL SELECT CAST(0xE5340B00 AS Date), 2011, 10, N'October', 28, 2012, 1, N'Friday', NULL
UNION ALL SELECT CAST(0xE6340B00 AS Date), 2011, 10, N'October', 29, 2012, 1, N'Saturday', NULL
UNION ALL SELECT CAST(0xE7340B00 AS Date), 2011, 10, N'October', 30, 2012, 1, N'Sunday', NULL
UNION ALL SELECT CAST(0xE8340B00 AS Date), 2011, 10, N'October', 31, 2012, 1, N'Monday', NULL
UNION ALL SELECT CAST(0xE9340B00 AS Date), 2011, 11, N'November', 1, 2012, 2, N'Tuesday', NULL
UNION ALL SELECT CAST(0xEA340B00 AS Date), 2011, 11, N'November', 2, 2012, 2, N'Wednesday', NULL
UNION ALL SELECT CAST(0xEB340B00 AS Date), 2011, 11, N'November', 3, 2012, 2, N'Thursday', NULL
UNION ALL SELECT CAST(0xEC340B00 AS Date), 2011, 11, N'November', 4, 2012, 2, N'Friday', NULL
UNION ALL SELECT CAST(0xED340B00 AS Date), 2011, 11, N'November', 5, 2012, 2, N'Saturday', NULL
UNION ALL SELECT CAST(0xEE340B00 AS Date), 2011, 11, N'November', 6, 2012, 2, N'Sunday', NULL
UNION ALL SELECT CAST(0xEF340B00 AS Date), 2011, 11, N'November', 7, 2012, 2, N'Monday', NULL
UNION ALL SELECT CAST(0xF0340B00 AS Date), 2011, 11, N'November', 8, 2012, 2, N'Tuesday', NULL
UNION ALL SELECT CAST(0xF1340B00 AS Date), 2011, 11, N'November', 9, 2012, 2, N'Wednesday', NULL
UNION ALL SELECT CAST(0xF2340B00 AS Date), 2011, 11, N'November', 10, 2012, 2, N'Thursday', NULL
UNION ALL SELECT CAST(0xF3340B00 AS Date), 2011, 11, N'November', 11, 2012, 2, N'Friday', NULL
UNION ALL SELECT CAST(0xF4340B00 AS Date), 2011, 11, N'November', 12, 2012, 2, N'Saturday', NULL
UNION ALL SELECT CAST(0xF5340B00 AS Date), 2011, 11, N'November', 13, 2012, 2, N'Sunday', NULL
UNION ALL SELECT CAST(0xF6340B00 AS Date), 2011, 11, N'November', 14, 2012, 2, N'Monday', NULL
UNION ALL SELECT CAST(0xF7340B00 AS Date), 2011, 11, N'November', 15, 2012, 2, N'Tuesday', NULL
UNION ALL SELECT CAST(0xF8340B00 AS Date), 2011, 11, N'November', 16, 2012, 2, N'Wednesday', NULL
UNION ALL SELECT CAST(0xF9340B00 AS Date), 2011, 11, N'November', 17, 2012, 2, N'Thursday', NULL
UNION ALL SELECT CAST(0xFA340B00 AS Date), 2011, 11, N'November', 18, 2012, 2, N'Friday', NULL
UNION ALL SELECT CAST(0xFB340B00 AS Date), 2011, 11, N'November', 19, 2012, 2, N'Saturday', NULL
UNION ALL SELECT CAST(0xFC340B00 AS Date), 2011, 11, N'November', 20, 2012, 2, N'Sunday', NULL
UNION ALL SELECT CAST(0xFD340B00 AS Date), 2011, 11, N'November', 21, 2012, 2, N'Monday', NULL
UNION ALL SELECT CAST(0xFE340B00 AS Date), 2011, 11, N'November', 22, 2012, 2, N'Tuesday', NULL
UNION ALL SELECT CAST(0xFF340B00 AS Date), 2011, 11, N'November', 23, 2012, 2, N'Wednesday', NULL
UNION ALL SELECT CAST(0x00350B00 AS Date), 2011, 11, N'November', 24, 2012, 2, N'Thursday', NULL
UNION ALL SELECT CAST(0x01350B00 AS Date), 2011, 11, N'November', 25, 2012, 2, N'Friday', NULL
UNION ALL SELECT CAST(0x02350B00 AS Date), 2011, 11, N'November', 26, 2012, 2, N'Saturday', NULL
UNION ALL SELECT CAST(0x03350B00 AS Date), 2011, 11, N'November', 27, 2012, 2, N'Sunday', NULL
UNION ALL SELECT CAST(0x04350B00 AS Date), 2011, 11, N'November', 28, 2012, 2, N'Monday', NULL
UNION ALL SELECT CAST(0x05350B00 AS Date), 2011, 11, N'November', 29, 2012, 2, N'Tuesday', NULL
UNION ALL SELECT CAST(0x06350B00 AS Date), 2011, 11, N'November', 30, 2012, 2, N'Wednesday', NULL
UNION ALL SELECT CAST(0x07350B00 AS Date), 2011, 12, N'December', 1, 2012, 3, N'Thursday', NULL
UNION ALL SELECT CAST(0x08350B00 AS Date), 2011, 12, N'December', 2, 2012, 3, N'Friday', NULL
UNION ALL SELECT CAST(0x09350B00 AS Date), 2011, 12, N'December', 3, 2012, 3, N'Saturday', NULL
UNION ALL SELECT CAST(0x0A350B00 AS Date), 2011, 12, N'December', 4, 2012, 3, N'Sunday', NULL
UNION ALL SELECT CAST(0x0B350B00 AS Date), 2011, 12, N'December', 5, 2012, 3, N'Monday', NULL
UNION ALL SELECT CAST(0x0C350B00 AS Date), 2011, 12, N'December', 6, 2012, 3, N'Tuesday', NULL
UNION ALL SELECT CAST(0x0D350B00 AS Date), 2011, 12, N'December', 7, 2012, 3, N'Wednesday', NULL
UNION ALL SELECT CAST(0x0E350B00 AS Date), 2011, 12, N'December', 8, 2012, 3, N'Thursday', NULL
UNION ALL SELECT CAST(0x0F350B00 AS Date), 2011, 12, N'December', 9, 2012, 3, N'Friday', NULL
UNION ALL SELECT CAST(0x10350B00 AS Date), 2011, 12, N'December', 10, 2012, 3, N'Saturday', NULL
UNION ALL SELECT CAST(0x11350B00 AS Date), 2011, 12, N'December', 11, 2012, 3, N'Sunday', NULL
UNION ALL SELECT CAST(0x12350B00 AS Date), 2011, 12, N'December', 12, 2012, 3, N'Monday', NULL
UNION ALL SELECT CAST(0x13350B00 AS Date), 2011, 12, N'December', 13, 2012, 3, N'Tuesday', NULL
UNION ALL SELECT CAST(0x14350B00 AS Date), 2011, 12, N'December', 14, 2012, 3, N'Wednesday', NULL
UNION ALL SELECT CAST(0x15350B00 AS Date), 2011, 12, N'December', 15, 2012, 3, N'Thursday', NULL
UNION ALL SELECT CAST(0x16350B00 AS Date), 2011, 12, N'December', 16, 2012, 3, N'Friday', NULL
UNION ALL SELECT CAST(0x17350B00 AS Date), 2011, 12, N'December', 17, 2012, 3, N'Saturday', NULL
UNION ALL SELECT CAST(0x18350B00 AS Date), 2011, 12, N'December', 18, 2012, 3, N'Sunday', NULL
UNION ALL SELECT CAST(0x19350B00 AS Date), 2011, 12, N'December', 19, 2012, 3, N'Monday', NULL
UNION ALL SELECT CAST(0x1A350B00 AS Date), 2011, 12, N'December', 20, 2012, 3, N'Tuesday', NULL
UNION ALL SELECT CAST(0x1B350B00 AS Date), 2011, 12, N'December', 21, 2012, 3, N'Wednesday', NULL
UNION ALL SELECT CAST(0x1C350B00 AS Date), 2011, 12, N'December', 22, 2012, 3, N'Thursday', NULL
UNION ALL SELECT CAST(0x1D350B00 AS Date), 2011, 12, N'December', 23, 2012, 3, N'Friday', NULL
UNION ALL SELECT CAST(0x1E350B00 AS Date), 2011, 12, N'December', 24, 2012, 3, N'Saturday', NULL
UNION ALL SELECT CAST(0x1F350B00 AS Date), 2011, 12, N'December', 25, 2012, 3, N'Sunday', NULL
UNION ALL SELECT CAST(0x20350B00 AS Date), 2011, 12, N'December', 26, 2012, 3, N'Monday', NULL
UNION ALL SELECT CAST(0x21350B00 AS Date), 2011, 12, N'December', 27, 2012, 3, N'Tuesday', NULL
UNION ALL SELECT CAST(0x22350B00 AS Date), 2011, 12, N'December', 28, 2012, 3, N'Wednesday', NULL
UNION ALL SELECT CAST(0x23350B00 AS Date), 2011, 12, N'December', 29, 2012, 3, N'Thursday', NULL
UNION ALL SELECT CAST(0x24350B00 AS Date), 2011, 12, N'December', 30, 2012, 3, N'Friday', NULL
UNION ALL SELECT CAST(0x25350B00 AS Date), 2011, 12, N'December', 31, 2012, 3, N'Saturday', NULL
UNION ALL SELECT CAST(0x26350B00 AS Date), 2012, 1, N'January', 1, 2012, 4, N'Sunday', NULL
UNION ALL SELECT CAST(0x27350B00 AS Date), 2012, 1, N'January', 2, 2012, 4, N'Monday', NULL
UNION ALL SELECT CAST(0x28350B00 AS Date), 2012, 1, N'January', 3, 2012, 4, N'Tuesday', NULL
UNION ALL SELECT CAST(0x29350B00 AS Date), 2012, 1, N'January', 4, 2012, 4, N'Wednesday', NULL
UNION ALL SELECT CAST(0x2A350B00 AS Date), 2012, 1, N'January', 5, 2012, 4, N'Thursday', NULL
UNION ALL SELECT CAST(0x2B350B00 AS Date), 2012, 1, N'January', 6, 2012, 4, N'Friday', NULL
UNION ALL SELECT CAST(0x2C350B00 AS Date), 2012, 1, N'January', 7, 2012, 4, N'Saturday', NULL
UNION ALL SELECT CAST(0x2D350B00 AS Date), 2012, 1, N'January', 8, 2012, 4, N'Sunday', NULL
UNION ALL SELECT CAST(0x2E350B00 AS Date), 2012, 1, N'January', 9, 2012, 4, N'Monday', NULL
UNION ALL SELECT CAST(0x2F350B00 AS Date), 2012, 1, N'January', 10, 2012, 4, N'Tuesday', NULL
UNION ALL SELECT CAST(0x30350B00 AS Date), 2012, 1, N'January', 11, 2012, 4, N'Wednesday', NULL
UNION ALL SELECT CAST(0x31350B00 AS Date), 2012, 1, N'January', 12, 2012, 4, N'Thursday', NULL
UNION ALL SELECT CAST(0x32350B00 AS Date), 2012, 1, N'January', 13, 2012, 4, N'Friday', NULL
UNION ALL SELECT CAST(0x33350B00 AS Date), 2012, 1, N'January', 14, 2012, 4, N'Saturday', NULL
UNION ALL SELECT CAST(0x34350B00 AS Date), 2012, 1, N'January', 15, 2012, 4, N'Sunday', NULL
UNION ALL SELECT CAST(0x35350B00 AS Date), 2012, 1, N'January', 16, 2012, 4, N'Monday', NULL
UNION ALL SELECT CAST(0x36350B00 AS Date), 2012, 1, N'January', 17, 2012, 4, N'Tuesday', NULL
UNION ALL SELECT CAST(0x37350B00 AS Date), 2012, 1, N'January', 18, 2012, 4, N'Wednesday', NULL
UNION ALL SELECT CAST(0x38350B00 AS Date), 2012, 1, N'January', 19, 2012, 4, N'Thursday', NULL
UNION ALL SELECT CAST(0x39350B00 AS Date), 2012, 1, N'January', 20, 2012, 4, N'Friday', NULL
UNION ALL SELECT CAST(0x3A350B00 AS Date), 2012, 1, N'January', 21, 2012, 4, N'Saturday', NULL
UNION ALL SELECT CAST(0x3B350B00 AS Date), 2012, 1, N'January', 22, 2012, 4, N'Sunday', NULL
UNION ALL SELECT CAST(0x3C350B00 AS Date), 2012, 1, N'January', 23, 2012, 4, N'Monday', NULL
UNION ALL SELECT CAST(0x3D350B00 AS Date), 2012, 1, N'January', 24, 2012, 4, N'Tuesday', NULL
UNION ALL SELECT CAST(0x3E350B00 AS Date), 2012, 1, N'January', 25, 2012, 4, N'Wednesday', NULL
UNION ALL SELECT CAST(0x3F350B00 AS Date), 2012, 1, N'January', 26, 2012, 4, N'Thursday', NULL
UNION ALL SELECT CAST(0x40350B00 AS Date), 2012, 1, N'January', 27, 2012, 4, N'Friday', NULL
UNION ALL SELECT CAST(0x41350B00 AS Date), 2012, 1, N'January', 28, 2012, 4, N'Saturday', NULL
UNION ALL SELECT CAST(0x42350B00 AS Date), 2012, 1, N'January', 29, 2012, 4, N'Sunday', NULL
UNION ALL SELECT CAST(0x43350B00 AS Date), 2012, 1, N'January', 30, 2012, 4, N'Monday', NULL
UNION ALL SELECT CAST(0x44350B00 AS Date), 2012, 1, N'January', 31, 2012, 4, N'Tuesday', NULL
UNION ALL SELECT CAST(0x45350B00 AS Date), 2012, 2, N'February', 1, 2012, 5, N'Wednesday', NULL
UNION ALL SELECT CAST(0x46350B00 AS Date), 2012, 2, N'February', 2, 2012, 5, N'Thursday', NULL
UNION ALL SELECT CAST(0x47350B00 AS Date), 2012, 2, N'February', 3, 2012, 5, N'Friday', NULL
UNION ALL SELECT CAST(0x48350B00 AS Date), 2012, 2, N'February', 4, 2012, 5, N'Saturday', NULL
UNION ALL SELECT CAST(0x49350B00 AS Date), 2012, 2, N'February', 5, 2012, 5, N'Sunday', NULL
UNION ALL SELECT CAST(0x4A350B00 AS Date), 2012, 2, N'February', 6, 2012, 5, N'Monday', NULL
UNION ALL SELECT CAST(0x4B350B00 AS Date), 2012, 2, N'February', 7, 2012, 5, N'Tuesday', NULL
UNION ALL SELECT CAST(0x4C350B00 AS Date), 2012, 2, N'February', 8, 2012, 5, N'Wednesday', NULL
UNION ALL SELECT CAST(0x4D350B00 AS Date), 2012, 2, N'February', 9, 2012, 5, N'Thursday', NULL
UNION ALL SELECT CAST(0x4E350B00 AS Date), 2012, 2, N'February', 10, 2012, 5, N'Friday', NULL
UNION ALL SELECT CAST(0x4F350B00 AS Date), 2012, 2, N'February', 11, 2012, 5, N'Saturday', NULL
UNION ALL SELECT CAST(0x50350B00 AS Date), 2012, 2, N'February', 12, 2012, 5, N'Sunday', NULL
UNION ALL SELECT CAST(0x51350B00 AS Date), 2012, 2, N'February', 13, 2012, 5, N'Monday', NULL
UNION ALL SELECT CAST(0x52350B00 AS Date), 2012, 2, N'February', 14, 2012, 5, N'Tuesday', NULL
UNION ALL SELECT CAST(0x53350B00 AS Date), 2012, 2, N'February', 15, 2012, 5, N'Wednesday', NULL
UNION ALL SELECT CAST(0x54350B00 AS Date), 2012, 2, N'February', 16, 2012, 5, N'Thursday', NULL
UNION ALL SELECT CAST(0x55350B00 AS Date), 2012, 2, N'February', 17, 2012, 5, N'Friday', NULL
UNION ALL SELECT CAST(0x56350B00 AS Date), 2012, 2, N'February', 18, 2012, 5, N'Saturday', NULL
UNION ALL SELECT CAST(0x57350B00 AS Date), 2012, 2, N'February', 19, 2012, 5, N'Sunday', NULL
UNION ALL SELECT CAST(0x58350B00 AS Date), 2012, 2, N'February', 20, 2012, 5, N'Monday', NULL
UNION ALL SELECT CAST(0x59350B00 AS Date), 2012, 2, N'February', 21, 2012, 5, N'Tuesday', NULL
UNION ALL SELECT CAST(0x5A350B00 AS Date), 2012, 2, N'February', 22, 2012, 5, N'Wednesday', NULL
UNION ALL SELECT CAST(0x5B350B00 AS Date), 2012, 2, N'February', 23, 2012, 5, N'Thursday', NULL
UNION ALL SELECT CAST(0x5C350B00 AS Date), 2012, 2, N'February', 24, 2012, 5, N'Friday', NULL
UNION ALL SELECT CAST(0x5D350B00 AS Date), 2012, 2, N'February', 25, 2012, 5, N'Saturday', NULL
UNION ALL SELECT CAST(0x5E350B00 AS Date), 2012, 2, N'February', 26, 2012, 5, N'Sunday', NULL
UNION ALL SELECT CAST(0x5F350B00 AS Date), 2012, 2, N'February', 27, 2012, 5, N'Monday', NULL
UNION ALL SELECT CAST(0x60350B00 AS Date), 2012, 2, N'February', 28, 2012, 5, N'Tuesday', NULL
UNION ALL SELECT CAST(0x61350B00 AS Date), 2012, 2, N'February', 29, 2012, 5, N'Wednesday', NULL
UNION ALL SELECT CAST(0x62350B00 AS Date), 2012, 3, N'March', 1, 2012, 6, N'Thursday', NULL
UNION ALL SELECT CAST(0x63350B00 AS Date), 2012, 3, N'March', 2, 2012, 6, N'Friday', NULL
UNION ALL SELECT CAST(0x64350B00 AS Date), 2012, 3, N'March', 3, 2012, 6, N'Saturday', NULL
UNION ALL SELECT CAST(0x65350B00 AS Date), 2012, 3, N'March', 4, 2012, 6, N'Sunday', NULL
UNION ALL SELECT CAST(0x66350B00 AS Date), 2012, 3, N'March', 5, 2012, 6, N'Monday', NULL
UNION ALL SELECT CAST(0x67350B00 AS Date), 2012, 3, N'March', 6, 2012, 6, N'Tuesday', NULL
UNION ALL SELECT CAST(0x68350B00 AS Date), 2012, 3, N'March', 7, 2012, 6, N'Wednesday', NULL
UNION ALL SELECT CAST(0x69350B00 AS Date), 2012, 3, N'March', 8, 2012, 6, N'Thursday', NULL
UNION ALL SELECT CAST(0x6A350B00 AS Date), 2012, 3, N'March', 9, 2012, 6, N'Friday', NULL
UNION ALL SELECT CAST(0x6B350B00 AS Date), 2012, 3, N'March', 10, 2012, 6, N'Saturday', NULL
UNION ALL SELECT CAST(0x6C350B00 AS Date), 2012, 3, N'March', 11, 2012, 6, N'Sunday', NULL
UNION ALL SELECT CAST(0x6D350B00 AS Date), 2012, 3, N'March', 12, 2012, 6, N'Monday', NULL
UNION ALL SELECT CAST(0x6E350B00 AS Date), 2012, 3, N'March', 13, 2012, 6, N'Tuesday', NULL
UNION ALL SELECT CAST(0x6F350B00 AS Date), 2012, 3, N'March', 14, 2012, 6, N'Wednesday', NULL
UNION ALL SELECT CAST(0x70350B00 AS Date), 2012, 3, N'March', 15, 2012, 6, N'Thursday', NULL
UNION ALL SELECT CAST(0x71350B00 AS Date), 2012, 3, N'March', 16, 2012, 6, N'Friday', NULL
UNION ALL SELECT CAST(0x72350B00 AS Date), 2012, 3, N'March', 17, 2012, 6, N'Saturday', NULL
UNION ALL SELECT CAST(0x73350B00 AS Date), 2012, 3, N'March', 18, 2012, 6, N'Sunday', NULL
UNION ALL SELECT CAST(0x74350B00 AS Date), 2012, 3, N'March', 19, 2012, 6, N'Monday', NULL
UNION ALL SELECT CAST(0x75350B00 AS Date), 2012, 3, N'March', 20, 2012, 6, N'Tuesday', NULL
UNION ALL SELECT CAST(0x76350B00 AS Date), 2012, 3, N'March', 21, 2012, 6, N'Wednesday', NULL
UNION ALL SELECT CAST(0x77350B00 AS Date), 2012, 3, N'March', 22, 2012, 6, N'Thursday', NULL
UNION ALL SELECT CAST(0x78350B00 AS Date), 2012, 3, N'March', 23, 2012, 6, N'Friday', NULL
UNION ALL SELECT CAST(0x79350B00 AS Date), 2012, 3, N'March', 24, 2012, 6, N'Saturday', NULL
UNION ALL SELECT CAST(0x7A350B00 AS Date), 2012, 3, N'March', 25, 2012, 6, N'Sunday', NULL
UNION ALL SELECT CAST(0x7B350B00 AS Date), 2012, 3, N'March', 26, 2012, 6, N'Monday', NULL
UNION ALL SELECT CAST(0x7C350B00 AS Date), 2012, 3, N'March', 27, 2012, 6, N'Tuesday', NULL
UNION ALL SELECT CAST(0x7D350B00 AS Date), 2012, 3, N'March', 28, 2012, 6, N'Wednesday', NULL
UNION ALL SELECT CAST(0x7E350B00 AS Date), 2012, 3, N'March', 29, 2012, 6, N'Thursday', NULL
UNION ALL SELECT CAST(0x7F350B00 AS Date), 2012, 3, N'March', 30, 2012, 6, N'Friday', NULL
UNION ALL SELECT CAST(0x80350B00 AS Date), 2012, 3, N'March', 31, 2012, 6, N'Saturday', NULL
UNION ALL SELECT CAST(0x81350B00 AS Date), 2012, 4, N'April', 1, 2012, 7, N'Sunday', NULL
UNION ALL SELECT CAST(0x82350B00 AS Date), 2012, 4, N'April', 2, 2012, 7, N'Monday', NULL
UNION ALL SELECT CAST(0x83350B00 AS Date), 2012, 4, N'April', 3, 2012, 7, N'Tuesday', NULL
UNION ALL SELECT CAST(0x84350B00 AS Date), 2012, 4, N'April', 4, 2012, 7, N'Wednesday', NULL
UNION ALL SELECT CAST(0x85350B00 AS Date), 2012, 4, N'April', 5, 2012, 7, N'Thursday', NULL
UNION ALL SELECT CAST(0x86350B00 AS Date), 2012, 4, N'April', 6, 2012, 7, N'Friday', NULL
UNION ALL SELECT CAST(0x87350B00 AS Date), 2012, 4, N'April', 7, 2012, 7, N'Saturday', NULL
UNION ALL SELECT CAST(0x88350B00 AS Date), 2012, 4, N'April', 8, 2012, 7, N'Sunday', NULL
UNION ALL SELECT CAST(0x89350B00 AS Date), 2012, 4, N'April', 9, 2012, 7, N'Monday', NULL
UNION ALL SELECT CAST(0x8A350B00 AS Date), 2012, 4, N'April', 10, 2012, 7, N'Tuesday', NULL
UNION ALL SELECT CAST(0x8B350B00 AS Date), 2012, 4, N'April', 11, 2012, 7, N'Wednesday', NULL
UNION ALL SELECT CAST(0x8C350B00 AS Date), 2012, 4, N'April', 12, 2012, 7, N'Thursday', NULL
UNION ALL SELECT CAST(0x8D350B00 AS Date), 2012, 4, N'April', 13, 2012, 7, N'Friday', NULL
UNION ALL SELECT CAST(0x8E350B00 AS Date), 2012, 4, N'April', 14, 2012, 7, N'Saturday', NULL
UNION ALL SELECT CAST(0x8F350B00 AS Date), 2012, 4, N'April', 15, 2012, 7, N'Sunday', NULL
UNION ALL SELECT CAST(0x90350B00 AS Date), 2012, 4, N'April', 16, 2012, 7, N'Monday', NULL
UNION ALL SELECT CAST(0x91350B00 AS Date), 2012, 4, N'April', 17, 2012, 7, N'Tuesday', NULL
UNION ALL SELECT CAST(0x92350B00 AS Date), 2012, 4, N'April', 18, 2012, 7, N'Wednesday', NULL
UNION ALL SELECT CAST(0x93350B00 AS Date), 2012, 4, N'April', 19, 2012, 7, N'Thursday', NULL
UNION ALL SELECT CAST(0x94350B00 AS Date), 2012, 4, N'April', 20, 2012, 7, N'Friday', NULL
UNION ALL SELECT CAST(0x95350B00 AS Date), 2012, 4, N'April', 21, 2012, 7, N'Saturday', NULL
UNION ALL SELECT CAST(0x96350B00 AS Date), 2012, 4, N'April', 22, 2012, 7, N'Sunday', NULL
UNION ALL SELECT CAST(0x97350B00 AS Date), 2012, 4, N'April', 23, 2012, 7, N'Monday', NULL
UNION ALL SELECT CAST(0x98350B00 AS Date), 2012, 4, N'April', 24, 2012, 7, N'Tuesday', NULL
UNION ALL SELECT CAST(0x99350B00 AS Date), 2012, 4, N'April', 25, 2012, 7, N'Wednesday', NULL
UNION ALL SELECT CAST(0x9A350B00 AS Date), 2012, 4, N'April', 26, 2012, 7, N'Thursday', NULL
UNION ALL SELECT CAST(0x9B350B00 AS Date), 2012, 4, N'April', 27, 2012, 7, N'Friday', NULL
UNION ALL SELECT CAST(0x9C350B00 AS Date), 2012, 4, N'April', 28, 2012, 7, N'Saturday', NULL
UNION ALL SELECT CAST(0x9D350B00 AS Date), 2012, 4, N'April', 29, 2012, 7, N'Sunday', NULL
UNION ALL SELECT CAST(0x9E350B00 AS Date), 2012, 4, N'April', 30, 2012, 7, N'Monday', NULL
UNION ALL SELECT CAST(0x9F350B00 AS Date), 2012, 5, N'May', 1, 2012, 8, N'Tuesday', NULL
UNION ALL SELECT CAST(0xA0350B00 AS Date), 2012, 5, N'May', 2, 2012, 8, N'Wednesday', NULL
UNION ALL SELECT CAST(0xA1350B00 AS Date), 2012, 5, N'May', 3, 2012, 8, N'Thursday', NULL
UNION ALL SELECT CAST(0xA2350B00 AS Date), 2012, 5, N'May', 4, 2012, 8, N'Friday', NULL
UNION ALL SELECT CAST(0xA3350B00 AS Date), 2012, 5, N'May', 5, 2012, 8, N'Saturday', NULL
UNION ALL SELECT CAST(0xA4350B00 AS Date), 2012, 5, N'May', 6, 2012, 8, N'Sunday', NULL
UNION ALL SELECT CAST(0xA5350B00 AS Date), 2012, 5, N'May', 7, 2012, 8, N'Monday', NULL
UNION ALL SELECT CAST(0xA6350B00 AS Date), 2012, 5, N'May', 8, 2012, 8, N'Tuesday', NULL
UNION ALL SELECT CAST(0xA7350B00 AS Date), 2012, 5, N'May', 9, 2012, 8, N'Wednesday', NULL
UNION ALL SELECT CAST(0xA8350B00 AS Date), 2012, 5, N'May', 10, 2012, 8, N'Thursday', NULL
UNION ALL SELECT CAST(0xA9350B00 AS Date), 2012, 5, N'May', 11, 2012, 8, N'Friday', NULL
UNION ALL SELECT CAST(0xAA350B00 AS Date), 2012, 5, N'May', 12, 2012, 8, N'Saturday', NULL
UNION ALL SELECT CAST(0xAB350B00 AS Date), 2012, 5, N'May', 13, 2012, 8, N'Sunday', NULL
UNION ALL SELECT CAST(0xAC350B00 AS Date), 2012, 5, N'May', 14, 2012, 8, N'Monday', NULL
UNION ALL SELECT CAST(0xAD350B00 AS Date), 2012, 5, N'May', 15, 2012, 8, N'Tuesday', NULL
UNION ALL SELECT CAST(0xAE350B00 AS Date), 2012, 5, N'May', 16, 2012, 8, N'Wednesday', NULL
UNION ALL SELECT CAST(0xAF350B00 AS Date), 2012, 5, N'May', 17, 2012, 8, N'Thursday', NULL
UNION ALL SELECT CAST(0xB0350B00 AS Date), 2012, 5, N'May', 18, 2012, 8, N'Friday', NULL
UNION ALL SELECT CAST(0xB1350B00 AS Date), 2012, 5, N'May', 19, 2012, 8, N'Saturday', NULL
UNION ALL SELECT CAST(0xB2350B00 AS Date), 2012, 5, N'May', 20, 2012, 8, N'Sunday', NULL
UNION ALL SELECT CAST(0xB3350B00 AS Date), 2012, 5, N'May', 21, 2012, 8, N'Monday', NULL
UNION ALL SELECT CAST(0xB4350B00 AS Date), 2012, 5, N'May', 22, 2012, 8, N'Tuesday', NULL
UNION ALL SELECT CAST(0xB5350B00 AS Date), 2012, 5, N'May', 23, 2012, 8, N'Wednesday', NULL
UNION ALL SELECT CAST(0xB6350B00 AS Date), 2012, 5, N'May', 24, 2012, 8, N'Thursday', NULL
UNION ALL SELECT CAST(0xB7350B00 AS Date), 2012, 5, N'May', 25, 2012, 8, N'Friday', NULL
UNION ALL SELECT CAST(0xB8350B00 AS Date), 2012, 5, N'May', 26, 2012, 8, N'Saturday', NULL
UNION ALL SELECT CAST(0xB9350B00 AS Date), 2012, 5, N'May', 27, 2012, 8, N'Sunday', NULL
UNION ALL SELECT CAST(0xBA350B00 AS Date), 2012, 5, N'May', 28, 2012, 8, N'Monday', NULL
UNION ALL SELECT CAST(0xBB350B00 AS Date), 2012, 5, N'May', 29, 2012, 8, N'Tuesday', NULL
UNION ALL SELECT CAST(0xBC350B00 AS Date), 2012, 5, N'May', 30, 2012, 8, N'Wednesday', NULL
UNION ALL SELECT CAST(0xBD350B00 AS Date), 2012, 5, N'May', 31, 2012, 8, N'Thursday', NULL
UNION ALL SELECT CAST(0xBE350B00 AS Date), 2012, 6, N'June', 1, 2012, 9, N'Friday', NULL
UNION ALL SELECT CAST(0xBF350B00 AS Date), 2012, 6, N'June', 2, 2012, 9, N'Saturday', NULL
UNION ALL SELECT CAST(0xC0350B00 AS Date), 2012, 6, N'June', 3, 2012, 9, N'Sunday', NULL
UNION ALL SELECT CAST(0xC1350B00 AS Date), 2012, 6, N'June', 4, 2012, 9, N'Monday', NULL
UNION ALL SELECT CAST(0xC2350B00 AS Date), 2012, 6, N'June', 5, 2012, 9, N'Tuesday', NULL
UNION ALL SELECT CAST(0xC3350B00 AS Date), 2012, 6, N'June', 6, 2012, 9, N'Wednesday', NULL
UNION ALL SELECT CAST(0xC4350B00 AS Date), 2012, 6, N'June', 7, 2012, 9, N'Thursday', NULL
UNION ALL SELECT CAST(0xC5350B00 AS Date), 2012, 6, N'June', 8, 2012, 9, N'Friday', NULL
UNION ALL SELECT CAST(0xC6350B00 AS Date), 2012, 6, N'June', 9, 2012, 9, N'Saturday', NULL
UNION ALL SELECT CAST(0xC7350B00 AS Date), 2012, 6, N'June', 10, 2012, 9, N'Sunday', NULL
UNION ALL SELECT CAST(0xC8350B00 AS Date), 2012, 6, N'June', 11, 2012, 9, N'Monday', NULL
UNION ALL SELECT CAST(0xC9350B00 AS Date), 2012, 6, N'June', 12, 2012, 9, N'Tuesday', NULL
UNION ALL SELECT CAST(0xCA350B00 AS Date), 2012, 6, N'June', 13, 2012, 9, N'Wednesday', NULL
UNION ALL SELECT CAST(0xCB350B00 AS Date), 2012, 6, N'June', 14, 2012, 9, N'Thursday', NULL
UNION ALL SELECT CAST(0xCC350B00 AS Date), 2012, 6, N'June', 15, 2012, 9, N'Friday', NULL
UNION ALL SELECT CAST(0xCD350B00 AS Date), 2012, 6, N'June', 16, 2012, 9, N'Saturday', NULL
UNION ALL SELECT CAST(0xCE350B00 AS Date), 2012, 6, N'June', 17, 2012, 9, N'Sunday', NULL
UNION ALL SELECT CAST(0xCF350B00 AS Date), 2012, 6, N'June', 18, 2012, 9, N'Monday', NULL
UNION ALL SELECT CAST(0xD0350B00 AS Date), 2012, 6, N'June', 19, 2012, 9, N'Tuesday', NULL
UNION ALL SELECT CAST(0xD1350B00 AS Date), 2012, 6, N'June', 20, 2012, 9, N'Wednesday', NULL
UNION ALL SELECT CAST(0xD2350B00 AS Date), 2012, 6, N'June', 21, 2012, 9, N'Thursday', NULL
UNION ALL SELECT CAST(0xD3350B00 AS Date), 2012, 6, N'June', 22, 2012, 9, N'Friday', NULL
UNION ALL SELECT CAST(0xD4350B00 AS Date), 2012, 6, N'June', 23, 2012, 9, N'Saturday', NULL
UNION ALL SELECT CAST(0xD5350B00 AS Date), 2012, 6, N'June', 24, 2012, 9, N'Sunday', NULL
UNION ALL SELECT CAST(0xD6350B00 AS Date), 2012, 6, N'June', 25, 2012, 9, N'Monday', NULL
UNION ALL SELECT CAST(0xD7350B00 AS Date), 2012, 6, N'June', 26, 2012, 9, N'Tuesday', NULL
UNION ALL SELECT CAST(0xD8350B00 AS Date), 2012, 6, N'June', 27, 2012, 9, N'Wednesday', NULL
UNION ALL SELECT CAST(0xD9350B00 AS Date), 2012, 6, N'June', 28, 2012, 9, N'Thursday', NULL
UNION ALL SELECT CAST(0xDA350B00 AS Date), 2012, 6, N'June', 29, 2012, 9, N'Friday', NULL
UNION ALL SELECT CAST(0xDB350B00 AS Date), 2012, 6, N'June', 30, 2012, 9, N'Saturday', NULL
UNION ALL SELECT CAST(0xDC350B00 AS Date), 2012, 7, N'July', 1, 2012, 10, N'Sunday', NULL
UNION ALL SELECT CAST(0xDD350B00 AS Date), 2012, 7, N'July', 2, 2012, 10, N'Monday', NULL
UNION ALL SELECT CAST(0xDE350B00 AS Date), 2012, 7, N'July', 3, 2012, 10, N'Tuesday', NULL
UNION ALL SELECT CAST(0xDF350B00 AS Date), 2012, 7, N'July', 4, 2012, 10, N'Wednesday', NULL
UNION ALL SELECT CAST(0xE0350B00 AS Date), 2012, 7, N'July', 5, 2012, 10, N'Thursday', NULL
UNION ALL SELECT CAST(0xE1350B00 AS Date), 2012, 7, N'July', 6, 2012, 10, N'Friday', NULL
UNION ALL SELECT CAST(0xE2350B00 AS Date), 2012, 7, N'July', 7, 2012, 10, N'Saturday', NULL
UNION ALL SELECT CAST(0xE3350B00 AS Date), 2012, 7, N'July', 8, 2012, 10, N'Sunday', NULL
UNION ALL SELECT CAST(0xE4350B00 AS Date), 2012, 7, N'July', 9, 2012, 10, N'Monday', NULL
UNION ALL SELECT CAST(0xE5350B00 AS Date), 2012, 7, N'July', 10, 2012, 10, N'Tuesday', NULL
UNION ALL SELECT CAST(0xE6350B00 AS Date), 2012, 7, N'July', 11, 2012, 10, N'Wednesday', NULL
UNION ALL SELECT CAST(0xE7350B00 AS Date), 2012, 7, N'July', 12, 2012, 10, N'Thursday', NULL
UNION ALL SELECT CAST(0xE8350B00 AS Date), 2012, 7, N'July', 13, 2012, 10, N'Friday', NULL
UNION ALL SELECT CAST(0xE9350B00 AS Date), 2012, 7, N'July', 14, 2012, 10, N'Saturday', NULL
UNION ALL SELECT CAST(0xEA350B00 AS Date), 2012, 7, N'July', 15, 2012, 10, N'Sunday', NULL
UNION ALL SELECT CAST(0xEB350B00 AS Date), 2012, 7, N'July', 16, 2012, 10, N'Monday', NULL
UNION ALL SELECT CAST(0xEC350B00 AS Date), 2012, 7, N'July', 17, 2012, 10, N'Tuesday', NULL
UNION ALL SELECT CAST(0xED350B00 AS Date), 2012, 7, N'July', 18, 2012, 10, N'Wednesday', NULL
UNION ALL SELECT CAST(0xEE350B00 AS Date), 2012, 7, N'July', 19, 2012, 10, N'Thursday', NULL
UNION ALL SELECT CAST(0xEF350B00 AS Date), 2012, 7, N'July', 20, 2012, 10, N'Friday', NULL
UNION ALL SELECT CAST(0xF0350B00 AS Date), 2012, 7, N'July', 21, 2012, 10, N'Saturday', NULL
UNION ALL SELECT CAST(0xF1350B00 AS Date), 2012, 7, N'July', 22, 2012, 10, N'Sunday', NULL
UNION ALL SELECT CAST(0xF2350B00 AS Date), 2012, 7, N'July', 23, 2012, 10, N'Monday', NULL
UNION ALL SELECT CAST(0xF3350B00 AS Date), 2012, 7, N'July', 24, 2012, 10, N'Tuesday', NULL
UNION ALL SELECT CAST(0xF4350B00 AS Date), 2012, 7, N'July', 25, 2012, 10, N'Wednesday', NULL
UNION ALL SELECT CAST(0xF5350B00 AS Date), 2012, 7, N'July', 26, 2012, 10, N'Thursday', NULL
UNION ALL SELECT CAST(0xF6350B00 AS Date), 2012, 7, N'July', 27, 2012, 10, N'Friday', NULL
UNION ALL SELECT CAST(0xF7350B00 AS Date), 2012, 7, N'July', 28, 2012, 10, N'Saturday', NULL
UNION ALL SELECT CAST(0xF8350B00 AS Date), 2012, 7, N'July', 29, 2012, 10, N'Sunday', NULL
UNION ALL SELECT CAST(0xF9350B00 AS Date), 2012, 7, N'July', 30, 2012, 10, N'Monday', NULL
UNION ALL SELECT CAST(0xFA350B00 AS Date), 2012, 7, N'July', 31, 2012, 10, N'Tuesday', NULL
UNION ALL SELECT CAST(0xFB350B00 AS Date), 2012, 8, N'August', 1, 2012, 11, N'Wednesday', NULL
UNION ALL SELECT CAST(0xFC350B00 AS Date), 2012, 8, N'August', 2, 2012, 11, N'Thursday', NULL
UNION ALL SELECT CAST(0xFD350B00 AS Date), 2012, 8, N'August', 3, 2012, 11, N'Friday', NULL
UNION ALL SELECT CAST(0xFE350B00 AS Date), 2012, 8, N'August', 4, 2012, 11, N'Saturday', NULL
UNION ALL SELECT CAST(0xFF350B00 AS Date), 2012, 8, N'August', 5, 2012, 11, N'Sunday', NULL
UNION ALL SELECT CAST(0x00360B00 AS Date), 2012, 8, N'August', 6, 2012, 11, N'Monday', NULL
UNION ALL SELECT CAST(0x01360B00 AS Date), 2012, 8, N'August', 7, 2012, 11, N'Tuesday', NULL
UNION ALL SELECT CAST(0x02360B00 AS Date), 2012, 8, N'August', 8, 2012, 11, N'Wednesday', NULL
UNION ALL SELECT CAST(0x03360B00 AS Date), 2012, 8, N'August', 9, 2012, 11, N'Thursday', NULL
UNION ALL SELECT CAST(0x04360B00 AS Date), 2012, 8, N'August', 10, 2012, 11, N'Friday', NULL
UNION ALL SELECT CAST(0x05360B00 AS Date), 2012, 8, N'August', 11, 2012, 11, N'Saturday', NULL
UNION ALL SELECT CAST(0x06360B00 AS Date), 2012, 8, N'August', 12, 2012, 11, N'Sunday', NULL
UNION ALL SELECT CAST(0x07360B00 AS Date), 2012, 8, N'August', 13, 2012, 11, N'Monday', NULL
UNION ALL SELECT CAST(0x08360B00 AS Date), 2012, 8, N'August', 14, 2012, 11, N'Tuesday', NULL
UNION ALL SELECT CAST(0x09360B00 AS Date), 2012, 8, N'August', 15, 2012, 11, N'Wednesday', NULL
UNION ALL SELECT CAST(0x0A360B00 AS Date), 2012, 8, N'August', 16, 2012, 11, N'Thursday', NULL
UNION ALL SELECT CAST(0x0B360B00 AS Date), 2012, 8, N'August', 17, 2012, 11, N'Friday', NULL
UNION ALL SELECT CAST(0x0C360B00 AS Date), 2012, 8, N'August', 18, 2012, 11, N'Saturday', NULL
UNION ALL SELECT CAST(0x0D360B00 AS Date), 2012, 8, N'August', 19, 2012, 11, N'Sunday', NULL
UNION ALL SELECT CAST(0x0E360B00 AS Date), 2012, 8, N'August', 20, 2012, 11, N'Monday', NULL
UNION ALL SELECT CAST(0x0F360B00 AS Date), 2012, 8, N'August', 21, 2012, 11, N'Tuesday', NULL
UNION ALL SELECT CAST(0x10360B00 AS Date), 2012, 8, N'August', 22, 2012, 11, N'Wednesday', NULL
UNION ALL SELECT CAST(0x11360B00 AS Date), 2012, 8, N'August', 23, 2012, 11, N'Thursday', NULL
UNION ALL SELECT CAST(0x12360B00 AS Date), 2012, 8, N'August', 24, 2012, 11, N'Friday', NULL
UNION ALL SELECT CAST(0x13360B00 AS Date), 2012, 8, N'August', 25, 2012, 11, N'Saturday', NULL
UNION ALL SELECT CAST(0x14360B00 AS Date), 2012, 8, N'August', 26, 2012, 11, N'Sunday', NULL
UNION ALL SELECT CAST(0x15360B00 AS Date), 2012, 8, N'August', 27, 2012, 11, N'Monday', NULL
UNION ALL SELECT CAST(0x16360B00 AS Date), 2012, 8, N'August', 28, 2012, 11, N'Tuesday', NULL
UNION ALL SELECT CAST(0x17360B00 AS Date), 2012, 8, N'August', 29, 2012, 11, N'Wednesday', NULL
UNION ALL SELECT CAST(0x18360B00 AS Date), 2012, 8, N'August', 30, 2012, 11, N'Thursday', NULL
UNION ALL SELECT CAST(0x19360B00 AS Date), 2012, 8, N'August', 31, 2012, 11, N'Friday', NULL
UNION ALL SELECT CAST(0x1A360B00 AS Date), 2012, 9, N'September', 1, 2012, 12, N'Saturday', NULL
UNION ALL SELECT CAST(0x1B360B00 AS Date), 2012, 9, N'September', 2, 2012, 12, N'Sunday', NULL
UNION ALL SELECT CAST(0x1C360B00 AS Date), 2012, 9, N'September', 3, 2012, 12, N'Monday', NULL
UNION ALL SELECT CAST(0x1D360B00 AS Date), 2012, 9, N'September', 4, 2012, 12, N'Tuesday', NULL
UNION ALL SELECT CAST(0x1E360B00 AS Date), 2012, 9, N'September', 5, 2012, 12, N'Wednesday', NULL
UNION ALL SELECT CAST(0x1F360B00 AS Date), 2012, 9, N'September', 6, 2012, 12, N'Thursday', NULL
UNION ALL SELECT CAST(0x20360B00 AS Date), 2012, 9, N'September', 7, 2012, 12, N'Friday', NULL
UNION ALL SELECT CAST(0x21360B00 AS Date), 2012, 9, N'September', 8, 2012, 12, N'Saturday', NULL
UNION ALL SELECT CAST(0x22360B00 AS Date), 2012, 9, N'September', 9, 2012, 12, N'Sunday', NULL
UNION ALL SELECT CAST(0x23360B00 AS Date), 2012, 9, N'September', 10, 2012, 12, N'Monday', NULL
UNION ALL SELECT CAST(0x24360B00 AS Date), 2012, 9, N'September', 11, 2012, 12, N'Tuesday', NULL
UNION ALL SELECT CAST(0x25360B00 AS Date), 2012, 9, N'September', 12, 2012, 12, N'Wednesday', NULL
UNION ALL SELECT CAST(0x26360B00 AS Date), 2012, 9, N'September', 13, 2012, 12, N'Thursday', NULL
UNION ALL SELECT CAST(0x27360B00 AS Date), 2012, 9, N'September', 14, 2012, 12, N'Friday', NULL
UNION ALL SELECT CAST(0x28360B00 AS Date), 2012, 9, N'September', 15, 2012, 12, N'Saturday', NULL
UNION ALL SELECT CAST(0x29360B00 AS Date), 2012, 9, N'September', 16, 2012, 12, N'Sunday', NULL
UNION ALL SELECT CAST(0x2A360B00 AS Date), 2012, 9, N'September', 17, 2012, 12, N'Monday', NULL
UNION ALL SELECT CAST(0x2B360B00 AS Date), 2012, 9, N'September', 18, 2012, 12, N'Tuesday', NULL
UNION ALL SELECT CAST(0x2C360B00 AS Date), 2012, 9, N'September', 19, 2012, 12, N'Wednesday', NULL
UNION ALL SELECT CAST(0x2D360B00 AS Date), 2012, 9, N'September', 20, 2012, 12, N'Thursday', NULL
UNION ALL SELECT CAST(0x2E360B00 AS Date), 2012, 9, N'September', 21, 2012, 12, N'Friday', NULL
UNION ALL SELECT CAST(0x2F360B00 AS Date), 2012, 9, N'September', 22, 2012, 12, N'Saturday', NULL
UNION ALL SELECT CAST(0x30360B00 AS Date), 2012, 9, N'September', 23, 2012, 12, N'Sunday', NULL
UNION ALL SELECT CAST(0x31360B00 AS Date), 2012, 9, N'September', 24, 2012, 12, N'Monday', NULL
UNION ALL SELECT CAST(0x32360B00 AS Date), 2012, 9, N'September', 25, 2012, 12, N'Tuesday', NULL
UNION ALL SELECT CAST(0x33360B00 AS Date), 2012, 9, N'September', 26, 2012, 12, N'Wednesday', NULL
UNION ALL SELECT CAST(0x34360B00 AS Date), 2012, 9, N'September', 27, 2012, 12, N'Thursday', NULL
UNION ALL SELECT CAST(0x35360B00 AS Date), 2012, 9, N'September', 28, 2012, 12, N'Friday', NULL
UNION ALL SELECT CAST(0x36360B00 AS Date), 2012, 9, N'September', 29, 2012, 12, N'Saturday', NULL
UNION ALL SELECT CAST(0x37360B00 AS Date), 2012, 9, N'September', 30, 2012, 12, N'Sunday', NULL
CREATE TABLE #values(
[itemID] [int] NOT NULL,
[location] int not null,
[type] int not null,
[FiscalYear] [int] NOT NULL,
[Month] [int] NOT NULL,
[ChangeAmount] [decimal](19, 2) NOT NULL)
INSERT INTO #values (ChangeAmount, FiscalYear, Month, itemID,type,location)
SELECT 300,2012,9,1,2,10 UNION ALL
SELECT 250,2012,8,1,2,10 UNION ALL
SELECT 200,2012,9,2,2,10 UNION ALL
SELECT 50, 2012,2,2,2,10 UNION ALL
SELECT 900,2012,2,3,3,9
declare @fiscalYear int = 2012, @typeID int = 2
;with calendar as
(select distinct calendarmonth, calendarYear, fiscalYear, fiscalMonth
from #calendar)
select
c.calendarMonth,
SUM(v.ChangeAmount)
from calendar c
LEFT JOIN #values v
on c.calendarMonth = v.Month
WHERE v.FiscalYear = @fiscalYear
AND v.type = @typeID
group by c.calendarMonth, c.fiscalMonth
order by c.fiscalMonth
drop table #values
drop table #calendar
yields
calendarMonth (No column name)
2 50.00
8 250.00
9 500.00
expected results
calendarMonth (No column name)
10 NULL
11 NULL
12 NULL
1 NULL
2 50.00
3 NULL
4 NULL
5 NULL
6 NULL
7 NULL
8 250.00
9 500.00
I think the issue is in the Where clause. If I eliminate the clause, every month shows up but of course, the items considered aren't accurate. When it is included, the numbers are correct but NULL months are eliminated.
April 2, 2012 at 6:35 am
Either of the following will return the results you want. The second is the way I would do it and it has a much better execution plan cost.
;with calendar as
(select distinct calendarmonth, calendarYear, fiscalYear, fiscalMonth
from #calendar)
select c.calendarMonth,SUM(v.ChangeAmount)
from calendar c
LEFT JOIN #values v
on c.calendarMonth = v.Month and v.FiscalYear = @fiscalYear and v.type = @typeid
--WHERE v.FiscalYear = @fiscalYear
-- AND v.type = @typeID
group by c.calendarMonth, c.fiscalMonth
order by c.fiscalMonth
;with calendar as
(select calendarmonth, calendarYear, fiscalYear, fiscalMonth
from #calendar WHERE calendarDay = 1)
select c.calendarMonth,SUM(v.ChangeAmount)
from calendar c
LEFT JOIN #values v
on c.calendarMonth = v.Month and v.FiscalYear = @fiscalYear and v.type = @typeid
group by c.calendarMonth, c.fiscalMonth
order by c.fiscalMonth
I only included the first so you could easily see where you were going wrong.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 2, 2012 at 6:35 am
Just move the Where clause conditions into the join conditions, should do it.
;with calendar as
(select distinct calendarmonth, calendarYear, fiscalYear, fiscalMonth
from #calendar)
select
c.calendarMonth,
SUM(v.ChangeAmount)
from calendar c
LEFT JOIN #values v
on c.calendarMonth = v.Month
AND v.FiscalYear = @fiscalYear
AND v.type = @typeID
group by c.calendarMonth, c.fiscalMonth
order by c.fiscalMonth
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply