Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 64 - A Payroll calculation Challenge

This challenge invites you to solve a payroll challenge which requires special calculation of holidays and absences that are adjacent to holidays.

Database Source Control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Bring your database development process forward by 5 years. Get started now.

Sample Data

Holidays Table
ID  Date
--- ----------
1   2011-08-03
2   2011-08-19
Attendance Table
EmpID Date       
----- ----------
10001 2011-08-01
10001 2011-08-02
10001 2011-08-05

10001 2011-08-08
10001 2011-08-09
10001 2011-08-10
10001 2011-08-11
10001 2011-08-12

10001 2011-08-15
10001 2011-08-16
10001 2011-08-17
10001 2011-08-18

10001 2011-08-22
10001 2011-08-23
10001 2011-08-24
10001 2011-08-25

10001 2011-08-29
10001 2011-08-30
10001 2011-08-31

Your job is to calculate the number of absences for each employee and produce the following output.

Expected Results

EmpID AbsentDt
----- ----------
10001 2011-08-03
10001 2011-08-04
10001 2011-08-13
10001 2011-08-14
10001 2011-08-19
10001 2011-08-20
10001 2011-08-21
10001 2011-08-26
10001 2011-08-27
10001 2011-08-28

Rules

  1. Sundays are non-working days.
  2. First and Last saturday of the month are non-working days. First saturday is the saturday that follows the first friday of the month. (This indicates that, if the 1st of a month is a Saturday, it is not considered as the first saturday).
  3. Days defined in the holiday table are non-working days.
  4. All other days (except for the above) are working days.
  5. If an attendance record is not found in the attendance table for a "working day", it is considered as an absence.
  6. If an employee is absent on a working day that is adjacent to a non-working day, then he/she is considered as absent for the entire absence period (including non-working) days. For example, if Saturday and Sundays are holidays and the employee is absent on Monday (or on Friday), 3 absences will be counted. Similarly, if Tuesday and Thursday are holidays and employee is absent on wednesday, 3 absences will be counted.
  7. No employee will work on a holiday/non-working day.
  8. You can assume that both "Holidays" and "Attendace" tables will contains the data of only one month. Your query needs to process the data of only the given month.
  9. It is possible that there are no records in the "holidays" table for a month.
  10. You can assume that all employees will have at least one record in the "attendance" table.
  11. The output should be ordered by EmpID, AbsentDt
  12. You can assume SET DATEFIRST=7.

Sample Script

Use the TSQL Script given below to generate the source table and fill them with sample data.

  
IF OBJECT_ID('TC64_Holidays','U') IS NOT NULL BEGIN   
	DROP TABLE TC64_Holidays  
END  
GO    
CREATE TABLE TC64_Holidays(ID INT IDENTITY,Date DATETIME)  
GO    
INSERT INTO TC64_Holidays(Date)  
SELECT '2011-08-03' UNION ALL
SELECT '2011-08-19'

SELECT * FROM TC64_Holidays 
GO
IF OBJECT_ID('TC64_Attendance','U') IS NOT NULL BEGIN   
	DROP TABLE TC64_Attendance  
END  
GO    
CREATE TABLE TC64_Attendance(EmpID INT,Date DATETIME)  
GO    
INSERT INTO TC64_Attendance(EmpID, Date)  
SELECT '10001','2011-08-01' UNION ALL
SELECT '10001','2011-08-02' UNION ALL
SELECT '10001','2011-08-05' UNION ALL
SELECT '10001','2011-08-08' UNION ALL
SELECT '10001','2011-08-09' UNION ALL
SELECT '10001','2011-08-10' UNION ALL
SELECT '10001','2011-08-11' UNION ALL
SELECT '10001','2011-08-12' UNION ALL
SELECT '10001','2011-08-15' UNION ALL
SELECT '10001','2011-08-16' UNION ALL
SELECT '10001','2011-08-17' UNION ALL
SELECT '10001','2011-08-18' UNION ALL
SELECT '10001','2011-08-22' UNION ALL
SELECT '10001','2011-08-23' UNION ALL
SELECT '10001','2011-08-24' UNION ALL
SELECT '10001','2011-08-25' UNION ALL
SELECT '10001','2011-08-29' UNION ALL
SELECT '10001','2011-08-30' UNION ALL
SELECT '10001','2011-08-31' 

SELECT * FROM TC64_Attendance
    

Restrictions

  1. The solution should be a single query that starts with a "SELECT" or “;WITH”

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
Database Source Control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Bring your database development process forward by 5 years. Get started now.

Jacob Sebastian
1 · 100% · 32235
Submit a Solution
Previous Challenge
Next Challenge
5
Liked
21
Comments
19
Solutions



Submit

TSQL Challenge 64 - A Payroll calculation Challenge

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.