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
Change is inevitable... Change for the better is not.
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) A socialist is someone who will give you the shirt off *someone else's* back.
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
Change is inevitable... Change for the better is not.
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_DateUserIDCodeTypeStart_TimeDuration_Min
202110301562ShiftCS10:00:00.0000000720
202110301562BreakE16:30:00.000000030
202110301562LunchE21:00:00.000000060
202110301172ShiftBS06:00:00.0000000720
202110301172BreakE12:00:00.000000030
202110301172LunchE15:00:00.000000060
202111011066ShiftAS06:00:00.0000000720
202111011066BreakE12:00:00.000000030
202111011066LunchE15:00:00.000000060
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) A socialist is someone who will give you the shirt off *someone else's* back.
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