query to get time periods

Question

Post reply

query to get time periods

vidya-391318

SSC-Addicted

Points: 407
More actions
June 8, 2007 at 12:24 pm

#119400

Hi,

How do i get the counts slotted into the following time periods
0-30 minutes
31-60 minutes
61-90 minutes
91-120 minutes
over 120 minutes
using 1 query
table structure
uidNative Numeric(18, 0)
dteNative DateTime
dteDocProcess DateTime

for example i want to display results like this
date count30 count60 count90 count120 count>120
please help
thanx..

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Some sample data and output results would help understand what you are looking for better.

vidya-391318 SSC-Addicted Points: 407 More actions · Answer 2

suppose... in the table... for 01/01/2007 there are 50 records

and the time taken to process is the difference between dteNative and dteDocProcess

so i want to display data based on the time taken to process the data

0-30 minutes.. what is the count of data processed ?

31-60 minutes ...what is the count of data processed ? and so on....

hope it is clear now

date count30 count60 count90 count120 count>120

01/01/2007 10 10 10 10 10

vidya-391318 SSC-Addicted Points: 407 More actions · Answer 3

i want to do something like this...but for a date... i want 1 record

select

CONVERT(VARCHAR(10), DTENATIVE, 101) AS [TODAY],

case

when

datediff(minute, dtenative, dtedocprocess) <= 30

then

count(uidNative)

end

as count30,

case

when

(datediff(minute, dtenative, dtedocprocess) > 30 and datediff(minute, dtenative, dtedocprocess) <= 60)

then

count(uidNative)

end

as count60

FROM

NATIVEDOCUMENT

WHERE

BSYSTEM

= 0

AND

NUMDOCSEQUENCE = 9999

AND

OPTSTATUS = 'Y'

AND

DTENATIVE BETWEEN '1/1/2007 01:00:00' and '1/31/2007 23:59:59'

GROUP

BY CONVERT(VARCHAR(10), DTENATIVE, 101) , datediff(minute, dtenative, dtedocprocess)

ORDER

BY CONVERT(VARCHAR(10), DTENATIVE, 101)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Give this a try:

select

TheDate,

sum(Count30),

sum(Count60),

sum(Count90),

sum(Count120),

sum(CountOver120)

from

(select

dateadd(dd,datediff(dd,0,dteNative),0) as TheDate,

case when datediff(mi, dteNative, dteDocProcess) between 0 and 30 then 1 else 0 end as Count30,

case when datediff(mi, dteNative, dteDocProcess) between 31 and 60 then 1 else 0 end as Count60,

case when datediff(mi, dteNative, dteDocProcess) between 61 and 90 then 1 else 0 end as Count90,

case when datediff(mi, dteNative, dteDocProcess) between 91 and 120 then 1 else 0 end as Count120,

case when datediff(mi, dteNative, dteDocProcess) > 120 then 1 else 0 end as CountOver120

from

dbo.DataTable -- this is your table

) dt

group by

TheDate

order by

TheDate

vidya-391318 SSC-Addicted Points: 407 More actions · Answer 5

thanks... for the help,...

i also need to get the total count for a day

and the max and mean wait times

MAX

(DATEDIFF(SECOND, DTENATIVE, DTEDOCPROCESS)) AS MAXWAIT,

AVG

(DATEDIFF(SECOND, dtenative, dtedocprocess)) AS MEANWAIT

can all this be done,... in 1 query....

vidya-391318 SSC-Addicted Points: 407 More actions · Answer 6

ok... i think i got it...

thanks a lot for your help....