Hi everyone,
I am having issues collecting YTD and MTD values.. I am trying to fetch YTD values for each piece of equipment
My date parameters are already being utilized and it seems to be skewing the YTD/MTD values
Any Suggestions or questions?
Thanks,
Digi
SELECT
DATEADD(dd, DATEDIFF(dd, 0, OEE.STR_STARTTIME_UTC), 0) 'START_TIME'
,DATEADD(dd, DATEDIFF(dd, 0, OEE.STR_ENDTIME_UTC), 0) 'END_TIME'
,CAST(OEE.DURATION / (60 * 60) AS Varchar) + ':' + RIGHT('0' + CAST(OEE.DURATION % (60 * 60) / 60 AS Varchar), 2)
+ ':' + RIGHT('0' + CAST(OEE.DURATION % (60) AS Varchar), 2) AS TOTALTIME
,OEE.[LNG_EQUIPMENT_ID] 'LNG_EQUIPMENT_ID'
,OEE.[STR_EQUIPMENT_ID] 'STR_EQUIPMENT_ID'
,OEE.[STR_EQUIPMENT_LABEL] 'EQUIPMENT_LABEL'
,OEE.[STR_LINE_LABEL] 'STR_LINE_LABEL'
,SUM(CASE WHEN OEE.STR_STARTTIME_UTC >= DATEADD(MM, DATEDIFF(MM, 0, GETDATE()),0)
AND OEE.STR_ENDTIME_UTC <= GETDATE() THEN CAST(OEE.DURATION / (60 * 60) AS Varchar) ELSE 0 END) AS MTD
,SUM(CASE WHEN OEE.STR_STARTTIME_UTC >= DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0)
AND OEE.STR_ENDTIME_UTC <= GETDATE() THEN CAST(OEE.DURATION / (60 * 60) AS Varchar) ELSE 0 END) AS YTD
FROM [exampleDB].[dbo].[example-view] OEE
WHERE
OEE.STR_REASON1 = 'Running'
AND OEE.STR_LINE_LABEL = @Line
AND DATEADD(dd, DATEDIFF(dd, 0, OEE.STR_STARTTIME_UTC), 0) >= @begDate
AND DATEADD(dd, DATEDIFF(dd, 0, OEE.STR_ENDTIME_UTC), 0) <= @endDate
GROUP BY
OEE.STR_STARTTIME_UTC
,OEE.STR_ENDTIME_UTC
,OEE.DURATION
,OEE.LNG_EQUIPMENT_ID
,OEE.STR_EQUIPMENT_ID
,OEE.STR_EQUIPMENT_LABEL
,OEE.STR_LINE_LABEL
,OEE.STR_REASON1