January 18, 2019 at 3:20 am
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
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
January 18, 2019 at 9:01 am
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
January 18, 2019 at 9:29 am
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