Table Schema
(
DeviceLogId int
DeviceId int
UserId int
LogDate datetime
)
Table data
DeviceLogId DeviceId UserId LogDate
112 25 66 2015-07-22 11:02:15.000
332 25 66 2015-07-22 17:29:25.000
555 25 88 2015-07-23 19:09:35.000
779 25 67 2015-07-24 16:23:49.000
1003 29 17 2015-07-18 13:03:04.000
I want output like
Intime Outtime logdate Incount OutCount
2015-01-01 10:22:29.000 2015-01-01 19:58:43.000 2015-01-01 7 7
2015-01-02 09:52:26.000 2015-01-02 20:25:25.000 2015-01-02 2 2
I want to display user
office intime and office outtime, number of time punch machine count...
select e.Intime,e.Outtime,e.logdate,e.Incount,e.OutCount from
(
select a.intime as Intime,b.outtime as Outtime,c.logdate,c.InCount as Incount,d.OutCount as OutCount from
(
select min(logdate) intime,cast(LogDate as date) logdate,userid
from DeviceLogs where deviceid in (26,31) and cast(LogDate as date) between '2015-01-01' and '2015-01-02' and UserId=7
group by cast(LogDate as date), UserId
)a inner join
(
select max(logdate) outtime,cast(LogDate as date) logdate,userid from DeviceLogs
where deviceid in (25,30) and cast(LogDate as date) between '2015-01-01' and '2015-01-02' and UserId=7
group by cast(LogDate as date), UserId
) b on a.logdate = b.logdate
left join
(
select UserId, cast(LogDate as date) logdate ,count(DeviceLogId) as InCount from DeviceLogs
where deviceid in (26,31) and cast(LogDate as date) between '2015-01-01' and '2015-01-02' and UserId=7
Group by UserId, cast(LogDate as date)
)c on b.logdate = c.logdate
left join
(
select UserId, cast(LogDate as date) logdate ,count(DeviceLogId) as OutCount from DeviceLogs
where deviceid in (25,30) and cast(LogDate as date) between '2015-01-01' and '2015-01-02' and UserId=7
Group by UserId, cast(LogDate as date)
)d on c.logdate = d.logdate
)e
I have create query but its working for single user only... I want this result for all user of the system.
Basically I want to dispaly all employee intime,outime,number of machine input , and out put count.
Waiting for your reply
Thanks in advance.