Home Get All punch in and out for each employee?
Reply: 0

Get All punch in and out for each employee?

user1974 Published in July 18, 2018, 10:30 pm

Expected Output:

SL#  Emp#   Employee Name       IN                     OUT         
1    106    George Jacob 24/Sep/2017 8:08:00AM 24/Sep/2017 12:53:00PM 04:45:00
                         24/Sep/2017 2:13:00PM 24/Sep/2017 5:58:00PM 03:45:00
                         25/Sep/2017 8:12:00AM 25/Sep/2017 6:02:00PM 09:50:00
                         26/Sep/2017 8:18:00AM 26/Sep/2017 2:15:00PM 05:57:00
                         26/Sep/2017 2:32:00PM 26/Sep/2017 6:00:00PM 03:28:00
                         27/Sep/2017 8:02:00AM 27/Sep/2017 5:57:00PM 09:55:00
                         28/Sep/2017 8:01:00AM 28/Sep/2017 6:01:00PM 10:00:00
                         01/Oct/2017 8:16:00AM 01/Oct/2017 5:56:00PM 09:40:00
                         02/Oct/2017 7:58:00AM 02/Oct/2017 5:56:00PM

I tried this query but not get an exact output as mentioned above:

SELECT Row_number()
     OVER (ORDER BY A.dt ASC)                        AS SNo,
     CONVERT(DATE, A.dt)
   --CONVERT(VARCHAR(26), A.DT, 103)  as DATEEVENT, 
   Cast(LEFT(CONVERT(TIME, a.dt), 5) AS VARCHAR) AS 'time',
   Isnull(B.areaname, 'OAE6080036073000006')     AS areaname,
   Isnull(c.dept_name, '')                       AS group_name,
   CONVERT(CHAR(11), '2017/12/30', 103)          AS StartDate,
   CONVERT(CHAR(11), '2018/01/11', 103)          AS ToDate,
   0                                             AS emp_card_no
FROM   dbo.trnevents AS A
   LEFT OUTER JOIN dbo.employee AS B
                ON A.emp_reader_id = B.emp_reader_id
   LEFT OUTER JOIN dbo.departments AS C
                ON B.dept_id = C.dept_id
   LEFT OUTER JOIN dbo.devicepersonnelarea AS E
                ON A.pointid = E.areaid
   LEFT OUTER JOIN dbo.event_entry AS F
                ON A.eventid = F.event_entry_id  
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.300523 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO