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

avatar image

sqlnewb
216 29 32 35

@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 voted first

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

avatar image

ThomasRushton ♦♦
40.3k 20 49 53

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

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

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(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:

x1016
x393
x3

asked: Oct 17, 2011 at 11:56 AM

Seen: 1174 times

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

Copyright 2016 Redgate Software. Privacy Policy