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.

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

  
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

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.

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

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

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.