Home Forums SQL Server 2014 Development - SQL Server 2014 OPENING BALANCE FOR EACH QTR START DATE FOR EACH LDGRID WITHIN A @START_DATE AND @END_DATE RE: OPENING BALANCE FOR EACH QTR START DATE FOR EACH LDGRID WITHIN A @START_DATE AND @END_DATE

  • Just in case anyone has a similar problem to this, I'm sharing the solution that was provided by ScottPlecther with the help of a CTE. Once again many thanks to Scott, your a star!!!

    ;WITH cte_qtr_bals AS (

    SELECT

    DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0) AS QTD_SD,

    LDGRID,

    SUM(ISNULL(T_AMT, 0) * CASE WHEN TDM = '0' THEN -1 ELSE 1 END) AS QTR_BAL

    FROM LDGRTRN

    WHERE LDGRID IN ('LDGR_ID1','LDGR_ID2','LDGR_ID3','LDGR_ID4')

    GROUP BY DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0), LDGRID

    )

    SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, L.ORDER_DATE), 0) AS QTD_SD,

    A.LDGRID,

    DATEPART(QUARTER, ORDER_DATE) QTR_#,

    (DATEPART(YYYY, ORDER_DATE)) AS YR,

    (SELECT SUM(QTR_BAL)

    FROM cte_qtr_bals cqb

    WHERE cqb.QTD_SD < DATEADD(QUARTER, DATEDIFF(QUARTER, 0, L.ORDER_DATE), 0)

    AND cqb.LDGRID = L.LDGRID

    ) AS QTR_OPN_BAL

    FROM LDGRACCTS A LEFT JOIN LDGRTRN L ON A.LDGRID = L.LDGRID

    WHERE A.LDGRID IN ('LDGR_ID1','LDGR_ID2','LDGR_ID3','LDGR_ID4')

    AND L.ORDER_DATE >= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @START_DATE), 0)

    AND L.ORDER_DATE < DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @END_DATE) + 1, 0)

    GROUP BY DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0),

    A.LDGRID,

    (DATEPART(YYYY, ORDER_DATE)),

    DATEPART(QUARTER, ORDER_DATE)

    ORDER BY QTD_SD,

    LDGRID