x

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)
more ▼

asked Oct 17, 2011 at 11:56 AM in Default

sqlnewb gravatar image

sqlnewb
216 28 30 31

@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.
Oct 17, 2011 at 12:57 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

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]
more ▼

answered Oct 18, 2011 at 01:58 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left

What I would do is something along these lines:

SELECT 
    SUM (CASE WHEN orderdate >= '2007-01-01' AND orderdate < '2007-07-01' THEN 1 ELSE 0 END) AS [2007H1],
    SUM (CASE WHEN orderdate >= '2008-01-01' AND orderdate < '2008-07-01' THEN 1 ELSE 0 END) AS [2008H1],
    SUM (CASE WHEN orderdate >= '2009-01-01' AND orderdate < '2009-07-01' THEN 1 ELSE 0 END) AS [2009H1]
FROM ...

Doesn't give you the GROUP BY that you want, but may well be quicker.

If you must have it as a GROUP BY, and it's a fixed pattern, then something like this might work:

SELECT convert(char(4), datepart(yyyy,orderdate)) + 'H1', count(*)
FROM orderdate
WHERE month(orderdate) < 7 -- because we're only interested in the first six months
GROUP BY convert(char(4),datepart(yyyy,orderdate))+'H1'
(untested...)
more ▼

answered Oct 17, 2011 at 12:47 PM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x742
x293
x3

asked: Oct 17, 2011 at 11:56 AM

Seen: 731 times

Last Updated: Oct 17, 2011 at 11:56 AM