Returning grouped results

Transact-SQL
1

Hi,

I need a sql to represent the following data to group the DTStamp column to a single hour with the relevant data

DTStamp PO ItemNo Qty
11/3/2014 6:24 P019646 55004-RT-L 1
11/3/2014 6:24 P019646 55004-RT-L 1
11/3/2014 6:25 P019646 55004-RT-L 1
11/3/2014 6:25 P019646 55004-RT-L 1
11/3/2014 7:05 P019645 55004-LFT-L 1
11/3/2014 7:07 P019435 8744-FG-XSS 1
11/3/2014 7:07 P019435 8744-FG-XSS 1
11/3/2014 7:07 P019435 8744-FG-XSS 1
11/3/2014 7:07 P019435 8744-FG-XSS 1
11/3/2014 8:22 P019657 8745-DS-M 1
11/3/2014 8:22 P019646 55004-RT-L 1
11/3/2014 8:22 P019657 8745-DS-M 1

The output should be -

DTStamp PO ItemNo Qty
11/3/2014 6:00 P019646 55004-RT-L 4
11/3/2014 7:00 P019645 55004-LFT-L 1
11/3/2014 7:00 P019435 8744-FG-XSS 4
11/3/2014 8:00 P019657 8745-DS-M 2
11/3/2014 8:00 P019646 55004-RT-L 1

Thanks,

Mohit.

2 
WITH yourSource (DTStamp,PO,ItemNo,Qty)
AS (SELECT '11/3/2014 6:24','P019646','55004-RT-L', 1 UNION ALL
    SELECT '11/3/2014 6:24','P019646','55004-RT-L', 1 UNION ALL
    SELECT '11/3/2014 6:25','P019646','55004-RT-L', 1 UNION ALL
    SELECT '11/3/2014 6:25','P019646','55004-RT-L', 1 UNION ALL
    SELECT '11/3/2014 7:05','P019645','55004-LFT-L', 1 UNION ALL
    SELECT '11/3/2014 7:07','P019435','8744-FG-XSS', 1 UNION ALL
    SELECT '11/3/2014 7:07','P019435','8744-FG-XSS', 1 UNION ALL
    SELECT '11/3/2014 7:07','P019435','8744-FG-XSS', 1 UNION ALL
    SELECT '11/3/2014 7:07','P019435','8744-FG-XSS', 1 UNION ALL
    SELECT '11/3/2014 8:22','P019657','8745-DS-M', 1 UNION ALL
    SELECT '11/3/2014 8:22','P019646','55004-RT-L', 1 UNION ALL
    SELECT '11/3/2014 8:22','P019657','8745-DS-M', 1 )


SELECT
    DATEADD(HOUR,DATEDIFF(HOUR,0,DTStamp),0) AS DTStamp
    , PO 
    , ItemNo 
    , SUM(Qty) AS Qty
FROM
    yourSource
GROUP BY
    DATEADD(HOUR,DATEDIFF(HOUR,0,DTStamp),0)
    , PO 
    , ItemNo

The output:

DTStamp                 PO      ItemNo      Qty
2014-11-03 06:00:00.000 P019646 55004-RT-L  4
2014-11-03 07:00:00.000 P019435 8744-FG-XSS 4
2014-11-03 07:00:00.000 P019645 55004-LFT-L 1
2014-11-03 08:00:00.000 P019646 55004-RT-L  1
2014-11-03 08:00:00.000 P019657 8745-DS-M   2
1 Like
3

Use DATEADD(HOUR,DATEDIFF(HOUR,'19000101',DTStamp),'19000101') to round down to the hour. So your query would be

SELECT DATEADD(HOUR,DATEDIFF(HOUR,'19000101',DTStamp),'19000101'),
	PO,
	Item,
	SUM(Quantity)
FROM
	YourTable
GROUP BY
	DATEADD(HOUR,DATEDIFF(HOUR,'19000101',DTStamp),'19000101'),
	PO,
	Item;
1 Like
4

Also, refer this to know more methods on how to filter datetime values effectively http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx