Untitled
unknown
sql
a year ago
1.5 kB
4
Indexable
CREATE VIEW dbo.VIEW_CERREJON_PLANTMOVEMENTS_Adjusted AS SELECT CAST(MeasurementStartDateTime AS DATE) AS [Date], SUM(WetTonnes) AS TotalWetTonnes, COALESCE(SUM(AdjustedTotals.TONNES), 0) AS TotalAdjustedTonnes FROM VIEW_CERREJON_PLANTMOVEMENTS LEFT JOIN ( SELECT FECHA, SUM(TONNES) AS TONNES FROM ( SELECT A.FECHA_CERREJON AS FECHA, A.MTDESTINATION, CASE WHEN A.MTDESTINATION = 'RECHAZO' THEN B.TONS_IN - B.TONS_OUT WHEN A.MTDESTINATION = 'PLANLAVA' THEN B.TONS_IN ELSE B.TONS_OUT END AS TONNES FROM DATAWAREHOUSE.DL_MDC.dbo.TBL_MUESTRAS_CCLAS A JOIN DATAWAREHOUSE.DL_MDC.dbo.PR_MDC_PLANTAS_PRODC B ON A.FECHA_CERREJON = B.STAT_DATE WHERE A.TIPO = 'PLANTA' AND B.EQUIP_NO = 2040000 -- Removed condition involving BASE_REPORTE AND A.MTDESTINATION = 'PLANLAVA' AND A.FMUESTRA BETWEEN CONVERT(DATETIME, CONCAT(EOMONTH(DATEADD(MM, -2, GETDATE())), ' 18:00:00'), 120) AND CONVERT(DATETIME, CONCAT(EOMONTH(DATEADD(MM, -1, GETDATE())), ' 18:00:00'), 120) ) C GROUP BY FECHA ) AdjustedTotals ON CAST(MeasurementStartDateTime AS DATE) = AdjustedTotals.FECHA GROUP BY CAST(MeasurementStartDateTime AS DATE);
Editor is loading...
Leave a Comment