SQL Server: How to get previous data

Question

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

Answer 1

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  

->>>>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.

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.

Answer 2

;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