February 26, 2009 at 3:14 am
Good Day
I have a system that collects status data etc. (Like shift change, lunch, break down, engine running etc)
and these records have a start time_stamp and a end time stamp
Shift change is from 07:00 AM to 07:00PM
If example a status (record)start time stamp is 05:30 and end time stamp is 08:00, i would like to split this record (make duplicate) but need to divide the time so that the first record Start time stamp is 05:30 and end time stamp is 07:00 then the second record starts at 07:00 and ends at 08:00
do you have some scripting for me to support my question??
thank you very much
Stefan
February 26, 2009 at 5:33 am
You should derive the shifts from a Calendar table and then join to your data.
(Look up Calendar table on this site)
For simplicity I have just used a shifts table here:
DECLARE @shifts TABLE
(
    StartTime datetime NOT NULL
    ,EndTime datetime NOT NULL
)
INSERT INTO @shifts
SELECT '20090224 19:00', '20090225 07:00' UNION ALL
SELECT '20090225 07:00', '20090225 19:00' UNION ALL
SELECT '20090225 19:00', '20090226 07:00'
DECLARE @t TABLE
(
    TId int NOT NULL
    ,StartTime datetime NOT NULL
    ,EndTime datetime NOT NULL
)
INSERT INTO @t
SELECT 1, '20090225 05:30', '20090225 08:00' UNION ALL
SELECT 2, '20090225 07:30', '20090225 15:00' UNION ALL
SELECT 3, '20090225 08:15', '20090225 21:00' UNION ALL
SELECT 4, '20090225 04:00', '20090225 20:00' UNION ALL
SELECT 5, '20090225 15:00', '20090226 03:00' UNION ALL
SELECT 6, '20090225 07:00', '20090225 19:00'
select * from @t
SELECT
    T.Tid
    ,CASE
        WHEN T.StartTime > S.StartTime
        THEN T.StartTime
        ELSE S.StartTime
    END AS StartTime
    ,CASE
        WHEN T.EndTime < S.EndTime
        THEN T.EndTime
        ELSE S.EndTime
    END AS EndTime
FROM @t T
    JOIN @shifts S
        ON T.StartTime < S.EndTime
            AND T.EndTime > S.StartTime
ORDER BY T.TId, StartTime
February 26, 2009 at 5:46 am
thanks Ken
I'll let you know if my brain burns again.
thanks a lot
Stef
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply