Several technicians are sent to a customer premises to do a certain maintenance work. Your task is to process the activity log entered by each technician and identify overlaps in the time sheet.
The company sends one or more technicians to perform maintenance work at customer premises. Each technician takes care of a specific task and maintains a time sheet. Technicians are categorized into different Grades. A technician with a lower Grade is billed at a ‘higher’ rate. It is quite possible that the time sheet submitted by the technicians overlap.
The company, however, does not want to have overlapping time sheet while billing the customer. In case of an overlap, the company wants to bill the customer only for the time spent by the technician with the highest billing rate. For example, if there is an overlap with a Grade1 technician and Grade3 technician, the company will bill only the time of the Grade1 technician for the overlapping period.
- Sample Data
JobID Technician Grade StartTime EndTime ----- ---------- ----- ------------------- ------------------- 1 Michael 1 2010-01-01 09:08:00 2010-01-01 12:00:00 1 John 2 2010-01-01 13:00:00 2010-01-01 16:24:00 1 Steve 3 2010-01-01 08:53:00 2010-01-01 16:53:00
- Expected Results
JobID Technician Grade StartTime EndTime ----- ---------- ----- ------------------- ------------------- 1 John 2 2010-01-01 13:00:00 2010-01-01 16:24:00 1 Michael 1 2010-01-01 09:08:00 2010-01-01 12:00:00 1 Steve 3 2010-01-01 08:53:00 2010-01-01 09:08:00 1 Steve 3 2010-01-01 12:00:00 2010-01-01 13:00:00 1 Steve 3 2010-01-01 16:24:00 2010-01-01 16:53:00
- Rule
- The duration of a job may be a few hours, or even a few days.
- A single technician can have multiple non-overlapping time entries for the same job with all grade values being the same.
- The output should be ordered by JobID, Technician, StartTime.
- The time sheet will contain time values rounded to the nearest minute.
- A Job cannot have two technicians having the same Grade .
- 個人解法
SELECT
JobID ,
Technician ,
Grade ,
[1] AS StartTime ,
[2] AS EndTime
FROM
(
SELECT
JobID ,
Technician ,
Grade ,
Date ,
((ROW_NUMBER() OVER (PARTITION BY JobID , Technician , Grade ORDER BY Date) - 1) / 2) + 1 AS GrpNO ,
((ROW_NUMBER() OVER (PARTITION BY JobID , Technician , Grade ORDER BY Date) - 1) % 2) + 1 AS ROWNO
FROM
(
SELECT
T2.JobID ,
T2.Technician ,
T2.Grade ,
T2.StartTime AS ST1 ,
T2.EndTime AS ET1 ,
T1.StartTime AS ST2 ,
T1.EndTime AS ET2
FROM TC49 AS T1
JOIN TC49 AS T2 ON T1.Grade <= T2.Grade AND
(T1.StartTime BETWEEN T2.StartTime AND T2.EndTime
OR T1.EndTime BETWEEN T2.StartTime AND T2.EndTime)
) AS P1
UNPIVOT
(
Date FOR ColName IN ([ST1],[ET1],[ST2],[ET2])
) AS PV1
GROUP BY JobID , Technician , Grade , Date
) AS P2
PIVOT
(
MAX(Date) FOR ROWNO IN ([1],[2])
) AS PV2
ORDER BY JobID , Technician , StartTime
- 延伸閱讀
- [SQL]次序函數應用-資料分組
沒有留言:
張貼留言