how to get the max , min time in a day

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 2000 Forums

SQL Server Development (2000)

how to get the max , min time in a day

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-05-29 : 02:00:12

declare @table table
(
cust_id int,
cust_name varchar(20),
action varchar(20),
date_time datetime
)

insert into @table
select 1, 'john', 'Login', '2006-04-10 08:56' union all
select 1, 'john', 'Logout', '2006-04-10 09:16' union all
select 1, 'john', 'Login', '2006-04-10 09:56' union all
select 1, 'john', 'CallOperator', '2006-04-10 10:00' union all
select 1, 'john', 'Attend', '2006-04-10 10:01' union all
select 1, 'john', 'Hold', '2006-04-10 10:05' union all
select 1, 'john', 'Attend', '2006-04-10 10:15' union all
select 1, 'john', 'logout', '2006-04-10 10:16' union all
select 1, 'john', 'Login', '2006-04-10 11:56' union all
select 1, 'john', 'CallOperator', '2006-04-10 12:00' union all
select 1, 'john', 'Attend', '2006-04-10 12:01' union all
select 1, 'john', 'logout', '2006-04-10 13:16' union all
select 2, 'jothi', 'Login', '2006-04-10 19:56' union all
select 2, 'jothi', 'CallOperator', '2006-04-10 20:00' union all
select 2, 'jothi', 'Attend', '2006-04-10 20:10' union all
select 2, 'jothi', 'Logout', '2006-04-10 20:10'

Hai, From the Above table, i would like to find out the
count of Actions for a particular operator for a day. means,
between FIRST LOGIN and LAST LOGOUT in a day, how many 'attend', how many 'CallOperator' and howman 'Hold' .
IF the opertor logged in and loggedout without any operation in between then i shouldnt consider that Sequence. How can i do this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-29 : 02:04:57

Read about Cross-tab reports in sql server help file. You will get an idea of how to do that

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-29 : 02:14:30

Sarakumar,

Your subject saided "how to get the max , min time in a day"
Are you referring to how to obtain the FIRST LOGIN & LAST LOGOUT ?
FIRST LOGIN = (select min(date_time) from @table where action = 'Login')
LAST_LOGOUT = (select max(date_time) from @table where action = 'Logout')

For the count of attend, CallOperator etc, follow Madhivanan's advice

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-05-30 : 22:06:22

hAI, Thanks for the reply. But the thing is Set of one month record will be there. among that i need to find out the Min login and MAx(Logout) for everyday. Here where i struk up..
That's y i asked here.
i just trying the get the logic.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-30 : 22:19:44

This will give you the cross-tab that you want.

select	dateadd(day, datediff(day, 0, date_time), 0),
	count(case when action = 'Attend' then 1 end) Count_Attend,
	count(case when action = 'CallOperator' then 1 end) Count_CallOperator,
	count(case when action = 'Hold' then 1 end) Count_Hold
from	table t
group by dateadd(day, datediff(day, 0, date_time), 0)

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-05-31 : 03:38:56

hAI,
Thanks for the Reply. Still working on the same,
One simple doubt. in case statment can i use two different column in the condition .
case 'Action' when 'login' then min(date_time) end like this...i w ant to implement the same logic to find out the max and min date_time. advise pls

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-31 : 03:43:16

"One simple doubt. in case statment can i use two different column in the condition ."
Yes.

"case 'Action' when 'login' then min(date_time) end "
The syntax is wrong. What you want to do over here ? You wanted the first login of the day and last login of the day ?

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-05-31 : 03:54:27

Hai,
Right i want to findout the first login and last logout of a day along with that, count of Actions.
i have done the count of actions, rowwise sum, columnwise sum. but now i want to find out first login and last logout.

quote:
Originally posted by khtan

"One simple doubt. in case statment can i use two different column in the condition ."
Yes.

"case 'Action' when 'login' then min(date_time) end "
The syntax is wrong. What you want to do over here ? You wanted the first login of the day and last login of the day ?

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-31 : 03:59:59

[code]select dateadd(day, datediff(day, 0, date_time), 0),
count(case when action = 'Attend' then 1 end) Count_Attend,
count(case when action = 'CallOperator' then 1 end) Count_CallOperator,
count(case when action = 'Hold' then 1 end) Count_Hold,
min(case when action = 'Login' then date_time end) as First_Login,
max(case when action = 'Logout' then date_time end) as Last_Logout
from @table t
group by dateadd(day, datediff(day, 0, date_time), 0)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources