July 26, 2006 at 6:25 am
Please help me to query from the following.
The number of distinct rows of system needs to be counted.
If the same system is accessed by the same employee within the past 24 hours, it should be counted as one.
set dateformat dmy
drop table emp_amount
create table emp_amount (dt datetime, empid int, Description varchar(20) )
insert emp_amount values ('01/10/2006 02:10:00',1,'System1') -- 1
insert emp_amount values ('01/10/2006 02:12:00',1,'System2') -- 1
insert emp_amount values ('01/10/2006 02:20:00',1,'System3') -- 1
insert emp_amount values ('01/10/2006 02:30:00',2,'System1') -- 2
insert emp_amount values ('01/10/2006 15:10:00',2,'System3') -- 2
insert emp_amount values ('01/10/2006 19:10:00',2,'System2') -- 2
insert emp_amount values ('01/10/2006 20:10:00',1,'System4') -- 1
insert emp_amount values ('01/10/2006 22:10:00',1,'System1')
insert emp_amount values ('02/10/2006 01:10:00',1,'System2')
insert emp_amount values ('02/10/2006 02:09:00',1,'System1')
insert emp_amount values ('02/10/2006 02:11:00',1,'System2')
insert emp_amount values ('02/10/2006 02:19:00',1,'System3')
insert emp_amount values ('02/10/2006 15:09:00',1,'System1') -- 3
insert emp_amount values ('02/10/2006 19:11:00',1,'System3') -- 3
insert emp_amount values ('02/10/2006 20:19:00',1,'System2') -- 3
insert emp_amount values ('02/10/2006 09:10:00',1,'System1')
insert emp_amount values ('02/10/2006 12:09:00',1,'System2')
insert emp_amount values ('02/10/2006 12:11:00',1,'System3')
insert emp_amount values ('02/10/2006 13:19:00',2,'System3') -- 4
The result set is given below:
Description Count
System3 4
System1 3
System2 3
System4 1
Can anyone help me please?
Thanks in advance...
Prakash
R
July 26, 2006 at 7:16 am
SELECT [Description], COUNT(*) AS [Count]
FROM (SELECT DISTINCT [Description], empid, DATEADD(day,DATEDIFF(day,0,dt),0) AS [dt]
FROM emp_amount) a
GROUP BY [Description]
What exactly do you mean by 'past 24 hours' ?
Far away is close at hand in the images of elsewhere.
Anon.
July 26, 2006 at 7:21 am
past 24 hours means in the same day.
Your query helped me a lot ... This is what i wanted.
Thanks
R
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply