Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SQL select statement which produces the following results, and I need to define it so that we can group the data based on 30 minute intervals of time and provide the sum of Revenue and Nbr of Trans.

Table Date/Time field -

ActionDateTime          REVENUE NBR of TRANS
2013-01-03 07:44:57.840 5.00    1
2013-01-03 07:45:10.093 5.00    1
2013-01-03 07:45:21.557 1.00    1
2013-01-03 09:07:21.253 50.00   1
2013-01-03 09:07:42.680 40.00   1
2013-01-03 09:07:49.007 104.30  1

Current Results -

DATE        TIME             REVENUE    NBR of TRANS
01/03/2013   07:44:57   5.00    1
01/03/2013   07:45:10   5.00    1
01/03/2013   07:45:21   1.00    1
01/03/2013   09:07:21   50.00   1
01/03/2013   09:07:42   40.00   1
01/03/2013   09:07:49   104.30  1

Desired Results -

DATE        TIME             REVENUE    NBR of TRANS
01/03/2013   08:00:00    11.00  3
01/03/2013   09:30:00   194.30  3

Current SQL statement -

select
  'DATE' = CONVERT(VARCHAR(12), x30.ActionDateTime, 101), 
  'TIME' = CONVERT(VARCHAR(12), x30.ActionDateTime, 108), 
  'REVENUE' = SUM(t01.Amount), 
  'NBR of TRANS' = COUNT(t01.DocumentNumber)
from
dbo.X30_AuditInformation x30 with (nolock),
dbo.T01_TransactionMaster t01 with (nolock),
dbo.T04_GiftDetails t04 with (nolock),
dbo.B01_BatchMaster b01 with (nolock)
where x30.TableRecordId = t01.RecordId and x30.TableId = 'T01_' and x30.Action = 'INSERT'
and t01.DocumentNumber = t04.DocumentNumber 
and b01.BatchNumber = t01.BatchNumber 
and t01.Date between '01/01/2013' and '01/02/2013'
and b01.BatchCategory in ('IMC')
and t04.SourceCode like 'T%'
GROUP BY x30.ActionDateTime, DATEPART(MINUTE, x30.ActionDateTime)/30 
ORDER BY x30.ActionDateTime

Is my GROUP BY incorrect?

share|improve this question
    
To be sure, is date irrelevant for the grouping? And then, do you to show intervals where nothing happened? –  gbn Apr 4 '13 at 15:43
    
Also what is '01/02/2013'? Is it January 2nd or February 1st? Do you want to include all of the data from that day, or do you want that "post" to be the last one? –  Aaron Bertrand Apr 4 '13 at 16:03

2 Answers 2

Just this...

SELECT
    'DATE' = CONVERT(VARCHAR(12), CAST(x30.ActionDateTime AS date), 101),
    'TIME' = CAST(DATEPART(HOUR, x30.ActionDateTime) AS char(2)) + ':' + CAST(DATEPART(MINUTE, x30.ActionDateTime)/30 AS char(2),
    ...

GROUP BY
    CAST(x30.ActionDateTime AS date),
    DATEPART(HOUR, x30.ActionDateTime),
    DATEPART(MINUTE, x30.ActionDateTime)/30 
share|improve this answer

Here is a slightly different technique that I'll demonstrate using a table variable that represents the core output of your join (since I'm way too lazy to rebuild your schema and way too annoyed to try to re-write your old-style joins):

DECLARE @x TABLE(ActionDateTime DATETIME, Amount DECIMAL(10,2), DocumentNumber INT);

INSERT @x VALUES
('2013-01-03 07:44:57.840',5.00  ,1),
('2013-01-03 07:45:10.093',5.00  ,1),
('2013-01-03 07:45:21.557',1.00  ,1),
('2013-01-03 09:07:21.253',50.00 ,1),
('2013-01-03 09:07:42.680',40.00 ,1),
('2013-01-03 09:07:49.007',104.30,1);

;WITH y AS 
(
  SELECT TOP (48) n = ROW_NUMBER() OVER 
  (ORDER BY [object_id]) * 30 FROM sys.all_objects
)
SELECT 
  ActionDateTime = DATEADD(MINUTE, y.n, DATEADD(DAY, DATEDIFF(DAY,0,x30.ActionDateTime),0)),
  REVENUE = SUM(x30.Amount),
  [NBR of TRANS] = COUNT(x30.DocumentNumber)
FROM @x AS x30
INNER JOIN y
  ON  x30.ActionDateTime >  DATEADD(MINUTE, y.n-30, 
      DATEADD(DAY, DATEDIFF(DAY,0,x30.ActionDateTime),0))
  AND x30.ActionDateTime <= DATEADD(MINUTE, y.n,    
      DATEADD(DAY, DATEDIFF(DAY,0,x30.ActionDateTime),0))
GROUP BY DATEADD(MINUTE, y.n, DATEADD(DAY, DATEDIFF(DAY,0,x30.ActionDateTime),0))
ORDER BY [ActionDateTime];

Results:

ActionDateTime           REVENUE  NBR of TRANS
-----------------------  -------  -----------
2013-01-03 08:00:00.000  11.00    3
2013-01-03 09:30:00.000  194.30   3
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.