asked
include a group of date ranges
Is this the proper way to group by a certain set of date ranges? I want to show the date ranges seperated by the 3 time periods Select sum(total), datepart(YYYY,orderdate) Yr WHERE YearMonth between 200701 and 200706 Yearmonth between 200801 and 200806 and YearMonth 200901 and 200906 group by datepart(YYYY,orderdate)
@sqlnewb - please don't delete the question especially when someone has provided an answer. If something is wrong with the question, or you need to add more/rephrase it, please just add comments or add edits.
What I would do is something along these lines: SELECT SUM (CASE WHEN orderdate >= '2007-01-01' AND orderdate ]= '2008-01-01' AND orderdate ]= '2009-01-01' AND orderdate ]
If you want flexibility in grouping then a temporary table that can have your grouping values in it can be very useful. It can be filled on the fly depending on requirements. USE [ASKSSC] GO -- make a pretend Orders table IF OBJECT_ID('ASKOrders') > 0 DROP TABLE ASKOrders CREATE TABLE ASKOrders ( OrderID INT IDENTITY , OrderDate DATE , OrderValue DECIMAL(8, 2) ) -- add some random data WHILE ( SELECT COUNT(*) FROM [dbo].[ASKOrders] ) < 2000 BEGIN INSERT INTO [dbo].[ASKOrders] ( [OrderDate] , [OrderValue] ) VALUES ( DATEADD(d, ( ABS(CHECKSUM(NEWID())) % 1460 ) * -1, GETDATE()) , -- OrderDate - date ( ABS(CHECKSUM(NEWID())) % 20000 ) / 100.0-- OrderValue - decimal ) END GO -- add an index CREATE INDEX IX_OderDates ON [dbo].[ASKOrders] ([OrderDate]) INCLUDE ([OrderValue]) -- SELECT COUNT(*) FROM [dbo].[ASKOrders] AS ao -- show the data in the table -- create a temporary table for grouping IF OBJECT_ID('tempdb..#Groups') > 0 DROP TABLE #Groups CREATE TABLE #Groups ( GroupID INT , GroupBaseVal DATE , GroupPeakVal DATE ) -- index the temp table CREATE INDEX IX_BaseDate ON [#Groups] ([GroupBaseVal], [GroupPeakVal]) INCLUDE ([GroupID]) -- add some random data INSERT INTO [#Groups] ( [GroupID], [GroupBaseVal], [GroupPeakVal] ) VALUES ( 1, -- GroupID - int '2008-01-01', -- GroupBaseVal - date '2008-12-31' -- GroupPeakVal - date ), ( 2, -- GroupID - int '2009-01-01', -- GroupBaseVal - date '2009-12-31' -- GroupPeakVal - date ), ( 3, -- GroupID - int '2010-01-01', -- GroupBaseVal - date '2010-12-31' -- GroupPeakVal - date ), ( 4, -- GroupID - int '2011-01-01', -- GroupBaseVal - date '2011-12-31' -- GroupPeakVal - date ) -- SELECT * FROM [#Groups] AS g -- show data in table -- the bit you want, getting the totals with a grouping SELECT SUM([ao].[OrderValue]) AS [Sum_of_OrderValue] , [g].[GroupID] FROM [dbo].[ASKOrders] AS ao INNER JOIN [#Groups] AS g ON ([ao].[OrderDate] >= [g].[GroupBaseVal] AND [ao].[OrderDate] ]]]]
