How do I...creae query for report for display every employee working hours

Question

0.00/5 (No votes)

See more:

Table Schema
(
DeviceLogId int
DeviceId    int
UserId      int
LogDate    datetime
)

CSS

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

CSS

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...

SQL

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.

Posted 3-Nov-15 21:22pm

bharatmsp

Add a Solution

Comments

Corporal Agarn 4-Nov-15 6:45am

You have made this more complicated than it needs to be. Try it with only one query (no sub queries).

jaket-cp 4-Nov-15 8:54am

From the sub-queries the script is checking for and UserId=7
Also deviceid in (25,30) and deviceid in (26,31) have been specified.
Do these relate to a "punch-in"/"punch-out"?
In the sample data, there is a DeviceId of 29, which is not in the query.
I assume in the output data, you would like to have the UserId.

Please update your question to make it more clear on your requirements, so we may be able to help.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

seravan · Answer 1 · 2015-11-11T20:10:00

Try this:

SQL

SELECT InDevice.UserId
    , MIN(InDevice.LogDate) [In Time]
    , MAX(OutDevice.LogDate) [Out Time]
    , CONVERT(nvarchar(10), InDevice.LogDate,20) [Log Date]
    , COUNT(InDevice.LogDate) [IN Count]
    , COUNT(OutDevice.LogDate) [OUT Count]
FROM DeviceLogs InDevice
LEFT OUTER JOIN DeviceLogs OutDevice ON InDevice.UserID = OutDevice.UserID
    AND OutDevice.DeviceID in (25,30) --given that these are the only machine that for Punching Out
WHERE InDevice.DeviceID in (26,31) --given that these machine for Punching In
GROUP BY InDevice.UserId, CONVERT(nvarchar(10), InDevice.LogDate,20)