Query for datas pe hour & count total carton

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query for datas pe hour & count total carton

Author

Topic

chresrond
Starting Member

2 Posts

Posted - 2012-12-13 : 23:17:31

Dear Master,

I'm newbie , I have table like bellow then I need retrieve data like as below result . could you advise how to get the result??

Please....

i want result like below ;

date count(ref_no) count(ctn_no) sum(quantity)

00:00am to 08:00 4 5 10000

08:01am to 12:00pm

12:01pm to 18:00pm
18:01 to 23:59pm 00 00 000

tbl_pack
insert_date ref_no ctn_no serial_no quantity

03/09/2012 13:12:53:837 I2280097 34 1 T3BSB 500
03/09/2012 13:12:53:867 I2280097 34 2 T3BSA 500
03/09/2012 13:12:53:893 I2280097 34 3 T3BS9 500
03/09/2012 13:12:53:923 I2280097 34 4 T1NJR 500
03/09/2012 13:13:37:453 I2280097 35 1 T29KU 2000
03/09/2012 13:13:37:483 I2280097 35 2 T29KT 2000
03/09/2012 13:13:37:51 I2280097 35 3 T29KS 2000
03/09/2012 13:25:11:907 I2280102 1 1 T491Q 400
03/09/2012 13:25:11:953 I2280102 1 2 T491S 400
03/09/2012 13:25:11:983 I2280102 1 3 T491U 400

ChrisR

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-14 : 00:20:23

[code]
declare @tbl_pack table (insert_date datetime, ref_no varchar(15), ctn_no int, serial_no int, field varchar(20), quantity inT)
insert into @tbl_pack
SELECT '03/09/2012 00:12:53:837', 'I2280097', 34, 1, 'T3BSB', 500 union all
SELECT '03/09/2012 08:00:53:867', 'I2280097', 34, 2, 'T3BSA', 500 union all
SELECT '03/09/2012 08:01:53:893', 'I2280097', 34, 3, 'T3BS9', 500 union all
SELECT '03/09/2012 13:12:53:923', 'I2280097', 34, 4, 'T1NJR', 500 union all
SELECT '03/09/2012 13:13:37:453', 'I2280097', 35, 1, 'T29KU', 2000 union all
SELECT '03/09/2012 13:13:37:483', 'I2280097', 35, 2, 'T29KT', 2000 union all
SELECT '03/09/2012 13:13:37:51', 'I2280097', 35, 3, 'T29KS', 2000 union all
SELECT '03/09/2012 13:25:11:907', 'I2280102', 1, 1, 'T491Q', 400 union all
SELECT '03/09/2012 13:25:11:953', 'I2280102', 1, 2, 'T491S', 400 union all
SELECT '03/09/2012 23:25:11:983', 'I2280102', 1, 3, 'T491U', 400

SELECT CASE WHEN CONVERT(VARCHAR(5), insert_date, 24) BETWEEN '00:00' AND '08:00' THEN '00:00 to 08:00'
WHEN CONVERT(VARCHAR(5), insert_date, 24) BETWEEN '08:01' AND '12:00' THEN '08:01 to 12:00'
WHEN CONVERT(VARCHAR(5), insert_date, 24) BETWEEN '12:01' AND '18:00' THEN '12:01 to 18:00'
WHEN CONVERT(VARCHAR(5), insert_date, 24) BETWEEN '18:01' AND '23:59' THEN '18:01 to 23:59'
END TimeRanges,
COUNT(ref_no) NoOfRef, COUNT(ctn_no) NoOfCtn, SUM(quantity) TotalQty
FROM @tbl_pack
GROUP BY CASE WHEN CONVERT(VARCHAR(5), insert_date, 24) BETWEEN '00:00' AND '08:00' THEN '00:00 to 08:00'
WHEN CONVERT(VARCHAR(5), insert_date, 24) BETWEEN '08:01' AND '12:00' THEN '08:01 to 12:00'
WHEN CONVERT(VARCHAR(5), insert_date, 24) BETWEEN '12:01' AND '18:00' THEN '12:01 to 18:00'
WHEN CONVERT(VARCHAR(5), insert_date, 24) BETWEEN '18:01' AND '23:59' THEN '18:01 to 23:59'
END
[/code]

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 02:34:35

see

http://visakhm.blogspot.in/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chresrond
Starting Member

2 Posts

Posted - 2012-12-14 : 02:58:24

Dear Candu,

Thank you for your answer, but I need per day and not count total row. I need total refno and total ctn_no .
Sample I given total only 2 ref no and 3 ctns only.

ChrisR

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 03:05:59

quote:
Originally posted by chresrond

Dear Candu,

Thank you for your answer, but I need per day and not count total row. I need total refno and total ctn_no .
Sample I given total only 2 ref no and 3 ctns only.

ChrisR

Add date field also onto GROUP BY in earlier suggestion
or use logic in link i posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-14 : 04:08:14

quote:
Originally posted by chresrond

Dear Candu,
Thank you for your answer, but I need per day and not count total row. I need total refno and total ctn_no .
Sample I given total only 2 ref no and 3 ctns only.
ChrisR

Hi,
Add GROUP BY CAST(insert_date AS DATE) and also put DISTINCT keyword in COUNT()

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources