Split time span into multiple records

  • Having a problem finding a solution for the following issue:

    I have a table that includes the fields below:

    ID - INT

    Machine - TINYINT

    StartTime - DATETIME

    EndTime - DATETIME

    What I am trying to do is figure out how much time is used for production per day. The problem is, there are production runs that run over midnight and possible multiple days without ending. For example, if I have the following data:

    ID - 1

    Machine - 2

    StartTime - 2015-09-01 22:00:00.000

    EndTime - 2015-09-03 22:00:00.000

    So what I am looking for is taking the above record and turning it into 3 records like below:

    ID Machine StartTime EndTime

    1 2 2015-09-01 22:00:00.000 2015-09-01 23:59:59.999

    1 2 2015-09-02 00:00:00.000 2015-09-02 23:59:59.999

    1 2 2015-09-03 00:00:00.000 2015-09-03 22:00:00.000

    Does anyone have a way to do this?

    Thank you in advance,

    Bob

  • It's possible by using a calendar table, but it doesn't make sense to duplicate rows when you're not adding anything special. Why do you want to have 3 ranges instead of one? What will you do with those additional rows?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Here is what I have so far:

    SELECT

    prod.ID,

    prod.Machine,

    prod.StartTime,

    DATEADD(dd, DATEDIFF(dd, 0, prod.EndTime), 0)

    FROM

    prod

    WHERE

    DATEDIFF(dd, prod.StartTime, prod.EndTime) = 1

    UNION ALL

    SELECT

    prod.ID,

    CASE WHEN DATEDIFF(dd, prod.StartTime, prod.EndTime) = 1

    THEN DATEADD(dd, DATEDIFF(dd, 0, prod.EndTime), 0)

    ELSE prod.StartTime

    END,

    prod.EndTime

    FROM

    prod

    ORDER BY

    prod.ID

    This works, unless the number of days between the range is greater than 1.

    I need to do this in order to have a way to extract the production data from the database to give me the number of hours per day, per machine that were used in order to get an OEE report.

    Thanks again,

    Bob

  • also, I will be adding columns to these rows after I get the rows to display.....they will be calculations based on values in other tables.

  • It makes sense to generate reports that show data per date. It won't make sense to generate additional data that will be simply duplicating rows.

    Here's an example I did playing with some sample data.

    CREATE TABLE #Prod(

    ID INT,

    Machine TINYINT,

    StartTime DATETIME,

    EndTime DATETIME);

    INSERT INTO #Prod

    VALUES( 1, 2, '2015-09-01 22:00:00.000', '2015-09-03 12:00:00.000'),

    ( 2, 2, '2015-09-03 14:00:00.000', '2015-09-03 22:00:00.000'),

    ( 3, 3, '2015-09-01 22:00:00.000', '2015-09-03 22:00:00.000'),

    ( 4, 4, '2015-10-01 22:00:00.000', '2015-09-03 22:00:00.000'),

    ( 4, 4, '2015-10-01 22:00:00.000', '2015-10-02 22:00:00.000'),

    ( 5, 5, '2015-09-01 12:00:00.000', '2015-09-01 22:00:00.000');

    DECLARE @StartDate date = '20150901',

    @EndDate date = '20150902';

    WITH

    E(n) AS(

    SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

    ),

    E2(n) AS(

    SELECT a.n FROM E a, E b

    ),

    E4(n) AS(

    SELECT a.n FROM E2 a, E2 b

    ),

    cteTally(n) AS(

    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 n

    FROM E4

    ),

    cteResult AS(

    SELECT Machine,

    DATEADD( dd, DATEDIFF( dd, 0, p.StartTime) + n, 0) AS CalDay,

    SUM(CASE --When the range is in the same day

    WHEN DATEADD( dd, DATEDIFF( dd, 0, p.StartTime), 0) = DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), 0)

    THEN DATEDIFF( MI, p.StartTime, p.EndTime) / 60.

    -- When first day of multiple days

    WHEN DATEADD( dd, DATEDIFF( dd, 0, p.StartTime), 0) < DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), 0) AND n = 0

    THEN ((60*24) - (DATEPART(HH, p.StartTime) * 60) - DATEPART(MI, p.StartTime)) / 60.

    -- When multiple days but not first nor last

    WHEN DATEADD( dd, DATEDIFF( dd, 0, p.StartTime), 0) < DATEADD( dd, DATEDIFF( dd, 0, p.StartTime) + n, 0)

    AND DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), 0) > DATEADD( dd, DATEDIFF( dd, 0, p.StartTime) + n, 0)

    THEN 24

    -- When last day of multiple days

    WHEN DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), 0) = DATEADD( dd, DATEDIFF( dd, 0, p.StartTime) + n, 0)

    THEN ((DATEPART(HH, p.EndTime) * 60) + DATEPART(MI, p.EndTime)) / 60.

    END) AS HoursPerDay

    FROM #Prod p

    JOIN cteTally t ON p.StartTime < DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), -t.n + 1)

    --WHERE p.StartTime < DATEADD( dd, 1, @EndDate)

    --AND p.EndTime > @StartDate

    GROUP BY Machine,

    DATEADD( dd, DATEDIFF( dd, 0, p.StartTime) + n, 0)

    )

    SELECT *

    FROM cteResult

    --WHERE CalDay BETWEEN @StartDate AND @EndDate

    ORDER BY Machine, CalDay

    GO

    DROP TABLE #Prod

    Review the code and ask questions that you might have. Also, read the following article which explains the tally table that is used here: http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/

    EDIT: Corrected the code after CKinley's remark.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thank you, Luis. I have had a chance to look it over. I am comfortable with most of it, but still looking over a few pieces of the code to make sure I understand it. I will comment back when I finish it this weekend.

    Have a good weekend,

    Bob

  • Luis, that seems like a great way to accomplish what nailers is trying to do. I would like to take some of your ideas for what I am trying to accomplish. Thank you for sharing.

    I have two concerns, though.

    1. I think the result row (3, 2, 2015-09-03 00:00:00.000, 8) should be (3, 2, 2015-09-03 00:00:00.000, 20) because on 2015-09-03, there were 20 hours of machine time. 12 hours from row 1 of #Prod and 8 hours from row 2 of #Prod. It looks like the algorithm didn't grab the 12 hours from row 1.

    2. For row 4 of #Prod, you have 2015-10-01 22:00:00.000 as StartTime and 2015-09-03 22:00:00.000 as EndTime. This means you are going backward in time. I think either you meant a different StartTime, or you are demonstrating the algorithm's ability to go back in time. Not sure which one, though.

  • You're right, there's an error on the calculation that must be fixed for issue #1.

    To fix it, the JOIN condition should look like this:

    JOIN cteTally t ON p.StartTime <= DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), -t.n + 1)

    The second issue that you mention is just an example on how this would ignore rows where the EndTime is older than the StartTime. I'm not sure if it would validate it correctly if the dates are the same. Of course, this data should be prevented with a CHECK constraint.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Ok, thank you for the update, Luis.

  • You're welcome.

    Now that I think about it, this might work better as an inline table valued function. I don't have time to test this right now, but it could be worth trying.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Just to share a warning... run the following and see what's wrong in the original query...

    SELECT CAST('2015-09-01 23:59:59.999' AS DATETIME);

    You shouldn't try to "hit the mark" on the end date. Always use the next date and use "<".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, thanks for that warning. I will look into that.

    Luis, I noticed that the HoursPerDay is a little off for the case "When last day of multiple days". It's not off for the example data we have used thus far, but let me insert a new row to show what's going on:

    INSERT INTO #Prod(ID, Machine, StartTime, EndTime)

    VALUES(6, 6, '2015-11-06 00:00:00', '2015-11-07 06:17:58');

    Here is the result set when I run the query now:

    MachineCalDay HoursPerDay

    22015-09-01 00:00:00.000 2.000000

    22015-09-02 00:00:00.000 24.000000

    22015-09-03 00:00:00.000 20.000000

    32015-09-01 00:00:00.000 2.000000

    32015-09-02 00:00:00.000 24.000000

    32015-09-03 00:00:00.000 22.000000

    42015-10-01 00:00:00.000 2.000000

    42015-10-02 00:00:00.000 22.000000

    52015-09-01 00:00:00.000 10.000000

    62015-11-06 00:00:00.000 24.000000

    62015-11-07 00:00:00.000 5.716666

    62015-11-08 00:00:00.000 NULL

    The HoursPerDay for 2015-11-06 is fine, but it is off for 2015-11-07 (should be 6.283) and 2015-11-08 comes out of nowhere and gives a NULL.

    After testing it, I found that the minutes datepart of EndTime is being subtracted from the hours datepart of EndTime:

    -- When last day of multiple days

    WHEN DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), 0) = DATEADD( dd, DATEDIFF( dd, 0, p.StartTime) + n, 0)

    THEN ((DATEPART(HH, p.EndTime) * 60) - DATEPART(MI, p.EndTime)) / 60.

    Maybe I don't fully understand the algorithm, but I think that the minutes datepart of EndTime should be added to the hours datepart. When I adjust change the "-" to a "+", here is the result set:

    MachineCalDay HoursPerDay

    22015-09-01 00:00:00.000 2.000000

    22015-09-02 00:00:00.000 24.000000

    22015-09-03 00:00:00.000 20.000000

    32015-09-01 00:00:00.000 2.000000

    32015-09-02 00:00:00.000 24.000000

    32015-09-03 00:00:00.000 22.000000

    42015-10-01 00:00:00.000 2.000000

    42015-10-02 00:00:00.000 22.000000

    52015-09-01 00:00:00.000 10.000000

    62015-11-06 00:00:00.000 24.000000

    62015-11-07 00:00:00.000 6.283333

    62015-11-08 00:00:00.000 NULL

    So we would add the minutes to the hours, then divide by 60.

    The problem with 2015-11-08 coming out of nowhere with a NULL is still a mystery to me, though.

  • That's why it's important to test correctly.

    You're right about adding the minutes instead of subtracting them. The NULL value might be there because you're still using the <= comparison.

    I updated the code in my original post to correct this issues.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • You are right about the <= comparison. I was using <= for the join condition, and just changed it to <.

    JOIN cteTally t ON p.StartTime < DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), -t.n + 1)

    The results look good now:

    MachineCalDay HoursPerDay

    22015-09-01 00:00:00.000 2.000000

    22015-09-02 00:00:00.000 24.000000

    22015-09-03 00:00:00.000 20.000000

    32015-09-01 00:00:00.000 2.000000

    32015-09-02 00:00:00.000 24.000000

    32015-09-03 00:00:00.000 22.000000

    42015-10-01 00:00:00.000 2.000000

    42015-10-02 00:00:00.000 22.000000

    52015-09-01 00:00:00.000 10.000000

    62015-11-06 00:00:00.000 24.000000

    62015-11-07 00:00:00.000 6.283333

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply