July 19, 2016 at 6:37 am
This is a time interval combing question of time intervals. I have two tables a Shift and Shift Activity where the Shift indicates the assigned schedule from a StartTime to an EndTime then in the Shift Activity are the activities that occur within the shift (such as break, lunch, etc.). What is required is to combine the Shift & ShiftActivity (with gaps) into a full schedule for the day.
For example I have the following Shift data:
ShiftIDUserIDStartTime EndTime
1102016-07-19 08:00:00.000 2016-07-19 12:00:00.000
2122016-07-19 08:30:00.000 2016-07-29 12:30:00.000
And, Shift Activity data for each Shift above:
ShiftActivityIDShiftIDActivityIDStartTime EndTime
1 12 2016-07-19 09:00:00.0002016-07-19 09:15:00.000
6 12 2016-07-19 10:15:00.0002016-07-19 10:25:00.000
3 12 2016-07-19 11:00:00.0002016-07-19 11:15:00.000
4 22 2016-07-19 09:30:00.0002016-07-19 09:45:00.000
5 22 2016-07-19 11:30:00.0002016-07-19 11:45:00.000
So, the end result for example for ShiftID = 1 (for UserID = 10) then would be to have a combined listing of start/end times from both tables as follows:
ShiftID UserID StartTime EndTime
1 10 2016-07-19 08:00:00.000 2016-07-19 09:00:00.000
1 10 2016-07-19 09:00:00.000 2016-07-19 09:15:00.000
1 10 2016-07-19 09:15:00.000 2016-07-19 10:15:00.000
1 10 2016-07-19 10:15:00.000 2016-07-19 10:25:00.000
1 10 2016-07-19 10:25:00.000 2016-07-19 11:00:00.000
1 10 2016-07-19 11:00:00.000 2016-07-19 11:15:00.000
1 10 2016-07-19 11:15:00.000 2016-07-19 12:00:00.000
I have tried various T-SQL and stored procedures but the complexity to perform this keeps increasing, I was wondering can this be done with a query either with CTE or without or using other T-SQL functions. What is the best way to handle such a data without getting to a long logic to manipulate the data to the way I want?
The SQL Code is as follows:
CREATE TABLE [dbo].[Shifts](
[ShiftID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NOT NULL,
CONSTRAINT [PK_Shifts] PRIMARY KEY CLUSTERED
(
[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].[ShiftActivity](
[ShiftActivityID] [int] IDENTITY(1,1) NOT NULL,
[ShiftID] [int] NOT NULL,
[ActivityID] [int] NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NOT NULL,
CONSTRAINT [PK_ShiftActivity] PRIMARY KEY CLUSTERED
(
[ShiftActivityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ShiftActivity] WITH CHECK ADD CONSTRAINT [FK_ShiftActivity_ShiftActivity] FOREIGN KEY([ShiftID])
REFERENCES [dbo].[Shifts] ([ShiftID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ShiftActivity] CHECK CONSTRAINT [FK_ShiftActivity_ShiftActivity]
GO
The data:
set identity_insert Shifts on
insert into Shifts (ShiftID, UserID, StartTime, EndTime) values (1, 10, 'Jul 19 2016 8:00AM', 'Jul 19 2016 12:00PM')
insert into Shifts (ShiftID, UserID, StartTime, EndTime) values (2, 12, 'Jul 19 2016 8:30AM', 'Jul 29 2016 12:30PM')
set identity_insert Shifts off
insert into ShiftActivity (ShiftID, ActivityID, StartTime, EndTime) values (1, 2, 'Jul 19 2016 9:00AM', 'Jul 19 2016 9:15AM')
insert into ShiftActivity (ShiftID, ActivityID, StartTime, EndTime) values (1, 2, 'Jul 19 2016 10:15AM', 'Jul 19 2016 10:25AM')
insert into ShiftActivity (ShiftID, ActivityID, StartTime, EndTime) values (1, 2, 'Jul 19 2016 11:00AM', 'Jul 19 2016 11:15AM')
insert into ShiftActivity (ShiftID, ActivityID, StartTime, EndTime) values (2, 2, 'Jul 19 2016 9:30AM', 'Jul 19 2016 9:45AM')
insert into ShiftActivity (ShiftID, ActivityID, StartTime, EndTime) values (2, 2, 'Jul 19 2016 11:30AM', 'Jul 19 2016 11:45AM')
July 19, 2016 at 8:15 am
maybe, as a start?
WITH CTE AS(
SELECT ShiftID, UserID, StartTime AS tm
FROM Shifts
UNION ALL
SELECT s.ShiftID, s.UserID, sa.StartTime AS tm
FROM Shifts AS s INNER JOIN ShiftActivity AS sa ON s.ShiftID = sa.ShiftID
UNION ALL
SELECT ShiftID, UserID, EndTime AS tm
FROM Shifts
UNION ALL
SELECT s.ShiftID, s.UserID, sa.EndTime AS tm
FROM Shifts AS s INNER JOIN ShiftActivity AS sa ON s.ShiftID = sa.ShiftID
)
SELECT * FROM (
SELECT Shiftid, UserId, tm as Starttime, LEAD(tm,1) OVER (PARTITION BY userid ORDER BY tm) Endtime
FROM CTE) x
WHERE x.Endtime > 0
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 19, 2016 at 8:33 am
J Livingston SQL (7/19/2016)
maybe, as a start?
WITH CTE AS(
SELECT ShiftID, UserID, StartTime AS tm
FROM Shifts
UNION ALL
SELECT s.ShiftID, s.UserID, sa.StartTime AS tm
FROM Shifts AS s INNER JOIN ShiftActivity AS sa ON s.ShiftID = sa.ShiftID
UNION ALL
SELECT ShiftID, UserID, EndTime AS tm
FROM Shifts
UNION ALL
SELECT s.ShiftID, s.UserID, sa.EndTime AS tm
FROM Shifts AS s INNER JOIN ShiftActivity AS sa ON s.ShiftID = sa.ShiftID
)
SELECT * FROM (
SELECT Shiftid, UserId, tm as Starttime, LEAD(tm,1) OVER (PARTITION BY userid ORDER BY tm) Endtime
FROM CTE) x
WHERE x.Endtime > 0
Nice solution 🙂 thank you I will have to try out with our different conditions but looks very promising.
July 19, 2016 at 8:38 am
Nice solution 🙂 thank you I will have to try out with our different conditions but looks very promising
If you have "different conditions" then why did you not post them in the first place?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 19, 2016 at 10:43 pm
J Livingston SQL (7/19/2016)
Nice solution 🙂 thank you I will have to try out with our different conditions but looks very promising
If you have "different conditions" then why did you not post them in the first place?
No worries conditions are the business cases faced on scheduling itself; not practical to include this in a post as this will be too complex and numerous to discuss. I just needed initially to understand how to re-look at the data in a less complex and efficient manner unlike in a previous more complex method that was implemented. Again, I appreciate the reply and help.
July 20, 2016 at 4:15 am
Muhanned Maayeh-445298 (7/19/2016)
J Livingston SQL (7/19/2016)
Nice solution 🙂 thank you I will have to try out with our different conditions but looks very promising
If you have "different conditions" then why did you not post them in the first place?
No worries conditions are the business cases faced on scheduling itself; not practical to include this in a post as this will be too complex and numerous to discuss. I just needed initially to understand how to re-look at the data in a less complex and efficient manner unlike in a previous more complex method that was implemented. Again, I appreciate the reply and help.
faie enough...hope you get it sorted successfully
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply