Solved

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

Posted on 2015-01-21
12
103 Views
Last Modified: 2015-01-22
How can I select the last known value of a record to be used, when there are no current records for the same ID?

E.g ID 01, has only 3 records for a given period. ID 02 has 4 records for more given periods than ID 01.

I want to develop a script in SQL 2014 to select the last known value for ID 01, as shown in bold below.

DATE          QTR_NUMBER      YR       ID              AMT
01/10/2013        4                    2013      ID_01       4,000,000.00
01/07/2014        3                    2014      ID_01       20,000,000.00
01/01/2014        1                    2014      ID_02      -375,000.00
01/04/2014        2                    2014      ID_02      -374,999.97
01/07/2014        3                   2014      ID_02      -375,000.00
01/10/2014        4                   2014      ID_02      -449,999.60
01/01/2015        1                   2015      ID_02      -449,999.60
01/01/2015        1                   2015      ID_01       20,000,000.00
0
Comment
Question by:barkome
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 333 total points
ID: 40561489
Try this code:
SELECT *
FROM TableName T1
WHERE T1.ID = 'ID_01'
   AND T1.DATE = (SELECT MAX(T2.DATE) FROM TableName T2 WHERE T2.ID=T1.ID)

Open in new window

0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 333 total points
ID: 40561495
Or more generic (for all ID's):
WITH LastRow_CTE (ID, DATE) AS 
(SELECT ID, MAX(DATE) 
FROM TableName 
GROUP BY ID)

SELECT T1.*
FROM TableName T1
	INNER JOIN LastRow_CTE T2
		ON T1.ID=T2.ID AND T1.DATE=T2.DATE

Open in new window

0
 
LVL 34

Accepted Solution

by:
ste5an earned 167 total points
ID: 40561498
E.g.

DECLARE @Sample TABLE
    (
      [DATE] DATE ,
      QTR_NUMBER INT ,
      YR INT ,
      ID INT ,
      AMT FLOAT
    );

INSERT  INTO @Sample
VALUES  ( '01/10/2013', 4, 2013, 1, 4000000.00 ),
        ( '01/07/2014', 3, 2014, 1, 20000000.00 ),
        ( '01/01/2014', 1, 2014, 2, -375000.00 ),
        ( '01/04/2014', 2, 2014, 2, -374999.97 ),
        ( '01/07/2014', 3, 2014, 2, -375000.00 ),
        ( '01/10/2014', 4, 2014, 2, -449999.60 ),
        ( '01/01/2015', 1, 2015, 2, -449999.60 ),
        ( '01/01/2015', 1, 2015, 1, 20000000.00 ),
        ( '01/01/2015', 1, 2015, 1, 20000000.00 );

WITH    Ordered
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY [DATE] DESC ) AS RN
               FROM     @Sample S
             )
    SELECT  O.[DATE] ,
            O.QTR_NUMBER ,
            O.YR ,
            O.ID ,
            O.AMT
    FROM    Ordered O
    WHERE   O.RN = 1;

SELECT  T1.[DATE] ,
        T1.QTR_NUMBER ,
        T1.YR ,
        T1.ID ,
        T1.AMT
FROM    @Sample T1
WHERE   T1.[DATE] = ( SELECT    MAX(T2.[DATE])
                      FROM      @Sample T2
                      WHERE     T2.ID = T1.ID
                    );

Open in new window


btw, we work with rows, not records. And please don't user reserved words as column names.
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 

Author Comment

by:barkome
ID: 40561553
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
0
 

Author Comment

by:barkome
ID: 40561614
To add, is there a way I can make use of the SQL Function - LAG()?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40561619
LAG is available since SQL Server 2012. If you have a previous version of SQL Server then you can't.
0
 

Author Comment

by:barkome
ID: 40561622
Im using SQL Server 2014..
0
 
LVL 34

Expert Comment

by:ste5an
ID: 40561659
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?
0
 

Author Comment

by:barkome
ID: 40561679
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
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40561841
Wouldn't be better to have a status field where you can set it to OPEN or CLOSED depending on the situation?
0
 
LVL 34

Expert Comment

by:ste5an
ID: 40561844
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.
0
 

Author Comment

by:barkome
ID: 40561866
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
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Tempdb Contention - SQL SERVER 10 41
Use SSRS to email customers? 4 29
mssql 7 32
Restore a log backup compressed 3 16
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question