CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mohammed
Mohammed

Reputation: 101

change the output

Sorry I am new to sql world could this statement be checkin time different with checkout time and remove checkinorout from the statement as you well see :

SELECT  
  USERINFO.Badgenumber AS USERID, 
  CHECKINOUT.CHECKTIME AS Checkin, 
  CHECKINOUT.CHECKTIME AS Checkout, 
  Machines.MachineAlias, 
  CHECKINOUT.checkinorout
FROM CHECKINOUT 
  INNER JOIN USERINFO ON CHECKINOUT.USERID = USERINFO.USERID 
  INNER JOIN Machines ON CHECKINOUT.SENSORID = Machines.MachineNumber
WHERE (CHECKINOUT.CHECKTIME > '2014-09-25 00:00:00.000') 
order by checkin

Output:

  USERID    Checkin              Checkout               Machines checkinorout
    32  2014-09-25 09:12:57.000 2014-09-25 09:12:57.000 HQ       Checkin   
    32  2014-09-25 12:58:51.000 2014-09-25 12:58:51.000 HQ       CheckOut  
    32  2014-09-26 18:03:33.000 2014-09-26 18:03:33.000 HQ       Checkin    
    32  2014-09-26 22:03:11.000 2014-09-26 22:03:11.000 HQ       CheckOut   
    32  2014-09-27 12:57:55.000 2014-09-27 12:57:55.000 HQ       Checkin    
    32  2014-09-27 17:01:32.000 2014-09-27 17:01:32.000 HQ       CheckOut   
    32  2014-09-28 13:05:03.000 2014-09-28 13:05:03.000 HQ       Checkin    
    32  2014-09-28 17:35:29.000 2014-09-28 17:35:29.000 HQ       CheckOut  
    32  2014-09-29 09:18:12.000 2014-09-29 09:18:12.000 HQ       Checkin    
    32  2014-09-29 18:10:43.000 2014-09-29 18:10:43.000 HQ       CheckOut   
    32  2014-09-30 09:12:13.000 2014-09-30 09:12:13.000 HQ       Checkin

I need the output be like this 

USERID  Checkin                 Checkout                 Machines 
    32  2014-09-25 09:12:57.000 2014-09-25 12:58:51.000  HQ         
    32  2014-09-26 18:03:33.000 2014-09-26 22:03:11.000  HQ         
    32  2014-09-27 12:57:55.000 2014-09-27 17:01:32.000  HQ
    32  2014-09-28 13:05:03.000 2014-09-28 17:35:29.000  HQ          
    32  2014-09-29 09:18:12.000 2014-09-29 18:10:43.000  HQ

Thanks MR @sgeddes but when i edit your statement to have a result 

with cte as (
  select *,
    row_number() over (partition by userid, checkinorout order by CHECKTIME) rn
  from CHECKINOUT
  )
select userid, 
    max(case when checkinorout = 'checkin' then CHECKTIME end) checkin, 
    max(case when checkinorout = 'checkout' then CHECKTIME end) checkout

from cte where USERID=15 and  CHECKTIME between '2014-9-21 00:00:00.000' and '2014-10-25 00:00:00.000'
group by userid , rn 


userid checkin  checkout 
15  NULL    2014-09-21 18:50:24.000
15  NULL    2014-09-22 18:06:15.000
15  NULL    2014-09-23 18:01:30.000
15  NULL    2014-09-24 16:52:36.000
15  NULL    2014-09-25 12:58:51.000
15  NULL    2014-09-26 22:03:11.000
15  NULL    2014-09-27 17:01:32.000
15  NULL    2014-09-28 17:35:29.000
15  NULL    2014-09-29 18:10:43.000
15  NULL    2014-09-30 18:11:19.000
15  NULL    2014-10-01 17:52:49.000
15  NULL    2014-10-12 20:13:10.000
15  2014-09-21 10:17:24.000 2014-10-13 22:13:11.000
15  2014-09-22 09:18:29.000 2014-10-14 21:49:28.000
15  2014-09-23 09:10:15.000 2014-10-15 10:14:09.000
15  2014-09-24 09:43:27.000 2014-10-16 17:55:06.000
15  2014-09-25 09:12:57.000 2014-10-17 23:17:00.000
15  2014-09-26 18:03:33.000 2014-10-20 12:38:22.000
15  2014-09-27 12:57:55.000 2014-10-21 07:31:39.000
15  2014-09-28 13:05:03.000 2014-10-22 05:51:47.000
15  2014-09-29 09:18:12.000 2014-10-24 11:26:06.000
15  2014-09-30 09:12:13.000 NULL
15  2014-10-01 10:16:59.000 NULL
15  2014-10-02 10:13:52.000 NULL
15  2014-10-03 00:59:18.000 NULL
15  2014-10-11 22:07:06.000 NULL
15  2014-10-12 09:58:34.000 NULL
15  2014-10-13 05:09:34.000 NULL
15  2014-10-14 11:42:58.000 NULL
15  2014-10-15 04:48:27.000 NULL
15  2014-10-15 15:49:06.000 NULL
15  2014-10-16 09:14:21.000 NULL
15  2014-10-16 21:14:18.000 NULL
15  2014-10-17 00:59:57.000 NULL
15  2014-10-18 17:42:26.000 NULL
15  2014-10-20 01:22:01.000 NULL
15  2014-10-21 01:24:30.000 NULL
15  2014-10-22 00:10:34.000 NULL
15  2014-10-23 20:01:02.000 NULL
15  2014-10-24 01:08:51.000 NULL

and i need it to be 

userid checkin              checkout
15  2014-09-21 10:17:24.000 2014-09-21 18:50:24.000
15  2014-09-22 09:18:29.000 2014-09-22 18:06:15.000
15  2014-09-23 09:10:15.000 2014-09-23 18:01:30.000
15  2014-09-24 09:43:27.000 2014-09-24 16:52:36.000
15  2014-09-25 09:12:57.000 2014-09-25 12:58:51.000
15  2014-09-26 18:03:33.000 2014-09-26 22:03:11.000
15  2014-09-27 12:57:55.000 2014-09-27 17:01:32.000

Thanks Mr @slavoo

what i have when i try ....

    SELECT  
  u.Badgenumber AS USERID, 
  c.CHECKTIME AS Checkout, 
  checkingOut.time AS Checkin, 
  m.MachineAlias
FROM CHECKINOUT c
  INNER JOIN USERINFO u ON c.USERID = u.USERID 
  INNER JOIN Machines m ON c.SENSORID = m.MachineNumber
OUTER APPLY
(
  SELECT top 1 c2.CHECKTIME as time FROM CHECKINOUT c2 
  WHERE c2.checkinorout = 'Checkout' 
  AND c.USERID = c2.USERID
  AND c.SENSORID = c2.SENSORID
  AND c.CHECKTIME < c2.CHECKTIME
  order by c2.CHECKTIME asc
 ) as checkingOut
WHERE  (c.CHECKTIME between  '2014-10-19 00:00:00.000' and '2014-10-27 00:00:00.000' ) 
    and u.Badgenumber=660  
  AND c.checkinorout = 'Checkin' 
  AND checkingOut.time is not null
order by checkingOut.time

The output:

   USERID   Checkout             Checkin                MachineAlias
    660 2014-10-19 01:56:47.000 2014-10-19 17:27:41.000 Branch4 
    660 2014-10-20 02:00:14.000 2014-10-20 17:39:35.000 Branch4 
    660 2014-10-21 01:55:49.000 2014-10-21 16:57:22.000 Branch4 
    660 2014-10-22 01:59:23.000 2014-10-25 16:48:29.000 Branch4 
    660 2014-10-23 16:59:34.000 2014-10-25 16:48:29.000 Branch4 
    660 2014-10-24 16:58:36.000 2014-10-25 16:48:29.000 Branch4 
    660 2014-10-25 01:56:47.000 2014-10-25 16:48:29.000 Branch4

and i need it to be

 USERID   Checkout               Checkin                MachineAlias
    660 2014-10-20 02:00:14.000 2014-10-19 17:27:41.000 Branch4 
    660 2014-10-21 01:55:49.000 2014-10-20 17:39:35.000 Branch4 
    660 2014-10-22 01:59:23.000 2014-10-21 16:57:22.000 Branch4 
    660 Null                    2014-10-23 16:59:34.000 Branch4
    660 2014-10-25 01:56:47.000 2014-10-24 16:58:36.000 Branch4 
    660 2014-10-26 00:55:35.000 2014-10-25 16:48:29.000 Branch4

and this is the table i read from it

    select USERID ,CHECKTIME,checkinorout from CHECKINOUT where USERID=80 and CHECKTIME between  '2014-10-19 00:00:00.000' and '2014-10-27 00:00:00.000'
order by CHECKTIME

and this is the full transaction for this user

80  2014-10-19 01:56:47.000 Checkin   
80  2014-10-19 17:27:41.000 CheckOut  
80  2014-10-20 02:00:14.000 Checkin   
80  2014-10-20 17:39:35.000 CheckOut  
80  2014-10-21 01:55:49.000 Checkin   
80  2014-10-21 16:57:22.000 CheckOut  
80  2014-10-22 01:59:23.000 Checkin   
80  2014-10-23 16:59:34.000 Checkin   
80  2014-10-24 16:58:36.000 Checkin   
80  2014-10-25 01:56:47.000 Checkin   
80  2014-10-25 16:48:29.000 CheckOut  
80  2014-10-26 00:55:35.000 Checkin

for your note i have script its update the check in or out in checkinorout column

to know this report be finger print check in and check out report and if he found no check in leave it Null and if he found the check out null leave it null ( this employ have shift check in 5:00:00 pm and check out 2:00:00 am ) ; .

Upvotes: 3

Views: 116

Answers (2)

slavoo
slavoo

Reputation: 6076

SELECT  
  u.Badgenumber AS USERID, 
  c.CHECKTIME AS Checkin, 
  checkingOut.time AS Checkout, 
  m.MachineAlias
FROM CHECKINOUT c
  INNER JOIN users u ON c.USERID = u.USERID 
  INNER JOIN Machines m ON c.SENSORID = m.MachineNumber
OUTER APPLY
(
  SELECT top 1 c2.CHECKTIME as time FROM CHECKINOUT c2 
  WHERE c2.checkinorout = 'Checkout' 
  AND c.USERID = c2.USERID
  AND c.SENSORID = c2.SENSORID
  AND c.CHECKTIME < c2.CHECKTIME
  AND CAST(c.CHECKTIME as DATE) = CAST(c2.CHECKTIME as DATE)
  order by c2.CHECKTIME asc
 ) as checkingOut
WHERE (c.CHECKTIME > '2014-09-25 00:00:00.000') 
  AND c.checkinorout = 'Checkin' 
order by checkin

SQL Fiddle

SQL Fiddle after update

Or this:

SELECT  
  u.Badgenumber AS USERID, 
  c.CHECKTIME AS Checkin, 
  checkingOut.time AS Checkout, 
  Machines.MachineAlias
FROM CHECKINOUT c
  INNER JOIN users u ON c.USERID = u.USERID 
  INNER JOIN Machines ON c.SENSORID = Machines.MachineNumber
OUTER APPLY
(
  SELECT top 1 c2.CHECKTIME as time FROM CHECKINOUT c2 
  WHERE c2.checkinorout = 'Checkout' 
  AND c.USERID = c2.USERID
  AND c.SENSORID = c2.SENSORID
  AND c.CHECKTIME < c2.CHECKTIME
  AND NOT EXISTS (SELECT * FROM CHECKINOUT c3                       
                  WHERE c3.checkinorout = 'Checkin'
                  AND c3.USERID = c2.USERID
                  AND c3.SENSORID = c2.SENSORID
                  AND c3.CHECKTIME > c.CHECKTIME
                  AND c3.CHECKTIME < c2.CHECKTIME)
  order by c2.CHECKTIME asc
 ) as checkingOut
WHERE (c.CHECKTIME > '2014-09-25 00:00:00.000') 
  AND c.checkinorout = 'Checkin'       
order by checkin

SqlFiddle

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

You can use ROW_NUMBER() to establish a grouping between your checkins and checkouts. Then you need to pivot your results -- you can use MAX with CASE to do that:

Here's a simplified version using a common table expression:

with cte as (
  select *,
    row_number() over (partition by userid, checkinorout order by checkin) rn
  from results
  )
select userid, 
    max(case when checkinorout = 'checkin' then checkin end) checkin, 
    max(case when checkinorout = 'checkout' then checkout end) checkout, 
    machines
from cte
group by userid, machines, rn

Edit, given your comments, can you not just use a HAVING clause to remove the NULL records?

having max(case when checkinorout = 'checkin' then checkin end) is not null
  and  max(case when checkinorout = 'checkout' then checkout end) is not null

Upvotes: 1

Related Questions