SQLTeam.com | Weblogs | Forums

YTD /MTD values based off date parameters


#1

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