Sandwich Query For Employee Attendance using SQL Server 2005

Mohammad Salmani

Hi,

I am looking a sandwich query for my attendance module, If the employee is absent on Saturday and Monday then sunday will be calculate as Absent the same has to be case of holidays.

The attendance table structure in SQL 2005 as per below.

EmpCode
EmpShift
AttDate
AttStatus
.
.

Please Advice/provide some solution for the above.

Thanks & Kind Regards
Mohammad Salmani

Lost User

Mohammad Salmani wrote:

provide some solution

Sorry, this site does not provide code to order. Google will find you many SQL tutorials that will help.

SeeSharp2

LEFT JOIN to a table that has all dates and find those that do not have a match.

Mohammad Salmani

I am having the below data in Attendance Table EmpCode AttMonth AttYear AttDate ShiftCode AttStatus 1 6 2021 2021-06-01 00:00:00.000 1001 P 1 6 2021 2021-06-02 00:00:00.000 1001 P 1 6 2021 2021-06-03 00:00:00.000 1001 P 1 6 2021 2021-06-04 00:00:00.000 1001 P 1 6 2021 2021-06-05 00:00:00.000 1002 A 1 6 2021 2021-06-06 00:00:00.000 1009 WO 1 6 2021 2021-06-07 00:00:00.000 1001 A 1 6 2021 2021-06-08 00:00:00.000 1001 P 1 6 2021 2021-06-09 00:00:00.000 1001 P I need to update 'WO' as 'A' while processing attendance please help for the above.

SeeSharp2

I can't help because I do not understand what you need help with.

David Mujica

A few SQL functions may be of help for you .. Take a look at this query

SELECT *,DATEPART(wk, AttDate) As 'Week Number',DATEPART(weekday, AttDate) As 'Week Day', DATENAME(WEEKDAY,ATTDATE) FROM #TEMP1

Mycroft Holmes

I would do the as 3 queries: 1 get the first absent date date - store it in date1 variable 2 get the next absent date > date 1 - if it exists store it in date2 variable If date2 is not null (more than 1 day) 3 set all records between date1 and date2 to absent This covers multi day holidays.

Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP

SeeSharp2

But this will only work on 1 record at a time.

Mycroft Holmes

One Employee at a time, correct. So put it in a loop through all employees.

Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP

Mohammad Salmani

Hi, I have implemented as per below query,If Employee is absent on Saturday and Monday the weeklyoff should be counted as Absent. I have passed the process date parameter in place of static date. SELECT A.EmpCode,A.AttDate As Sundate FROM Attendance_Details A INNER JOIN (SELECT A.Empcode,A.AttDate As SatDate,A.AttStatus As SatStatus FROM Attendance_Details A INNER JOIN (SELECT EmpCode,AttDate As MonDate,AttStatus As MonStatus FROM Attendance_Details WHERE AttDate='2021-06-07' AND DATENAME(WEEKDAY, '2021-06-07') = 'Monday' and AttStatus IN ('A','HL'))T ON T.EmpCode=A.EmpCode WHERE DATENAME(WEEKDAY, DATEADD(day,-2,'2021-06-07')) = 'Saturday' AND AttStatus IN ('A') AND AttDate=DATEADD(day,-2,'2021-06-07'))T1 ON A.EmpCode=T1.EmpCode AND DATENAME(WEEKDAY, DATEADD(day,-1,'2021-06-07')) = 'Sunday' AND AttStatus NOT IN('AL','SL','PL','ML','FL') AND AttDate=DATEADD(day,-1,'2021-06-07') Please help to improve the above query... Thanks & Regards Mohammad Salmani

SeeSharp2

Mycroft Holmes wrote:

loop

Loops are a last resort in databases. They are built to work on sets.