Shift time interval combining from parent to child

  • 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')

  • 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

  • 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.

  • 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

  • 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.

  • 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