December 26, 2017 at 8:14 am
Hi All and Merry Christmas!
I have what I thought was a simple requirement.
I have to modify a table that has three columns (for simplicity sake I'll use the following made up columns)
Name, shift, sundayshift_date. This example is based on shift work that employees do each sunday and they can have morning, afternoon or late shift. I'd want to find the min and max date for the contigous blocks of shift work they do in a row. Here's an example.
This displays Graham working three Lates in a row and then a morning the following week. The second table (which is what I want to achieve) displays the 3 weeks contiguous Late shift as start and end dates.
Name shift_type sunday_shift_date
Graham Late 22/10/2017
Graham Late 29/10/2017
Graham Late 05/11/2017
Graham Morning 12/11/2017
Graham Afternoon 19/11/2017
Graham Afternoon 26/11/2017
Graham Afternoon 03/12/2017
Tracy Late 17/12/2017
name Shift From To
Graham Late 22/10/2017 05/11/2017
Graham Late 12/11/2017 12/11/2017
Graham Late 19/11/2017 03/12/2017
Tracy ...... (and so on)
Obviously this code wont work a it just group the dates together ignoring the contiguous date blocks
SELECT
name ,
Shift,
MIN(sunday_shift_date) fromdate,
MAX(sunday_shift_date) todate
FROM
tblShift
GROUP BY name, Shift
Any help with this would be appreciated, perhaps you have old code lying around that does this??
Thanks
December 26, 2017 at 8:25 am
Thom A - Tuesday, December 26, 2017 8:20 AMWhy is the 2nd and 3rd entries for Graham Late? I would have expected these to be Morning and Afternoon respectively.
Hi, it means that Graham has worked the Late shift consecutively three weeks in a row. On the following week he has worked on the morning shift and so on.
The first three weeks should be grouped as they are consecutive late shifts. The morning shift should be a row on it's own.
Hope that makes sense.
December 26, 2017 at 8:29 am
No, this doesn't make sense. The 2nd entry in your expected result set states Late 12/11/2012, however, in your first dataset it's morning on 12/11/2017. Can you provide a full expected result set, as "and so on" doesn't give us enough to represent values other than Late.
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 26, 2017 at 8:39 am
Thom A - Tuesday, December 26, 2017 8:29 AMNo, this doesn't make sense. The 2nd entry in your expected result set states Late 12/11/2012, however, in your first dataset it's morning on 12/11/2017. Can you provide a full expected result set, as "and so on" doesn't give us enough to represent values other than Late.Thanks.
OOps my bad -
name Shift From To
Graham Late 22/10/2017 05/11/2017
Graham Morning 12/11/2017 12/11/2017
Graham Afternoon 19/11/2017 03/12/2017
Tracy ...... (and so on)
Sorry about that
December 26, 2017 at 8:44 am
To keep it a bit more simple, let's just stick with Graham
name shift_type sunday_shift_date
Graham Late 22/10/2017
Graham Late 29/10/2017
Graham Late 05/11/2017
Graham Morning 12/11/2017
Graham Afternoon 19/11/2017
Graham Afternoon 26/11/2017
Graham Afternoon 03/12/2017
Graham Late 10/12/2017
Graham Late 17/12/2017
--Expected
name Shift From To
Graham Late 22/10/2017 05/11/2017
Graham Morning 12/11/2017 12/11/2017
Graham Afternoon 19/11/2017 03/12/2017
Graham Late 10/12/2017 17/12/2017
December 26, 2017 at 9:49 am
This is a fairly straitforward gaps and islands problem. Here is a solution. There may also be a solution with LAG, but I don't have access to a SQL 2012 environment right now to test it.
I've set up your table using a TABLE VALUE CONSTRUCTOR. You'll want to replace that with your actual table.
WITH shift_groups AS
(
SELECT *,
grp = ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY dt, shift_type)
- ROW_NUMBER() OVER(PARTITION BY [name], shift_type ORDER BY dt)
FROM
(
VALUES
('Graham', 'Late', '22/10/2017')
, ('Graham', 'Late', '29/10/2017')
, ('Graham', 'Late', '05/11/2017')
, ('Graham', 'Morning', '12/11/2017')
, ('Graham', 'Afternoon', '19/11/2017')
, ('Graham', 'Afternoon', '26/11/2017')
, ('Graham', 'Afternoon', '03/12/2017')
, ('Graham', 'Late', '10/12/2017')
, ('Graham', 'Late', '17/12/2017')
) m([name], shift_type, sunday_shift_date)
CROSS APPLY (VALUES(CONVERT(DATE, sunday_shift_date, 103))) dt(dt)
)
SELECT [name], shift_type, MIN(dt), MAX(dt)
FROM shift_groups
GROUP BY [name], shift_type, grp
ORDER BY [name], MIN(dt)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 26, 2017 at 10:26 am
drew.allen - Tuesday, December 26, 2017 9:49 AMThis is a fairly straitforward gaps and islands problem. Here is a solution. There may also be a solution with LAG, but I don't have access to a SQL 2012 environment right now to test it.I've set up your table using a TABLE VALUE CONSTRUCTOR. You'll want to replace that with your actual table.
WITH shift_groups AS
(
SELECT *,
grp = ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY dt, shift_type)
- ROW_NUMBER() OVER(PARTITION BY [name], shift_type ORDER BY dt)
FROM
(
VALUES
('Graham', 'Late', '22/10/2017')
, ('Graham', 'Late', '29/10/2017')
, ('Graham', 'Late', '05/11/2017')
, ('Graham', 'Morning', '12/11/2017')
, ('Graham', 'Afternoon', '19/11/2017')
, ('Graham', 'Afternoon', '26/11/2017')
, ('Graham', 'Afternoon', '03/12/2017')
, ('Graham', 'Late', '10/12/2017')
, ('Graham', 'Late', '17/12/2017')
) m([name], shift_type, sunday_shift_date)
CROSS APPLY (VALUES(CONVERT(DATE, sunday_shift_date, 103))) dt(dt)
)
SELECT [name], shift_type, MIN(dt), MAX(dt)
FROM shift_groups
GROUP BY [name], shift_type, grp
ORDER BY [name], MIN(dt)Drew
This is brilliant Drew! spot on solution and thanks so much!!
December 26, 2017 at 12:12 pm
So, is this what you are looking for?
IF OBJECT_ID('dbo.TestTable','U') IS NOT NULL
DROP TABLE [dbo].[TestTable];
CREATE TABLE [dbo].[TestTable](
[name] VARCHAR(30)
, [ShiftType] VARCHAR(30)
, [SundayShiftDate] DATE);
INSERT INTO [dbo].[TestTable]([name],[ShiftType],[SundayShiftDate])
VALUES ('Graham','Late','2017-10-22')
,('Graham','Late','2017-10-29')
,('Graham','Late','2017-11-05')
,('Graham','Morning','2017-11-12')
,('Graham','Afternoon','2017-11-19')
,('Graham','Afternoon','2017-11-26')
,('Graham','Afternoon','2017-12-03')
,('Graham','Late','2017-12-10')
,('Graham','Late','2017-12-17');
SELECT [tt].[name], [tt].[ShiftType], [tt].[SundayShiftDate] FROM [dbo].[TestTable] AS [tt];
WITH base AS (
SELECT
[tt].[name]
, [tt].[ShiftType]
, [tt].[SundayShiftDate]
, [rn] = ROW_NUMBER() OVER (PARTITION BY [tt].[name], [tt].[ShiftType] ORDER BY [tt].[SundayShiftDate])
FROM
[dbo].[TestTable] AS [tt]
), Interim AS (
SELECT
.[name]
, .[ShiftType]
, .[SundayShiftDate]
, [GroupDate] = DATEADD(DAY,-(7 * [rn]), .[SundayShiftDate])
FROM
[base] AS
)
SELECT
.[name]
, .[ShiftType]
, [From] = MIN(.[SundayShiftDate])
, [To] = MAX(.[SundayShiftDate])
FROM
[Interim] AS
GROUP BY
.[name]
, .[ShiftType]
, .[GroupDate]
ORDER BY
.[name]
, MIN(.[GroupDate]);
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply