Click here to Skip to main content
14,579,188 members
Rate this:
Please Sign up or sign in to vote.
I have developed procedure with the help of you...Now i need guidance that when i import attendance of Employee data in sql table then i want to generate daily date for each employee in a table as per date range ,although attendance intime and outtime is available or not of employee, but date must generate for those employee who status in Joined.,

What I have tried:

i tried those employee who are absent means intime and out time are not coming then their record is coming by using right Join ,but i want daily date for each employee to be generate ....in this regard need your help...thanks

<pre>;With CTE As
    (
    select EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeeDetails.OTEntitled,EmployeeDetails.Empcur,EmployeeDetails.Dhour,EmployeeDetails.LTime from EmployeeDetails where Empcur='Join'
    )
    ,CTE4 As
    (
    Select MachineAttendance.EmpID,MachineAttendance.Datetime,MachineAttendance.INOUT from MachineAttendance
    ) 
    ,cte1 AS
    (
    SELECT CTE4.EmpID, CAST(CTE4.Datetime as Date) AS [Date], 
    CASE WHEN CTE4.INOUT = 1 THEN CTE4.DateTime END AS INOUT_INTIME,
    CASE WHEN CTE4.INOUT = 2 THEN CTE4.DateTime END AS INOUT_OUTTIME
    From 
     CTE4
    
    ), 
   cte2 
    as
    (
    select cte1.EmpID, Date, MAX(INOUT_INTIME) AS INTIME, 
                          MAX(INOUT_OUTTIME) AS OUTTIME
    , DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) as [Hours]
    FROM CTE1
    GROUP BY EmpID, [Date]
    )
    select cte.EmpID,cte.EmpName,cte2.Date, cte.OTEntitled,CTE.Empcur,CTE.Dhour,cte.LTime,cte2.INTIME,  cte2.OUTTIME,  cte2.[Hours]
    , CASE WHEN  cte2.[Hours] >= 8 THEN 1
    WHEN  cte2.[Hours] = 0 THEN 0
    WHEN  cte2.[Hours] >= 6 THEN 0.5 END AS [Day],
    CASE WHEN  cte2.[Hours] > CTE.Dhour then  cte2.[Hours] - CTE.Dhour else 0 End as OT,
    CASE when   
    cte.OTEntitled = 'Yes'  AND cte2.[Hours] >= CTE.Dhour 
THEN (( cte2.[Hours] - 8) * 100) else 0 END AS OTAMount,  
   -- cte2.[Hours] >= 8 
     -- THEN ( cte2.[Hours] - 8) * 100 else 0 END AS OTAMount,
   
    Convert(varchar(10), cte2.INTIME,108) as [Time],
    Case When Convert(Time, cte2.INTIME,108) > cte.LTime Then 1 else 0 end as Late    
    from cte2  
    right Join cte ON cte.EmpId= cte2.EmpID
    order by cte2.Date asc
Posted
Updated 19-Jun-20 15:43pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You can use for example recursive CTE to generate desired dates. Consider the following example
CREATE TABLE Person (
  PersonId int
);

CREATE TABLE Attendance (
  PersonId int,
  InTime   datetime,
  OutTime  datetime
);

INSERT INTO Person (PersonId) VALUES 
   (1),
   (2),
   (3);

INSERT INTO Attendance (PersonId, InTime, OutTime) VALUES 
   (1, CONVERT(datetime, '2019-01-04 09:00:00.000', 120), CONVERT(datetime, '2019-01-04 12:00:00.000', 120)),
   (1, CONVERT(datetime, '2019-01-04 13:00:00.000', 120), CONVERT(datetime, '2019-01-04 17:00:00.000', 120)),
   (1, CONVERT(datetime, '2019-01-06 10:00:00.000', 120), CONVERT(datetime, '2019-01-06 17:00:00.000', 120)),
   (3, CONVERT(datetime, '2019-01-06 10:00:00.000', 120), CONVERT(datetime, '2019-01-06 11:00:00.000', 120));


;WITH Dates (ReportingDate) AS (
   SELECT CONVERT(date, '2019-01-01 00:00:00.000', 120) AS ReportingDate
   UNION ALL
   SELECT DATEADD(day, 1, d.ReportingDate)
   FROM Dates d
   WHERE d.ReportingDate < CONVERT(date, '2019-01-10 00:00:00.000', 120)
),
AttendanceHours AS (
   SELECT a.PersonId,
          CAST(a.InTime as Date) AS AttendanceDate,
		  MIN(a.InTime) AS MinIn,
		  MAX(a.OutTime) AS MaxOut
   FROM Attendance a
   GROUP BY a.PersonId,
           CAST(InTime as Date)
)
SELECT p.PersonId,
	   d.ReportingDate ,
	   ah.MinIn,
	   ah.MaxOut
FROM Person p
     CROSS APPLY Dates d
	 LEFT JOIN AttendanceHours ah ON ah.PersonId = p.PersonId AND ah.AttendanceDate = d.ReportingDate
ORDER BY p.PersonId,
	   d.ReportingDate 
OPTION (MAXRECURSION 1000);


The result is
PersonId   ReportingDate   MinIn                     MaxOut
--------   -------------   -----                     ------
1          2019-01-01      NULL                      NULL
1          2019-01-02      NULL                      NULL
1          2019-01-03      NULL                      NULL
1          2019-01-04      2019-01-04 09:00:00.000   2019-01-04 17:00:00.000
1          2019-01-05      NULL                      NULL
1          2019-01-06      2019-01-06 10:00:00.000   2019-01-06 17:00:00.000
1          2019-01-07      NULL                      NULL
1          2019-01-08      NULL                      NULL
1          2019-01-09      NULL                      NULL
1          2019-01-10      NULL                      NULL
2          2019-01-01      NULL                      NULL
2          2019-01-02      NULL                      NULL
2          2019-01-03      NULL                      NULL
2          2019-01-04      NULL                      NULL
2          2019-01-05      NULL                      NULL
2          2019-01-06      NULL                      NULL
2          2019-01-07      NULL                      NULL
2          2019-01-08      NULL                      NULL
2          2019-01-09      NULL                      NULL
2          2019-01-10      NULL                      NULL
3          2019-01-01      NULL                      NULL
3          2019-01-02      NULL                      NULL
3          2019-01-03      NULL                      NULL
3          2019-01-04      NULL                      NULL
3          2019-01-05      NULL                      NULL
3          2019-01-06      2019-01-06 10:00:00.000   2019-01-06 11:00:00.000
3          2019-01-07      NULL                      NULL
3          2019-01-08      NULL                      NULL
3          2019-01-09      NULL                      NULL
3          2019-01-10      NULL                      NULL
   
Comments
Maciej Los 6-Jan-19 11:50am
   
Looks perfect to me!
Member 12314309 6-Jan-19 21:52pm
   
Respected Wendelius,,,,THanks ALot sir ...Most respect for you.....Thanks again
Wendelius 6-Jan-19 23:52pm
   
You're welcome :)
Member 13431498 19-Jun-20 21:45pm
   
very very very good and thank you and thanks again

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100