Guest User

Untitled

a guest
Jan 18th, 2019
86
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.36 KB | None | 0 0
  1. SELECT runtime ---internalDowntime-OtherDowntime AS productiontime based on overlapping
  2. FROM
  3. (
  4. SELECT deck.ID_DECKUNGSBEITRAG,
  5. DATEDIFF(MINUTE, l.START_DATUM, l.END_DATUM) / 60.0 runtime, --which includes downtime,
  6. (
  7. SELECT SUM(DATEDIFF(MINUTE, le.START_DATUM, le.END_DATUM) / 60.0)
  8. FROM dbo.STILLSTANDSZEIT s
  9. INNER JOIN dbo.LEITSTAND le ON le.ID_STILLSTANDSZEIT = s.ID_STILLSTANDSZEIT
  10. WHERE s.ID_HERSTELLVORSCHRIFT = hs.ID_HERSTELLVORSCHRIFT
  11. ) internalDowntime,
  12. (
  13. SELECT SUM(DATEDIFF(MINUTE, ZEIT_VON, ZEIT_BIS) / 60.0)
  14. FROM dbo.PRODUKTIONSANLAGE_AUSFALLZEIT
  15. WHERE ZEIT_VON >= l.START_DATUM
  16. AND ZEIT_VON <= l.END_DATUM
  17. AND ID_PRODUKTIONSANLAGE = p.ID_PRODUKTIONSANLAGE
  18. ) OtherDowntime
  19. FROM dbo.DECKUNGSBEITRAG deck
  20. INNER JOIN dbo.HERSTELLVORSCHRIFT hs ON deck.CHARGE = hs.CHARGE_NUMMER
  21. INNER JOIN dbo.LEITSTAND l ON l.ID_HERSTELLVORSCHRIFT = hs.ID_HERSTELLVORSCHRIFT
  22. INNER JOIN dbo.PRODUKTIONSANLAGE p ON p.ID_PRODUKTIONSANLAGE = l.ID_PRODUKTIONSANLAGE
  23. WHERE deck.CHARGE = 'CHG0116112945'
  24. AND deck.ID_MANDANT = 1
  25. ) temp;
  26.  
  27. SELECT DATEDIFF(minute,'2016-12-09 01:00:49.203','2016-12-12 22:30:04.787')/60.0 = 93.500000
  28.  
  29. 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