Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
- SELECT DATEDIFF(minute,'2016-12-09 01:00:49.203','2016-12-12 22:30:04.787')/60.0 = 93.500000
- SELECT DATEDIFF(minute,'2016-12-10 06:00:00.000','2016-12-12 06:00:00.000')/60.0 =48.000000
Add Comment
Please, Sign In to add comment