December 6, 2021 at 8:23 pm
Thank you Ken. This script works like a charm! You rock!
December 6, 2021 at 8:56 pm
I am glad it helped but you will need to test it very carefully before it goes near production. You should especially try to think of every boundary condition and compare it to Scotts JOIN solution. BTW both Joe and Jeff are correct, the schema is horrible and should really be fixed by the supplier. Of course sometimes one just has to live with what is there.
December 7, 2021 at 8:47 pm
Jeff Moden wrote:ScottPletcher wrote:Jeff Moden wrote:This is so wrong. Your company is trying to have you fix the wrong problem. The real problem is the source of the data and THAT's what actually needs to be fixed. For example, if you look at the first two rows, 10:00 + 720 minutes (which is exactly 6 hours) cannot lead to a break start time of 10:30 on the next line.
Huh? I think it's clear that the first row is the entire shift duration, and the next row is simply the first break that occurred in the shift.
Huh, hell. 😀 10:00 +720 minutes is NOT 10:30 like the second line says. That system is broken.
Again, the first entry is the ENTIRE shift time, NOT the time until the first break.
Again... it's not. It's crap data.
--Jeff Moden
December 7, 2021 at 9:43 pm
ScottPletcher wrote:Jeff Moden wrote:ScottPletcher wrote:Jeff Moden wrote:This is so wrong. Your company is trying to have you fix the wrong problem. The real problem is the source of the data and THAT's what actually needs to be fixed. For example, if you look at the first two rows, 10:00 + 720 minutes (which is exactly 6 hours) cannot lead to a break start time of 10:30 on the next line.
Huh? I think it's clear that the first row is the entire shift duration, and the next row is simply the first break that occurred in the shift.
Huh, hell. 😀 10:00 +720 minutes is NOT 10:30 like the second line says. That system is broken.
Again, the first entry is the ENTIRE shift time, NOT the time until the first break.
Again... it's not. It's crap data.
When a shift starts, you can know the total time of the shift but you can't be sure of when the first break will actually be taken. I can't see how an app would be able to record data that isn't known yet.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 7, 2021 at 10:18 pm
Jeff Moden wrote:ScottPletcher wrote:Jeff Moden wrote:ScottPletcher wrote:Jeff Moden wrote:This is so wrong. Your company is trying to have you fix the wrong problem. The real problem is the source of the data and THAT's what actually needs to be fixed. For example, if you look at the first two rows, 10:00 + 720 minutes (which is exactly 6 hours) cannot lead to a break start time of 10:30 on the next line.
Huh? I think it's clear that the first row is the entire shift duration, and the next row is simply the first break that occurred in the shift.
Huh, hell. 😀 10:00 +720 minutes is NOT 10:30 like the second line says. That system is broken.
Again, the first entry is the ENTIRE shift time, NOT the time until the first break.
Again... it's not. It's crap data.
When a shift starts, you can know the total time of the shift but you can't be sure of when the first break will actually be taken. I can't see how an app would be able to record data that isn't known yet.
I get all of that, Scott. It's still crap data sitting in a system that someone is using elsewhere. Patching it is just putting an unsterile band-aid on a stab wound.
--Jeff Moden
December 17, 2021 at 6:23 pm
I think this will work. I have NOT yet adjusted it for performance (or tuned it at all yet, really), I was just trying to make it work. Let me know (1) if this gives you the results you need (2) if it performs well enough for you to be able to use.
;WITH Cte_Test_With_Row_Nums AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY UserID, Schedule_Start_Date ORDER BY Start_Time) AS row_num
FROM dbo.Test
)
SELECT ca1.*
FROM Cte_Test_With_Row_Nums CT
LEFT OUTER JOIN Cte_Test_With_Row_Nums CT_Next ON CT_Next.UserID = CT.UserID AND CT_Next.Schedule_Start_Date = CT.Schedule_Start_Date AND CT_Next.row_num = CT.row_num + 1
CROSS APPLY (
SELECT CT.Schedule_Start_Date, CT.UserID, CT.Code,
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT.Start_Time), CAST(CT.Schedule_Start_Date AS datetime)) AS Start_Time,
CASE WHEN CT.Code = 'Shift'
THEN DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT_Next.Start_Time), CAST(CT.Schedule_Start_Date AS datetime))
ELSE DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT.Start_Time) + CT.Duration_Min, CAST(CT.Schedule_Start_Date AS datetime)) END AS End_Time
UNION ALL
SELECT CT.Schedule_Start_Date, CT.UserID, 'Shift',
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT.Start_Time) + CT.Duration_Min, CAST(CT.Schedule_Start_Date AS datetime)) AS Start_Time,
CASE WHEN CT_Next.Start_Time IS NULL
THEN (SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT_Shift.Start_Time) + CT_Shift.Duration_Min, CAST(CT_Shift.Schedule_Start_Date AS datetime))
FROM Cte_Test_With_Row_Nums CT_Shift WHERE CT_Shift.UserID = CT.UserID AND CT_Shift.Schedule_Start_Date = CT.Schedule_Start_Date AND CT_Shift.row_num = 1)
ELSE DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT_Next.Start_Time), CAST(CT.Schedule_Start_Date AS datetime)) END AS End_Time
WHERE CT.Code <> 'Shift' AND DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT.Start_Time) + CT.Duration_Min, CAST(CT.Schedule_Start_Date AS datetime)) <
(SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT_Shift.Start_Time) + CT_Shift.Duration_Min,
CAST(CT_Shift.Schedule_Start_Date AS datetime)) FROM Cte_Test_With_Row_Nums CT_Shift WHERE CT_Shift.UserID = CT.UserID AND
CT_Shift.Schedule_Start_Date = CT.Schedule_Start_Date AND CT_Shift.row_num = 1)
) AS ca1
ORDER BY CT.UserID, CT.Schedule_Start_Date, CT.Start_Time
Hi Scott,
I am sorry to bug you again but after reviewing the data, the Code for shift name can change. I tried to modify your code to suit the data new data but I was not successful in doing it. I hope if you can lend me hand on this please?
CREATE TABLE [dbo].[Test](
[Schedule_Start_Date] [varchar](8) NULL,
[UserID] [int] NULL,
[Code] [varchar](10) NULL,
[Type] [varchar](1) NULL,
[Start_Time] [time](7) NULL,
[Duration_Min] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Test] ([Schedule_Start_Date], [UserID], [Code], [Type], [Start_Time], [Duration_Min]) VALUES (N'20211030', 1562, N'ShiftC', 'S', CAST(N'10:00:00' AS Time), 720)
GO
INSERT [dbo].[Test] ([Schedule_Start_Date], [UserID], [Code], [Type], [Start_Time], [Duration_Min]) VALUES (N'20211030', 1562, N'Break', 'E', CAST(N'16:30:00' AS Time), 30)
GO
INSERT [dbo].[Test] ([Schedule_Start_Date], [UserID], [Code], [Type], [Start_Time], [Duration_Min]) VALUES (N'20211030', 1562, N'Lunch', 'E', CAST(N'21:00:00' AS Time), 60)
GO
INSERT [dbo].[Test] ([Schedule_Start_Date], [UserID], [Code], [Type], [Start_Time], [Duration_Min]) VALUES (N'20211030', 1172, N'ShiftB', 'S', CAST(N'06:00:00' AS Time), 720)
GO
INSERT [dbo].[Test] ([Schedule_Start_Date], [UserID], [Code], [Type], [Start_Time], [Duration_Min]) VALUES (N'20211030', 1172, N'Break', 'E', CAST(N'12:00:00' AS Time), 30)
GO
INSERT [dbo].[Test] ([Schedule_Start_Date], [UserID], [Code], [Type], [Start_Time], [Duration_Min]) VALUES (N'20211030', 1172, N'Lunch', 'E', CAST(N'15:00:00' AS Time), 60)
GO
INSERT [dbo].[Test] ([Schedule_Start_Date], [UserID], [Code], [Type], [Start_Time], [Duration_Min]) VALUES (N'20211101', 1066, N'ShiftA', 'S', CAST(N'06:00:00' AS Time), 720)
GO
INSERT [dbo].[Test] ([Schedule_Start_Date], [UserID], [Code], [Type], [Start_Time], [Duration_Min]) VALUES (N'20211101', 1066, N'Break', 'E', CAST(N'12:00:00' AS Time), 30)
GO
INSERT [dbo].[Test] ([Schedule_Start_Date], [UserID], [Code], [Type], [Start_Time], [Duration_Min]) VALUES (N'20211101', 1066, N'Lunch', 'E', CAST(N'15:00:00' AS Time), 60)
GO
Schedule_Start_Date UserID Code Type Start_Time Duration_Min
20211030 1562 ShiftC S 10:00:00.0000000 720
20211030 1562 Break E 16:30:00.0000000 30
20211030 1562 Lunch E 21:00:00.0000000 60
20211030 1172 ShiftB S 06:00:00.0000000 720
20211030 1172 Break E 12:00:00.0000000 30
20211030 1172 Lunch E 15:00:00.0000000 60
20211101 1066 ShiftA S 06:00:00.0000000 720
20211101 1066 Break E 12:00:00.0000000 30
20211101 1066 Lunch E 15:00:00.0000000 60
And the result I need is as follow: (Similar to the previous result but the Shift code name can varies)
Schedule_Start_Date UserID Code Start_Time End_Time
20211030 1562 ShiftC 10:00 16:30
20211030 1562 Break 16:30 17:00
20211030 1562 ShiftC 17:00 21:00
20211030 1562 Lunch 21:00 22:00
20211030 1172 ShiftB 6:00 12:00
20211030 1172 Break 12:00 12:30
20211030 1172 ShiftB 12:30 15:00
20211030 1172 Lunch 15:00 16:00
20211030 1172 ShiftB 16:00 18:00
20211101 1066 ShiftA 6:00 12:00
20211101 1066 Break 12:00 12:30
20211101 1066 ShiftA 12:30 15:00
20211101 1066 Lunch 15:00 16:00
20211101 1066 ShiftA 16:00 18:00
Probably this?!:
;WITH Cte_Test_With_Row_Nums AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY UserID, Schedule_Start_Date ORDER BY Start_Time) AS row_num
FROM dbo.Test
)
SELECT ca1.*
FROM Cte_Test_With_Row_Nums CT
LEFT OUTER JOIN Cte_Test_With_Row_Nums CT_Next ON CT_Next.UserID = CT.UserID AND CT_Next.Schedule_Start_Date = CT.Schedule_Start_Date AND CT_Next.row_num = CT.row_num + 1
CROSS APPLY (
SELECT CT.Schedule_Start_Date, CT.UserID, CT.Code,
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT.Start_Time), CAST(CT.Schedule_Start_Date AS datetime)) AS Start_Time,
CASE WHEN CT.Code LIKE 'Shift%'
THEN DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT_Next.Start_Time), CAST(CT.Schedule_Start_Date AS datetime))
ELSE DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT.Start_Time) + CT.Duration_Min, CAST(CT.Schedule_Start_Date AS datetime)) END AS End_Time
UNION ALL
SELECT CT.Schedule_Start_Date, CT.UserID,
--'Shift',
(SELECT CT_Shift.Code FROM Cte_Test_With_Row_Nums CT_Shift WHERE CT_Shift.UserID = CT.UserID AND
CT_Shift.Schedule_Start_Date = CT.Schedule_Start_Date AND CT_Shift.row_num = 1) AS Shift,
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT.Start_Time) + CT.Duration_Min, CAST(CT.Schedule_Start_Date AS datetime)) AS Start_Time,
CASE WHEN CT_Next.Start_Time IS NULL
THEN (SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT_Shift.Start_Time) + CT_Shift.Duration_Min, CAST(CT_Shift.Schedule_Start_Date AS datetime))
FROM Cte_Test_With_Row_Nums CT_Shift WHERE CT_Shift.UserID = CT.UserID AND CT_Shift.Schedule_Start_Date = CT.Schedule_Start_Date AND CT_Shift.row_num = 1)
ELSE DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT_Next.Start_Time), CAST(CT.Schedule_Start_Date AS datetime)) END AS End_Time
WHERE CT.Code NOT LIKE 'Shift%' AND DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT.Start_Time) + CT.Duration_Min, CAST(CT.Schedule_Start_Date AS datetime)) <
(SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CT_Shift.Start_Time) + CT_Shift.Duration_Min,
CAST(CT_Shift.Schedule_Start_Date AS datetime)) FROM Cte_Test_With_Row_Nums CT_Shift WHERE CT_Shift.UserID = CT.UserID AND
CT_Shift.Schedule_Start_Date = CT.Schedule_Start_Date AND CT_Shift.row_num = 1)
) AS ca1
ORDER BY CT.UserID, CT.Schedule_Start_Date, CT.Start_Time
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 17, 2021 at 6:55 pm
You are amazing Scott! Thank you so much!
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply