Query for datas pe hour & count total carton

Question

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
03/09/2012 13:25:12:017	I2280102	1	4	T491T	400
03/09/2012 13:25:12:047	I2280102	1	5	T491R	400
03/09/2012 13:25:26:063	I2280102	2	1	T491O	400
03/09/2012 13:25:26:11	I2280102	2	2	T491V	400
03/09/2012 13:31:17:047	I2280106	1	1	T42Z2	500
03/09/2012 13:31:17:297	I2280106	1	2	T42YS	500
03/09/2012 13:31:17:53	I2280106	1	3	T42YT	500
03/09/2012 13:31:18:267	I2280106	1	4	T42YQ	500
03/09/2012 13:31:18:983	I2280106	1	5	T46ZK	500
03/09/2012 13:31:19:157	I2280106	1	6	T42YM	500
03/09/2012 13:31:19:407	I2280106	1	7	T42YP	500

Best regards,

Chris

Answer 1

Try:

select '00:00 am - 08:00am' as [date], count(ref_no)  as Ref_No_Count,  count(ctn_no)  AS Ctn_No_Count, sum(quantity) as TotalQuantity

from dbo.tbl_pack where cast(insert_date as time) between '00:00:00' and '08:00:00'

UNION ALL

select '08:01 am - 12:00pm' as [date], count(ref_no)  as Ref_No_Count,  count(ctn_no)  AS Ctn_No_Count, sum(quantity) as TotalQuantity

from dbo.tbl_pack where cast(insert_date as time) > '08:00:00' and cast(insert_date as time) <= '12:00:00'

etc. for other times you need.

Or, alternatively, create a table variable with time intervals (StartTime EndTime) and join with that table.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Check this:

select Case when cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)>= '1900-01-01 00:00:00.000' And cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)<='1900-01-01 08:00:00.000' then '00:00AM to 08:00AM'
            when cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)>= '1900-01-01 08:01:00.000' And cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)<='1900-01-01 12:00:00.000' then '08:01AM to 12:00PM'
            when cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)>= '1900-01-01 12:01:00.000' And cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)<='1900-01-01 18:00:00.000' then '12:01AM to 18:00PM'
            when cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)>= '1900-01-01 18:01:00.000' And cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)<='1900-01-01 23:59:00.000' then '18:01AM to 23:59PM'
        end as [Date]
,count(Ref_no),Count(serial_no),Sum(quantity)from test
group by Case when cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)>= '1900-01-01 00:00:00.000' And cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)<='1900-01-01 08:00:00.000' then '00:00AM to 08:00AM'
            when cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)>= '1900-01-01 08:01:00.000' And cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)<='1900-01-01 12:00:00.000' then '08:01AM to 12:00PM'
            when cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)>= '1900-01-01 12:01:00.000' And cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)<='1900-01-01 18:00:00.000' then '12:01AM to 18:00PM'
            when cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)>= '1900-01-01 18:01:00.000' And cast(LTRIM(RIGHT(CONVERT(VARCHAR(20), insert_date, 100), 7)) as datetime)<='1900-01-01 23:59:00.000' then '18:01AM to 23:59PM'
        end

Regards,Eshwar

---

--Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

Answer 3

Chirs please supply DDL, like create table and insert statements  It saves us all time to reply. also want to clarify

date 

 count(ref_no)-- is count of total Ref_no in one day

   count(ctn_no)-- is count of total ctn_no in one day 

sum(quantity) in one day

right..?

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

Answer 4

Naomi.

Thanks that's an easiest method..:)

select '00:00 am - 08:00am' as [date], count(ref_no)  as Ref_No_Count,  count(ctn_no)  AS Ctn_No_Count, sum(quantity) as TotalQuantity
from dbo.test where cast(insert_date as time) between '00:00:00' and '08:00:00'
UNION ALL
select '08:01 am - 12:00pm' as [date], count(ref_no)  as Ref_No_Count,  count(ctn_no)  AS Ctn_No_Count, sum(quantity) as TotalQuantity
from dbo.test where cast(insert_date as time) > '08:00:00' and cast(insert_date as time) <= '12:00:00'
UNION ALL
select '00:00 am - 08:00am' as [date], count(ref_no)  as Ref_No_Count,  count(ctn_no)  AS Ctn_No_Count, sum(quantity) as TotalQuantity
from dbo.test where cast(insert_date as time) > '12:00:00' and cast(insert_date as time) <= '18:00:00'
UNION ALL
select '08:01 am - 12:00pm' as [date], count(ref_no)  as Ref_No_Count,  count(ctn_no)  AS Ctn_No_Count, sum(quantity) as TotalQuantity
from dbo.test where cast(insert_date as time) > '18:00:00' and cast(insert_date as time) <= '23:59:00'

Regards,Eshwar

---

--Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

Answer 5

dear Dia.Agha,

Yes, I need result per day .