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 72 - Calculate the Payroll Hours of Employees

This challenge invites you to solve a payroll challenge which requires to calculate the number of hours employee worked in a week.

The Payroll application for the agency expects the payroll data to be send in CSV file. And the CSV file will be created from the result set as shown in the expected results. There will be 1 header row per week for the Employee. The header row will show the Employee details, Week Start Date and Week End Date. Under 1 header row there can be multiple detail row each per earning code. The payroll hours will be summarized by Earning 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.

Sample Data

Employees Table:

  
EmpID EmpName
----- --------------
1	  John Torres
2	  Irina Williams

Payroll Week Table:

  
WeekID EmpID WeekStart  WeekEnd
------ ----- ---------- ----------
1	   1	 11-20-2011	11-26-2011
2	   2	 11-27-2011	12-03-2011
3	   1	 11-27-2011	12-03-2011

Employee Visits Table:

 
ID EmpID VisitDate	StartTime EndTime Earningcode
-- ----- ---------- --------- ------- -----------
1  1	 11-20-2011	10:00	  12:00	  Sat-Sun1
2  1	 11-21-2011	13:30	  16:00	  Mon-Fri1
3  1	 11-22-2011	14:00	  15:00	  Mon-Fri1
4  1	 11-24-2011	10:00	  14:00	  Mon-Fri1
5  1	 11-25-2011	13:30	  16:00	  Mon-Fri1
6  1	 11-26-2011	14:00	  15:00	  Sat-Sun1
7  2	 11-27-2011	09:00	  11:00	  Sat-Sun1
8  2	 11-28-2011	07:00	  12:00	  Mon-Fri1
9  2	 11-29-2011	09:00	  11:00	  Mon-Fri1
10 2	 12-03-2011	07:00	  12:00	  Sat-Sun1

Expected Results

  
RecordType EmpID EmpName        WeekStart  WeekEnd    Earning code Hours
---------- ----- -------------- ---------- ---------- ------------ -----
H	       1	 John Torres    11-20-2011 11-26-2011		
D					                                  Sat-Sun1	   3.00
D					                                  Mon-Fri1	   10.00
H	       2	 Irina Williams 11-27-2011 12-03-2011		
D					                                  Sat-Sun1	   7.00
D					                                  Mon-Fri1	   7.00

Rules

  1. The output should be ordered by EmpID, WeekStart and then by Record Type descending. Within a group of detail the output should be order by Hours and then Earning code desc.
  2. The RecordType column indicates whether the row is a header row or detail row.
  3. The start times and end times will have only 00, 15, 30, 45 for the minute part.

Sample Script

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

 
IF OBJECT_ID('TC72_Employees','U') IS NOT NULL BEGIN   
	DROP TABLE TC72_Employees  
END  
GO   
CREATE TABLE TC72_Employees (
	EmpID INT IDENTITY, 
	EmpName VARCHAR(100)
)  
GO    
INSERT INTO TC72_Employees(EmpName)  
SELECT 'John Torres' UNION ALL
SELECT 'Irina Williams'

SELECT * FROM TC72_Employees
GO

IF OBJECT_ID('TC72_PayrollWeek','U') IS NOT NULL BEGIN   
	DROP TABLE TC72_PayrollWeek 
END  
GO    
CREATE TABLE TC72_PayrollWeek(
	WeekID INT IDENTITY, 
	EmpID INT,
	WeekStart DATETIME,
	WeekEnd DATETIME
)  
GO    
INSERT INTO TC72_PayrollWeek(EmpID,WeekStart,WeekEnd)  
SELECT 1,'11-20-2011','11-26-2011' UNION ALL
SELECT 2,'11-27-2011','12-03-2011' UNION ALL
SELECT 1,'11-27-2011','12-03-2011'

SELECT * FROM TC72_PayrollWeek
GO

IF OBJECT_ID('TC72_EmployeeVisits','U') IS NOT NULL BEGIN   
	DROP TABLE TC72_EmployeeVisits
END  
GO    
CREATE TABLE TC72_EmployeeVisits(
	ID INT, 
	EmpID INT,
	VisitDate DATETIME,
	StartTime VARCHAR(5),
	EndTime VARCHAR(5),
	EarningCode VARCHAR(100)
)  
GO    
INSERT INTO TC72_EmployeeVisits(ID,EmpID,VisitDate,StartTime,EndTime,EarningCode)  
SELECT 1,1,'11-20-2011','10:00','12:00','Sat-Sun1' UNION ALL
SELECT 2,1,'11-21-2011','13:30','16:00','Mon-Fri1' UNION ALL
SELECT 3,1,'11-22-2011','14:00','15:00','Mon-Fri1' UNION ALL
SELECT 4,1,'11-24-2011','10:00','14:00','Mon-Fri1' UNION ALL
SELECT 5,1,'11-25-2011','13:30','16:00','Mon-Fri1' UNION ALL
SELECT 6,1,'11-26-2011','14:00','15:00','Sat-Sun1' UNION ALL
SELECT 7,2,'11-27-2011','09:00','11:00','Sat-Sun1' UNION ALL
SELECT 8,2,'11-28-2011','07:00','12:00','Mon-Fri1' UNION ALL
SELECT 9,2,'11-29-2011','09:00','11:00','Mon-Fri1' UNION ALL
SELECT 10,2,'12-03-2011','07:00','12:00','Sat-Sun1'

SELECT * FROM TC72_EmployeeVisits

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.

puzzles
13 · 13% · 4057
Submit a Solution
Previous Challenge
Next Challenge
10
Liked
12
Comments
93
Solutions



Submit

TSQL Challenge 72 - Calculate the Payroll Hours of Employees

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.