Untitled

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);