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] <= [g].[GroupPeakVal])
GROUP BY [g].[GroupID]
answered
Oct 18 '11 at 01:58 AM
Fatherjack ♦♦
38.8k
●
55
●
69
●
104
@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.