-2
DECLARE  @EmpTable AS TABLE (EmpId Int, EmpName Nvarchar(255)) 
INSERT INTO @EmpTable (EmpId, EmpName) VALUES (001, 'Paul')
INSERT INTO @EmpTable (EmpId, EmpName) VALUES (002, 'Juan')
INSERT INTO @EmpTable (EmpId, EmpName) VALUES (003, 'Elise')
INSERT INTO @EmpTable (EmpId, EmpName) VALUES (004, 'John')
INSERT INTO @EmpTable (EmpId, EmpName) VALUES (005, 'Smith')


DECLARE  @Duty AS TABLE (ID Int, StaffId Int, Shedule Nvarchar(255), BreakTime Nvarchar(255), DutyDate DateTime, Status Nvarchar(50) ) 
INSERT INTO @Duty (ID, StaffId, Shedule, BreakTime, DutyDate, Status) VALUES (1, 001, '11:00PM - 8:00AM', '02:00AM - 03:00AM', '2015-07-25', 'Sick Leave')
INSERT INTO @Duty (ID, StaffId, Shedule, BreakTime, DutyDate) VALUES (2, 002, '11:00PM - 8:00AM', '02:00AM - 03:00AM', '2015-07-25')
INSERT INTO @Duty (ID, StaffId, Shedule, BreakTime, DutyDate) VALUES (3, 003, '04:00PM - 11:00PM', '08:00PM - 09:00PM', '2015-07-26')
INSERT INTO @Duty (ID, StaffId, Shedule, BreakTime, DutyDate) VALUES (4, 004, '08:00AM - 4:00PM', '12:00PM - 01:00PM', '2015-07-25')
INSERT INTO @Duty (ID, StaffId, Shedule, BreakTime, DutyDate) VALUES (5, 005, '04:00PM - 11:00PM', '08:00PM - 09:00PM', '2015-07-26')


DECLARE  @Attendance AS TABLE (ID Int, StaffNo Int, DeviceId Int, Clock DateTime)  
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (1, 004, 0, '2015-07-25 08:01:00AM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (2, 004, 1, '2015-07-25 12:05:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (3, 004, 0, '2015-07-25 12:48:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (4, 004, 1, '2015-07-25 04:01:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (5, 002, 0, '2015-07-25 10:44:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (6, 002, 1, '2015-07-25 02:01:00AM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (7, 002, 0, '2015-07-25 03:05:00AM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (8, 002, 1, '2015-07-25 08:15:00AM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (9, 003, 0, '2015-07-26 03:48:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (10, 003, 1, '2015-07-26 08:20:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (11, 003, 0, '2015-07-26 09:30:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (12, 003, 1, '2015-07-26 11:10:10PM')

how to get below result using above tables

Result

  • Where are you getting the "PunchIn" values from, i get that 004 matches the value in @Attendance, but for example: 10:44:00AM for 002 is nowhere to be found. Please elaborate on where you are getting your values in your output table from. – Randi Vertongen Nov 16 '18 at 10:37
  • I am getting it from Clock in @Attendance table. May its not showing the way I wanted as I am new in this forum – user165130 Nov 16 '18 at 11:10
  • No problem, i just think that the clock value is a bit wrong then in the example? Everything is at 2015-07-25 08:01:00AM – Randi Vertongen Nov 16 '18 at 11:56
0

Your question did not give us a lot to go on, but I took a stab at it anyway.

My solution has several assumptions:

  1. It looks like your attendance data is such that there will always be 4 rows for each StaffNo such that (ordered by time), row1=PunchIn, row2=BreakOut, row3=BreakIn and row4=ExitTime. My solution is built on that assumption.
  2. You did not indicate how your result set was supposed to be order, so my results are in a different order than your example, but the data is the same as your results.
  3. You did not indicate that you wanted a solution that handles infinite number of days, so my solution used only the data you provided in your question.
  4. I don't think the Attendance data is correct for StaffNo=2. It looks like they punch in on 7/25, but take a break early morning on 7/26. I adjusted your data to reflect that.
  5. I think the ExitTime for StaffNo=4 does not match your results. My results reflect the actual data provided.

--Demo setup with your original data

DECLARE  @EmpTable AS TABLE (EmpId Int, EmpName Nvarchar(255)) 
INSERT INTO @EmpTable (EmpId, EmpName) VALUES (001, 'Paul')
INSERT INTO @EmpTable (EmpId, EmpName) VALUES (002, 'Juan')
INSERT INTO @EmpTable (EmpId, EmpName) VALUES (003, 'Elise')
INSERT INTO @EmpTable (EmpId, EmpName) VALUES (004, 'John')
INSERT INTO @EmpTable (EmpId, EmpName) VALUES (005, 'Smith')


DECLARE  @Duty AS TABLE (ID Int, StaffId Int, Shedule Nvarchar(255), BreakTime Nvarchar(255), DutyDate DateTime, Status Nvarchar(50) ) 
INSERT INTO @Duty (ID, StaffId, Shedule, BreakTime, DutyDate, Status) VALUES (1, 001, '11:00PM - 8:00AM', '02:00AM - 03:00AM', '2015-07-25', 'Sick Leave')
INSERT INTO @Duty (ID, StaffId, Shedule, BreakTime, DutyDate) VALUES (2, 002, '11:00PM - 8:00AM', '02:00AM - 03:00AM', '2015-07-25')
INSERT INTO @Duty (ID, StaffId, Shedule, BreakTime, DutyDate) VALUES (3, 003, '04:00PM - 11:00PM', '08:00PM - 09:00PM', '2015-07-26')
INSERT INTO @Duty (ID, StaffId, Shedule, BreakTime, DutyDate) VALUES (4, 004, '08:00AM - 4:00PM', '12:00PM - 01:00PM', '2015-07-25')
INSERT INTO @Duty (ID, StaffId, Shedule, BreakTime, DutyDate) VALUES (5, 005, '04:00PM - 11:00PM', '08:00PM - 09:00PM', '2015-07-26')


DECLARE  @Attendance AS TABLE (ID Int, StaffNo Int, DeviceId Int, Clock DateTime)  
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (1, 004, 0, '2015-07-25 08:01:00AM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (2, 004, 1, '2015-07-25 12:05:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (3, 004, 0, '2015-07-25 12:48:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (4, 004, 1, '2015-07-25 04:01:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (5, 002, 0, '2015-07-25 10:44:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (6, 002, 1, '2015-07-26 02:01:00AM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (7, 002, 0, '2015-07-26 03:05:00AM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (8, 002, 1, '2015-07-26 08:15:00AM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (9, 003, 0, '2015-07-26 03:48:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (10, 003, 1, '2015-07-26 08:20:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (11, 003, 0, '2015-07-26 09:30:00PM')
INSERT INTO @Attendance (ID, StaffNo, DeviceId, Clock) VALUES (12, 003, 1, '2015-07-26 11:10:10PM')

--My solution based on several assumptions
WITH ScheduleStart
AS (
    SELECT StaffId
        ,convert(DATE, dutydate) AS DutyDate
        ,min(substring(shedule, 1, CHARINDEX('-', shedule) - 2)) AS ScheduleStart
    FROM @Duty
    GROUP BY staffid
        ,STATUS
        ,DutyDate
    )
    ,AttendanceInfoAndRowNumber
AS (
    SELECT ROW_NUMBER() OVER (
            PARTITION BY StaffNo ORDER BY Clock
            ) AS rn
        ,*
    FROM @Attendance
    )
SELECT d.ID
    ,d.StaffId AS stid
    ,PunchIn.clock AS PunchIn
    ,CASE 
        WHEN convert(TIME, punchin.clock) > convert(TIME, ss.ScheduleStart)
            THEN 
            RIGHT('0' + CAST(DATEDIFF(S, ss.ScheduleStart, punchin.clock) / 3600 AS VARCHAR(2)), 2) + 
            ':' + 
            RIGHT('0' + CAST(DATEDIFF(S, ss.ScheduleStart, punchin.clock) % 3600 / 60 AS VARCHAR(2)), 2) + 
            ':' + 
            RIGHT('0' + CAST(DATEDIFF(S, ss.ScheduleStart, punchin.clock) % 60 AS VARCHAR(2)), 2)
        END AS LateIn
    ,Breakout.clock AS BreakOut
    ,Breakin.clock AS BreakIn
    ,ExitTime.clock AS ExitTime
    ,d.DutyDate AS DutyDate
    ,CASE 
        WHEN d.STATUS IS NOT NULL
            THEN d.STATUS
        WHEN a.StaffNo IS NULL
            THEN 'Absent'
        ELSE 'Present'
        END AS STATUS
--,d.*
FROM @Duty d
JOIN ScheduleStart ss
    ON ss.StaffId = d.StaffId
LEFT JOIN AttendanceInfoAndRowNumber a
    ON a.StaffNo = d.StaffId
        AND d.DutyDate = convert(DATE, a.clock)
OUTER APPLY (
    SELECT convert(VARCHAR(10), convert(TIME(0), clock), 109) AS clock
    FROM AttendanceInfoAndRowNumber
    WHERE StaffNo = a.StaffNo 
        AND rn = 1
    ) AS punchin
OUTER APPLY (
    SELECT convert(VARCHAR(10), convert(TIME(0), clock), 109) AS clock
    FROM AttendanceInfoAndRowNumber
    WHERE StaffNo = a.StaffNo 
        AND rn = 2
    ) AS Breakout
OUTER APPLY (
    SELECT convert(VARCHAR(10), convert(TIME(0), clock), 109) AS clock
    FROM AttendanceInfoAndRowNumber
    WHERE StaffNo = a.StaffNo 
        AND rn = 3
    ) AS BreakIn
OUTER APPLY (
    SELECT convert(VARCHAR(10), convert(TIME(0), clock), 109) AS clock
    FROM AttendanceInfoAndRowNumber
    WHERE StaffNo = a.StaffNo 
        AND rn = 4
    ) AS ExitTime
WHERE a.rn = 1
    OR a.rn IS NULL
ORDER BY d.ID
    ,d.StaffId

My results

| ID | stid | PunchIn    | LateIn   | BreakOut   | BreakIn    | ExitTime   | DutyDate                | STATUS     |
|----|------|------------|----------|------------|------------|------------|-------------------------|------------|
| 1  | 1    | NULL       | NULL     | NULL       | NULL       | NULL       | 2015-07-25 00:00:00.000 | Sick Leave |
| 2  | 2    | 10:44:00PM | NULL     | 2:01:00AM  | 3:05:00AM  | 8:15:00AM  | 2015-07-25 00:00:00.000 | Present    |
| 3  | 3    | 3:48:00PM  | NULL     | 8:20:00PM  | 9:30:00PM  | 11:10:10PM | 2015-07-26 00:00:00.000 | Present    |
| 4  | 4    | 8:01:00AM  | 00:01:00 | 12:05:00PM | 12:48:00PM | 4:01:00PM  | 2015-07-25 00:00:00.000 | Present    |
| 5  | 5    | NULL       | NULL     | NULL       | NULL       | NULL       | 2015-07-26 00:00:00.000 | Absent     |
| improve this answer | |
  • Thank you for your support. I have tried it with sql server 2005 and you code it seems is used in later version, so it didn't work and I have change some according to 2005 but it didn't give the result – user165130 Nov 20 '18 at 5:22
  • I assumed you were on a supported version of SQL Server (at least SQL Server 2008). I've update the tag in your question to reflect that you are SQL Sever 2005. – Scott Hodgin Nov 20 '18 at 9:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.