How to add daily date for each employee in attendance table ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

SQL

<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 6-Jan-19 4:45am

Member 12314309

Updated 19-Jun-20 15:43pm

Add a Solution

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)

Wendelius · Accepted Answer · 2019-01-06T05:19:00

You can use for example recursive CTE to generate desired dates. Consider the following example

SQL

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