SQL Server: How to get previous data

T.Zacks 3,986 Reputation points
2022-04-18T17:49:34.777+00:00

Suppose i have a table where i am storing ID and Quarter value like

1 1Q 2010
2 2Q 2010
3 3Q 2010
4 4Q 2010
5 FY 2010

100 1Q 2030

ID is auto number.

i want to get sql where i will pass quarter value 4Q 2010 and sql return value like

PrevID PrevQuarter CurrID CurrQuarter
3 3Q 2010 4 4Q 2010

please tell me how to compose a sql which return value like this way with immediate previous quarter data.

i got few links on this topic
https://stackoverflow.com/questions/4860024/how-to-get-previous-row-value
https://blog.sqlauthority.com/2013/09/22/sql-server-how-to-access-the-previous-row-and-next-row-value-in-select-statement/

thanks

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bert Zhou-msft 3,421 Reputation points
    2022-04-19T02:13:44.647+00:00

    Hi,@T.Zacks

    Welcome to Microsoft T-SQL Q&A Forum!

    The two reference documents you gave already point in the right direction, using the lag function to access a row with a specified physical offset before moving forward. Look this example,

    select (ID, Value) from table as table1 join  
    inner join table as table2  
    on table1.ID = (table2.ID -1)  
    

    if you look at it carefully, just to find the ID number of the previous row, LAG is shown here to make it easier to implement.Naomi also gave the answer, and here I use a subfunction.

    Code:

    Create table #test  
     (  
     ID int identity(1,1),  
     Quarter nvarchar(20)  
     )  
     insert into #test values  
     ('1Q 2010'),  
      ('2Q 2010'),  
       ('3Q 2010'),  
        ('4Q 2010'),  
      ('FY 2010')  
    select * from #test  
      
    select PrevID,PrevQuarter,CurrID,CurrQuarter  
    from   
    (  
    select Lag(ID,1) over(order by ID)PrevID ,LAG(Quarter,1)over(order by ID)PrevQuarter,  
    ID as CurrID,Quarter as CurrQuarter   
    from #test  
    )t  
    where CurrID=4  
    

    194024-image.png
    ->>>>Note that I used parameters in the lag function. If you can test the effect of changing 1 to 2, you will find that you can get the offset of the first two lines.
    194093-image.png
    Best regards,
    Bert Zhou


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


1 additional answer

Sort by: Most helpful
  1. Naomi 7,361 Reputation points
    2022-04-18T18:31:59.89+00:00

    ;with cte as (select LAG(ID) OVER (order by ID) as PreviousID, LAG(Quarter) OVER (order by ID) as PrevQuarter, ID as CurrID, Quarter as CurrQuarter from DatesInfo)

    select * from DatesInfo where CurrQuarter = '4Q 2010' -- this is assuming that data are loaded in the same order as ID

    1 person found this answer helpful.