September 9, 2018 at 9:33 pm
Query needed, where to award billing time of a case based on a employee with various priority time shifts.
Any help would be appreciated.
1. Case can start at 7:00 AM and end at 7:00 AM Next Day.
3. Shifts can start at 7:00 AM and End at 7:00 AM Next Day
4. Shifts time can overlap each other.
5. Employee will have 1 to 5 shifts assigned.
6. Employee will always have ShiftID 99
7. Billing Time will be awarded to Shift with the Lowest Shift Priority Number
Example #1
Case Table
CaseID, EmployeeID, CaseStartTime, CaseEndTime
111111, 2222222222, 1/1/2018 7:10 AM, 1/1/2018 5:20 PM
Shift Table
Date, EmployeeID, ShiftID, ShiftPriority, ShiftStartTime, ShiftEndTime
1/1/2018, 2222222222, 1, 1, 7:30 AM, 4:00 PM
1/1/2018, 2222222222, 22, 22, 4:00 PM, 12:00 AM
1/1/2018, 2222222222, 44, 44, 12:00 AM, 7:00 AM
1/1/2018, 2222222222, 99, 99, 7:00 AM, 7:00 AM
Desired Query Output
CaseID, Date, EmployeeID, ShiftID, BillStartTime, BillEndTime
111111, 1/1/2018, 2222222222, 1, 7:30 AM, 4:00 PM
111111, 1/1/2018, 2222222222, 22, 4:00 PM, 5:20 PM
111111, 1/1/2018, 2222222222, 99, 7:10 AM, 7:30 AM
Example #2, same CaseID but only using ShiftID 1 and 99
Case Table
CaseID, EmployeeID, CaseStartTime, CaseEndTime
111111, 2222222222, 1/1/2018 7:10 AM, 1/1/2018 5:20 PM
Shift Table
Date, EmployeeID, ShiftID, ShiftPriority, ShiftStartTime, ShiftEndTime
1/1/2018, 2222222222, 1, 1, 7:30 AM, 4:00 PM
1/1/2018, 2222222222, 99, 99, 7:00 AM, 7:00 AM
Desired Query Output
CaseID, Date, EmployeeID, ShiftID, BillStartTime, BillEndTime
111111, 1/1/2018, 2222222222, 1, 7:30 AM, 4:00 PM
111111, 1/1/2018, 2222222222, 99, 7:10 AM, 7:30 AM
111111, 1/1/2018, 2222222222, 99, 4:00 PM, 5:20 PM
Example #3, Different CaseID, shifts with time gaps and overlaps.
Case Table
CaseID, EmployeeID, CaseStartTime, CaseEndTime
111115, 2222222222, 1/1/2018 7:10 AM, 1/1/2018 9:47 PM
Shift Table
Date, EmployeeID, ShiftID, ShiftPriority, ShiftStartTime, ShiftEndTime
1/1/2018, 2222222222, 1, 1, 7:30 AM, 4:00 PM
1/1/2018, 2222222222, 33, 33, 5:00 PM, 6:00 PM
1/1/2018, 2222222222, 34, 34, 7:00 PM, 7:30 PM
1/1/2018, 2222222222, 35, 35, 7:00 PM, 10:00 PM
1/1/2018, 2222222222, 99, 99, 7:00 AM, 7:00 AM
Desired Query Output
CaseID, Date, EmployeeID, ShiftID, BillStartTime, BillEndTime
111115, 1/1/2018, 2222222222, 1, 7:30 AM, 4:00 PM
111115, 1/1/2018, 2222222222, 33, 5:00 PM, 6:00 PM
111115, 1/1/2018, 2222222222, 34, 7:00 PM, 7:30 PM
111115, 1/1/2018, 2222222222, 35, 7:30 PM, 9:47 PM
111115, 1/1/2018, 2222222222, 99, 7:10 AM, 7:30 AM
111115, 1/1/2018, 2222222222, 99, 4:00 PM, 5:00 PM
111115, 1/1/2018, 2222222222, 99, 6:00 PM, 7:00 PM
September 9, 2018 at 9:50 pm
What have you tried so far? People here will help you if you make an honest effort.
Pretty good job posting data... but CREATE TABLE and INSERT scripts will get you tested answers.
Since you're new, please read this article on posting best practices.
September 11, 2018 at 1:06 pm
Here is what I have so far. I am currently working on Part 3, Part 4 and Part 5.
If you have any suggestion on another approach, please feel free to share the information.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Create the tables used in the query
CREATE TABLE [dbo].[Cases](
[CaseID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[CaseStart] [datetime] NULL,
[CaseEnd] [datetime] NULL,
CONSTRAINT [PK_Cases] PRIMARY KEY CLUSTERED
(
[CaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Shifts](
[Date] [date] NOT NULL,
[EmployeeID] [int] NOT NULL,
[ShiftID] [int] NOT NULL,
[ShiftPriority] [int] NULL,
[ShiftStart] [datetime] NULL,
[ShiftEnd] [datetime] NULL,
CONSTRAINT [PK_Shifts] PRIMARY KEY CLUSTERED
(
[Date] ASC,
[EmployeeID] ASC,
[ShiftID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Billing](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CaseID] [int] NOT NULL,
[Date] [date] NOT NULL,
[EmployeeID] [int] NOT NULL,
[ShiftID] [int] NOT NULL,
[ShiftCounter] [int] NULL,
[BillStart] [datetime] NULL,
[BillEnd] [datetime] NULL,
CONSTRAINT [PK_Billing] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--Insert the input data into the Cases Table
INSERT INTO [dbo].[Cases] ([CaseID],[EmployeeID] ,[CaseStart],[CaseEnd])
VALUES (111111,222222,'1/1/2018 7:10 AM','1/1/2018 5:20PM')
INSERT INTO [dbo].[Cases] ([CaseID],[EmployeeID] ,[CaseStart],[CaseEnd])
VALUES (111112,333333,'1/1/2018 7:10 AM','1/1/2018 5:20PM')
INSERT INTO [dbo].[Cases] ([CaseID],[EmployeeID] ,[CaseStart],[CaseEnd])
VALUES (111113,444444,'1/1/2018 7:10 AM','1/1/2018 11:20PM')
----Insert the input data into the Shifts Table
INSERT INTO [dbo].[Shifts] ([Date],[EmployeeID],[ShiftID],[ShiftPriority],[ShiftStart],[ShiftEnd])
VALUES ('1/1/2018', 222222, 1, 1, '1/1/2018 7:30 AM', '1/1/2018 4:00 PM')
INSERT INTO [dbo].[Shifts] ([Date],[EmployeeID],[ShiftID],[ShiftPriority],[ShiftStart],[ShiftEnd])
VALUES ('1/1/2018', 222222, 22, 22, '1/1/2018 4:00 PM', '1/2/2018 12:00 AM')
INSERT INTO [dbo].[Shifts] ([Date],[EmployeeID],[ShiftID],[ShiftPriority],[ShiftStart],[ShiftEnd])
VALUES ('1/1/2018', 222222, 44, 44, '1/2/2018 12:00 AM', '1/2/2018 7:00 AM')
INSERT INTO [dbo].[Shifts] ([Date],[EmployeeID],[ShiftID],[ShiftPriority],[ShiftStart],[ShiftEnd])
VALUES ('1/1/2018', 222222, 99, 99, '1/1/2018 7:00 AM', '1/2/2018 7:00 AM')
INSERT INTO [dbo].[Shifts] ([Date],[EmployeeID],[ShiftID],[ShiftPriority],[ShiftStart],[ShiftEnd])
VALUES ('1/1/2018', 333333, 1, 1, '1/1/2018 7:30 AM', '1/1/2018 4:00 PM')
INSERT INTO [dbo].[Shifts] ([Date],[EmployeeID],[ShiftID],[ShiftPriority],[ShiftStart],[ShiftEnd])
VALUES ('1/1/2018', 333333, 99, 99, '1/1/2018 7:00 AM', '1/2/2018 7:00 AM')
INSERT INTO [dbo].[Shifts] ([Date],[EmployeeID],[ShiftID],[ShiftPriority],[ShiftStart],[ShiftEnd])
VALUES ('1/1/2018', 444444, 1, 1, '1/1/2018 7:30 AM', '1/1/2018 4:00 PM')
INSERT INTO [dbo].[Shifts] ([Date],[EmployeeID],[ShiftID],[ShiftPriority],[ShiftStart],[ShiftEnd])
VALUES ('1/1/2018', 444444, 33, 33, '1/1/2018 5:00 PM', '1/1/2018 6:00 PM')
INSERT INTO [dbo].[Shifts] ([Date],[EmployeeID],[ShiftID],[ShiftPriority],[ShiftStart],[ShiftEnd])
VALUES ('1/1/2018', 444444, 34, 34, '1/1/2018 7:00 PM', '1/1/2018 7:30 PM')
INSERT INTO [dbo].[Shifts] ([Date],[EmployeeID],[ShiftID],[ShiftPriority],[ShiftStart],[ShiftEnd])
VALUES ('1/1/2018', 444444, 35, 35, '1/1/2018 7:00 PM', '1/1/2018 10:00 PM')
INSERT INTO [dbo].[Shifts] ([Date],[EmployeeID],[ShiftID],[ShiftPriority],[ShiftStart],[ShiftEnd])
VALUES ('1/1/2018', 444444, 99, 99, '1/1/2018 7:00 AM', '1/2/2018 7:00 AM')
--Test Query
--Rules
--1. Case can start at 7:00 AM and end at 7:00 AM Next Day.
--2. Shifts can start at 7:00 AM and End at 7:00 AM Next Day
--3. Shifts time can overlap each other.
--4. Employee will have 1 to 5 shifts assigned.
--5. Employee will always have ShiftID 99
--6. Billing Time will be awarded to Shift with the Lowest Shift Priority Number
--Initial thought was to run 5 queries Part 0001 to Part 0005 (because Rule #5 states that there could be upt to 5 shifts)
--Starting from the lowest shift priority number going to the highest, awarding Billing Start and End time to Case time that has not been taken.
--Maintenance to delete
DELETE FROM Billing
--Part 0001, The lowest ShiftPriority number shift will always get the billing time if the case time is within the shift start and end times.
INSERT INTO Billing
--Q3 Select fields for Insert into Billing. Calculate BillStart and BillEnd Values (where Case time is within the Shift time)
SELECT CaseID, Date, EmployeeID, ShiftID, 1,
--Logic used to determine BillStart and BillEnd
CASE WHEN ShiftStart > CaseStart
THEN ShiftStart
ELSE CaseStart
END AS BillStart,
CASE WHEN ShiftEnd < CaseEnd
THEN ShiftEnd
ELSE CaseEnd
END AS BillEnd
FROM
(
--Q2 Join Shifts Table for other needed fields
SELECT Q1.CaseID, Q1.EmployeeID, Q1.CaseStart, Q1.CaseEnd, Shifts_1.Date, Shifts_1.ShiftID, Shifts_1.ShiftStart, Shifts_1.ShiftEnd
FROM
(
--Q1 Select the shift with the lowest ShiftPriority number
SELECT dbo.Cases.CaseID, dbo.Cases.EmployeeID, dbo.Cases.CaseStart, dbo.Cases.CaseEnd, MIN(dbo.Shifts.ShiftPriority) AS ShiftPriorityMin
FROM dbo.Cases INNER JOIN
dbo.Shifts ON dbo.Cases.EmployeeID = dbo.Shifts.EmployeeID
GROUP BY dbo.Cases.CaseID, dbo.Cases.EmployeeID, dbo.Cases.CaseStart, dbo.Cases.CaseEnd
) AS Q1
INNER JOIN dbo.Shifts AS Shifts_1 ON Q1.EmployeeID = Shifts_1.EmployeeID AND Q1.ShiftPriorityMin = Shifts_1.ShiftPriority
) AS Q2
--Part 0002, Process the next lowest ShiftPriority. This Shift could start before and/or end after shift that was processed in Part 0001.
INSERT INTO Billing
--Need to Union 0002a and 0002b. If we 'INSERT INTO Billing' Part 0002a, It Changes Part 0002b Q1 because records would be added to the Billing table.
--Part 0002a, Look at shifts which start before shift that was processed in Part 0001
--Q5 Select for Insert into Billing
SELECT CaseID, Date, EmployeeID, ShiftID, 2, BillStart,
--Logic used to determine BillEnd
CASE WHEN (BillEnd > ExistingBillStart)
THEN
ExistingBillStart
ELSE
BillEnd
END AS BillEnd
FROM
(
--Q4 Join Billing Table, Only use records where Q3.BillStart < Billing_1.BillStart
SELECT Q3.CaseID, Q3.Date, Q3.EmployeeID, Q3.ShiftID, Q3.BillStart, Q3.BillEnd, Billing_1.ShiftID AS ExistingShiftID, Billing_1.ShiftCounter AS ExistingShiftCounter, Billing_1.BillStart AS ExistingBillStart, Billing_1.BillEnd AS ExistingBillEnd
FROM
(
--Q3 Calculate BillStart and BillEnd Values
SELECT CaseID, Date, EmployeeID, ShiftID, CASE WHEN ShiftStart > CaseStart THEN ShiftStart ELSE CaseStart END AS BillStart, CASE WHEN ShiftEnd < CaseEnd THEN ShiftEnd ELSE CaseEnd END AS BillEnd
FROM
(
--Q2 Join with Shifts Table for other needed fields
SELECT Q1.CaseID, Q1.EmployeeID, Q1.CaseStart, Q1.CaseEnd, Shifts_1.Date, Shifts_1.ShiftID, Shifts_1.ShiftStart, Shifts_1.ShiftEnd
FROM
(
--Q1 Select lowest priority shift that has not been already processed.
SELECT dbo.Cases.CaseID, dbo.Cases.EmployeeID, dbo.Cases.CaseStart, dbo.Cases.CaseEnd, MIN(dbo.Shifts.ShiftPriority) AS ShiftPriorityMin
FROM dbo.Cases INNER JOIN
dbo.Shifts ON dbo.Cases.EmployeeID = dbo.Shifts.EmployeeID LEFT OUTER JOIN
dbo.Billing ON dbo.Shifts.EmployeeID = dbo.Billing.EmployeeID AND dbo.Shifts.ShiftID = dbo.Billing.ShiftID AND dbo.Shifts.Date = dbo.Billing.Date
GROUP BY dbo.Cases.CaseID, dbo.Cases.EmployeeID, dbo.Cases.CaseStart, dbo.Cases.CaseEnd, dbo.Billing.CaseID
HAVING (dbo.Billing.CaseID IS NULL)
) AS Q1 INNER JOIN
dbo.Shifts AS Shifts_1 ON Q1.EmployeeID = Shifts_1.EmployeeID AND Q1.ShiftPriorityMin = Shifts_1.ShiftPriority
) AS Q2
) AS Q3 INNER JOIN
dbo.Billing AS Billing_1 ON Q3.CaseID = Billing_1.CaseID AND Q3.Date = Billing_1.Date AND Q3.EmployeeID = Billing_1.EmployeeID
WHERE Q3.BillStart < Billing_1.BillStart
) AS Q4
UNION
--Part 0002b Process Cases Where Bill End Time is After ExistingBillEnd
--Q5 Select for Insert into Billing
SELECT CaseID, Date, EmployeeID, ShiftID, 2,
--Logic used to determine BillStart
CASE WHEN (BillStart < ExistingBillEnd)
THEN
ExistingBillEnd
ELSE
BillStart
END AS BillStart, BillEnd
FROM
(
--Q4 Join Billing Table, Only use records where Q3.BillEnd > Billing_1.BillEnd
SELECT Q3.CaseID, Q3.Date, Q3.EmployeeID, Q3.ShiftID, Q3.BillStart, Q3.BillEnd, Billing_1.ShiftID AS ExistingShiftID, Billing_1.ShiftCounter AS ExistingShiftCounter, Billing_1.BillStart AS ExistingBillStart, Billing_1.BillEnd AS ExistingBillEnd
FROM
(
--Q3 Calculate BillStart and BillEnd Values
SELECT CaseID, Date, EmployeeID, ShiftID, CASE WHEN ShiftStart > CaseStart THEN ShiftStart ELSE CaseStart END AS BillStart, CASE WHEN ShiftEnd < CaseEnd THEN ShiftEnd ELSE CaseEnd END AS BillEnd
FROM
(
--Q2 Join with Shifts Table for other needed fields
SELECT Q1.CaseID, Q1.EmployeeID, Q1.CaseStart, Q1.CaseEnd, Shifts_1.Date, Shifts_1.ShiftID, Shifts_1.ShiftStart, Shifts_1.ShiftEnd
FROM
(
--Q1 Select lowest priority shift that has not been already processed. Use the Billing table to see processed ShiftID(s).
SELECT dbo.Cases.CaseID, dbo.Cases.EmployeeID, dbo.Cases.CaseStart, dbo.Cases.CaseEnd, MIN(dbo.Shifts.ShiftPriority) AS ShiftPriorityMin
FROM dbo.Cases INNER JOIN
dbo.Shifts ON dbo.Cases.EmployeeID = dbo.Shifts.EmployeeID LEFT OUTER JOIN
dbo.Billing ON dbo.Shifts.EmployeeID = dbo.Billing.EmployeeID AND dbo.Shifts.ShiftID = dbo.Billing.ShiftID AND dbo.Shifts.Date = dbo.Billing.Date
GROUP BY dbo.Cases.CaseID, dbo.Cases.EmployeeID, dbo.Cases.CaseStart, dbo.Cases.CaseEnd, dbo.Billing.CaseID
HAVING (dbo.Billing.CaseID IS NULL)
) AS Q1 INNER JOIN
dbo.Shifts AS Shifts_1 ON Q1.EmployeeID = Shifts_1.EmployeeID AND Q1.ShiftPriorityMin = Shifts_1.ShiftPriority
) AS Q2
) AS Q3 INNER JOIN
dbo.Billing AS Billing_1 ON Q3.CaseID = Billing_1.CaseID AND Q3.Date = Billing_1.Date AND Q3.EmployeeID = Billing_1.EmployeeID
WHERE Q3.BillEnd > Billing_1.BillEnd
) AS Q4
--Part 0003, Process the next lowest ShiftPriority. This Shift could start before and/or end after shift that was processed in Part 0001.
--This part will be more complex because it is possible that there is a gap in time between the shifts used during Part 0001 and Part 0003.
--For Part 0004 there could be two gaps, For Part 0005 there could be three gaps.
September 11, 2018 at 1:13 pm
With that much query to review, I'd rather know ahead of time exactly what "assigning billing time" really means. Why would there ever be work that's not in the "billable" category? Also, when you say "billable" in the words you are using, it appears to have a meaning that is different than a traditional meaning. What is the meaning of "assigning billing time to the shift with the lowest priority" ? From what point of view? What time period is the basis for such "assignment". I'm pretty sure I'm not on your page for this, because I'm pretty sure I don't have any of your definitions for those concepts. Please explain in detail, as it's just not obvious.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 11, 2018 at 1:20 pm
This is a variant on interval packing. Here is what I came up with. I think there is a better way to approach this, but I don't really have time to work on it right now.
BEGIN TRY
SELECT *
INTO #Case
FROM
(
VALUES(15, 2, CAST('20180101 07:10' AS Datetime), CAST('20180101 21:47' AS datetime))
)v(CaseID, EmpID, CaseStartTime, CaseEndTime)
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
SELECT dt, EmpID, v.ShiftID, v.ShiftID AS ShiftPriority, v.ShiftStartTime, v.ShiftEndTime
INTO #Shift
FROM
( VALUES
(CAST('20180101' AS date), 2, 1, CAST('7:30 AM' AS time(0)), CAST('4:00 PM' AS time(0))),
(CAST('20180101' AS date), 2, 33, '5:00 PM', '6:00 PM'),
(CAST('20180101' AS date), 2, 34, '7:00 PM', '7:30 PM'),
(CAST('20180101' AS date), 2, 35, '7:00 PM', '10:00 PM'),
(CAST('20180101' AS date), 2, 99, '7:00 AM', '7:00 AM')
) v(dt, EmpId, ShiftID, ShiftStartTime, ShiftEndTime)
END TRY
BEGIN CATCH
END CATCH;
/* THIS IS THE BEGINING OF THE ACTUAL SOLUTION */
WITH EmployeeShifts AS
(
SELECT
s.EmpId, s.dt, s.ShiftID, d.ShiftPriority
, d.ShiftStartDatetime, d.ShiftEndDatetime
FROM #Shift s
CROSS APPLY
(
VALUES
(
s.ShiftPriority
, DATEADD(DAY, DATEDIFF(DAY, s.ShiftStartTime, s.dt), CAST(s.ShiftStartTime AS datetime))
, DATEADD(DAY, DATEDIFF(DAY, s.ShiftEndTime, s.dt) + CASE WHEN s.ShiftEndTime <= s.ShiftStartTime THEN 1 ELSE 0 END, CAST(s.ShiftEndTime AS datetime))
)
) d(ShiftPriority, ShiftStartDatetime, ShiftEndDatetime)
)
,EmployeeShiftsUnpacked AS
(
SELECT DISTINCT es.dt, es.EmpId, d.dt AS ShiftStartDatetime, LEAD(d.dt) OVER(PARTITION BY es.EmpID, es.dt ORDER BY d.dt) AS ShiftEndDatetime, c.CaseStartTime, c.CaseEndTime, c.CaseID
FROM EmployeeShifts es
INNER JOIN #Case c
ON es.EmpId = c.EmpID
AND es.ShiftStartDatetime < c.CaseEndTime
AND es.ShiftEndDatetime >= c.CaseStartTime
CROSS APPLY ( VALUES(es.ShiftStartDatetime), (es.ShiftEndDatetime), (c.CaseStartTime), (c.CaseEndTime) ) d(dt)
WHERE d.dt BETWEEN c.CaseStartTime AND c.CaseEndTime
)
SELECT esu.CaseID, CAST(esu.ShiftStartDatetime AS DATE) AS Dt, esu.EmpId, ps.ShiftID, ps.ShiftPriority, CAST(esu.ShiftStartDatetime AS TIME(0)) AS BillStartTime, CAST(esu.ShiftEndDatetime AS TIME(0)) AS BillEndTime
FROM EmployeeShiftsUnpacked esu
CROSS APPLY
(
SELECT TOP 1 ps.ShiftID, ps.ShiftPriority
FROM EmployeeShifts ps
WHERE ps.EmpId = esu.EmpId
AND ps.dt = esu.dt
AND ps.ShiftStartDatetime < esu.ShiftEndDatetime
AND ps.ShiftEndDatetime > esu.ShiftStartDatetime
ORDER BY ps.ShiftPriority
) ps
WHERE esu.ShiftStartDatetime < esu.ShiftEndDatetime
ORDER BY ShiftPriority, ShiftStartDatetime
I have only tried it against the one example, but it should work for all cases.
Part of the problem you're facing is that shifts are cyclical and SQL Server doesn't work well with cyclical data, so I've "flattened" out the cycles into a normal timeline.
Essentially, the code "unpivots" the start and end times and then uses LEAD to calculate the new end to the interval. You do have to filter out cases where the start and end times are the same from the final results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply