How can I select the last known value of a record, when there are no current records for the same ID

Thanks All..

The right result set returns, but one thing thats not coming out right is the date. Let me explain...I expect

DATE                  QTR                YR           ID               CLOSING AMT        
01/07/2014        3                    2014      ID_01          20,000,000.00            

to be
DATE                  QTR                YR           ID               OPENING AMT
01/01/2015        1                    2015      ID_01           20,000,000.00

because the last known CLOSING AMT for record ID 01 was in july

When a user generates report a between the date range of 1st JAN - 21st JAN 15, naturally ID 02 will appear, because it had an CLOSING balance for the 4th qtr of 2014, whereas ID 01 will not because its last known qtr closing balance was for the 3rd qtr, which is outside DATEADD(QUARTER, DATEDIFF(QUARTER, 0, 1st Dec 14), 0)

So what I am looking for is the last known recorded qtr closing value (in this scenario 6mths ago) to appear as the current qtr open balance within the date range of 1st-21st jan 15.

HTH

To add, is there a way I can make use of the SQL Function - LAG()?

LAG is available since SQL Server 2012. If you have a previous version of SQL Server then you can't.

Im using SQL Server 2014..

When a user generates report a between the date range of 1st JAN - 21st JAN 15, naturally ID 02 will appear, because it had an CLOSING balance for the 4th qtr of 2014, whereas ID 01 will not because its last known qtr closing balance was for the 3rd qtr, which is outside DATEADD(QUARTER, DATEDIFF(QUARTER, 0, 1st Dec 14), 0)

Can you explain this?

User generate monthly reports with ID's having their respective qtr, mtd and ytd balances.

A few ID's dont have any transactions for a long period of time with their closing balances rolling/carried over as the opening balance for the next mth.

I am able to accurately get the opening balance for an ID with a recent transaction up to the start of the latest qtr used for reporting, but where an ID has not done any recent transaction, its last known closing balance that has been carried over several months doesn't appear.

Technically, with this syntax, I want to see the very latest qtr opening balance which should be the last known closing balance:


;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

Wouldn't be better to have a status field where you can set it to OPEN or CLOSED depending on the situation?

Well, please step back, and explain your data.

It seems to be an aggregated snapshot. Thus there is no "transaction" nor an opening or closing balance which I could identify.

Hi

@Vitor, in the long run I will have to eventually develop a balance fact table that will store all levels of balances from the day level right up to the YTD level. However, status field would be a nice idea. Right now I making use of derived tables to get something out.

@ste5an, a look at the following link will give you an idea where there is sample date.

http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28592475.html