Find overlapping time intervals excluding overlapping time

  • In our scenrio
    Production time is always includes downtimes if there is a downtime (internal and other), idea is calculate exact production time, excluding the downtimes. Problem here is, internal downtime may overlap with other downtime

    SELECT runtime  ---internalDowntime-OtherDowntime AS productiontime  based on overlapping
    FROM
    (
        SELECT deck.ID_DECKUNGSBEITRAG,
               DATEDIFF(MINUTE, l.START_DATUM, l.END_DATUM) / 60.0 runtime, --which includes downtime,

        (
            SELECT SUM(DATEDIFF(MINUTE, le.START_DATUM, le.END_DATUM) / 60.0)
            FROM dbo.STILLSTANDSZEIT s
                 INNER JOIN dbo.LEITSTAND le ON le.ID_STILLSTANDSZEIT = s.ID_STILLSTANDSZEIT
            WHERE s.ID_HERSTELLVORSCHRIFT = hs.ID_HERSTELLVORSCHRIFT
        ) internalDowntime,
        (
            SELECT SUM(DATEDIFF(MINUTE, ZEIT_VON, ZEIT_BIS) / 60.0)
            FROM dbo.PRODUKTIONSANLAGE_AUSFALLZEIT
            WHERE ZEIT_VON >= l.START_DATUM
                  AND ZEIT_VON <= l.END_DATUM
                  AND ID_PRODUKTIONSANLAGE = p.ID_PRODUKTIONSANLAGE
        ) OtherDowntime
        FROM dbo.DECKUNGSBEITRAG deck
             INNER JOIN dbo.HERSTELLVORSCHRIFT hs ON deck.CHARGE = hs.CHARGE_NUMMER
             INNER JOIN dbo.LEITSTAND l ON l.ID_HERSTELLVORSCHRIFT = hs.ID_HERSTELLVORSCHRIFT
             INNER JOIN dbo.PRODUKTIONSANLAGE p ON p.ID_PRODUKTIONSANLAGE = l.ID_PRODUKTIONSANLAGE
        WHERE deck.CHARGE = 'CHG0116112945'
              AND deck.ID_MANDANT = 1
    ) temp;

    Date format is yyyy-mm-dd hh:mm:ss

    Production start and endtime (runtime)


      SELECT DATEDIFF(minute,'2016-12-09 01:00:49.203','2016-12-12 22:30:04.787')/60.0       =  93.500000

    Internal Downtime

  • 1strow 1 hour and second row 5 hours, total 6 hours

  • Other Downtime

  • 1st row      SELECT DATEDIFF(minute,'2016-12-10 06:00:00.000','2016-12-12 06:00:00.000')/60.0  =48.000000

    Highlighted one is inclusive with internal downtime except 1 hour, hence totally 49 hours

    Excepting final output(production time) is  93.50 – 6 -  49  = 38.5

    Appreciate if somebody could help to achieve this

  • You're much more likely to get help if you supply consumable data.  (See the first link in my signature.)

    You should also look up Packing Intervals by Itzik Ben Gan.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • If you want to union the internal and external downtime together to produce one set of downtime you could use a query like this:
    ;WITH AllDownTime AS
    (
        /* Overlapping DownTimeInternal DownTimeExternal */ 
        SELECT IIF(i.StartTime < e.StartTime, i.StartTime, e.StartTime) StartTime,
               IIF(i.EndTime < e.EndTime, e.EndTime, i.EndTime) EndTime
          FROM dbo.DownTimeInternal i
         INNER JOIN dbo.DownTimeExternal e
                 ON i.StartTime < e.EndTime AND e.StartTime < i.EndTime
    UNION ALL
        /* Non overlapping DownTimeInternal */
        SELECT i.StartTime, i.EndTime
          FROM dbo.DownTimeInternal i
          WHERE NOT EXISTS(SELECT *
                             FROM dbo.DownTimeExternal e
                            WHERE i.StartTime < e.EndTime AND e.StartTime < i.EndTime)
    UNION ALL
        /* Non overlapping DownTimeExternal */
        SELECT e.StartTime, e.EndTime
          FROM dbo.DownTimeExternal e
         WHERE NOT EXISTS(SELECT *
                            FROM dbo.DownTimeInternal i
                           WHERE e.StartTime < i.EndTime AND i.StartTime < e.EndTime)
    )
    SELECT *
    FROM AllDownTime

  • Viewing 3 posts - 1 through 2 (of 2 total)

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