Hi @Manish Papreja ,
I checked your sample data and found that the value of Month and FY were not in a sequential order.
Please refer to below updated one:
CREATE TABLE mytable(
Month VARCHAR(3) NOT NULL
,FY INTEGER NOT NULL
,SaleAmt VARCHAR(3) NOT NULL
);
INSERT INTO mytable(Month,FY,SaleAmt) VALUES ('Jan',2020,'500');
INSERT INTO mytable(Month,FY,SaleAmt) VALUES ('Feb',2020,'450');
INSERT INTO mytable(Month,FY,SaleAmt) VALUES ('Mar',2020,'550');
INSERT INTO mytable(Month,FY,SaleAmt) VALUES ('Apr',2020,'600');
INSERT INTO mytable(Month,FY,SaleAmt) VALUES ('Jun',2020,'570');
INSERT INTO mytable(Month,FY,SaleAmt) VALUES ('Jul',2020,'700');
INSERT INTO mytable(Month,FY,SaleAmt) VALUES ('Aug',2020,'630');
INSERT INTO mytable(Month,FY,SaleAmt) VALUES ('Sep',2020,'670');
INSERT INTO mytable(Month,FY,SaleAmt) VALUES ('Oct',2020,'800');
INSERT INTO mytable(Month,FY,SaleAmt) VALUES ('Nov',2020,'-');
INSERT INTO mytable(Month,FY,SaleAmt) VALUES ('Dec',2020,'-');
INSERT INTO mytable(Month,FY,SaleAmt) VALUES ('Jan',2021,'-');
select *,lag(SaleAmt,3) over (order by cast('01 '+cast(month as char(3))+' '+cast(FY as char(4)) as date)) [Collection Target] from mytable
OR
;with cte as
(select *,ROW_NUMBER() over (order by cast('01 '+cast(month as char(3))+' '+cast(FY as char(4)) as date)) rn from mytable)
select a.Month,a.FY,a.SaleAmt,b.SaleAmt [Collection Target] from cte a
left join cte b on a.rn=b.rn+3
Output:
Month FY SaleAmt Collection Target
Jan 2020 500 NULL
Feb 2020 450 NULL
Mar 2020 550 NULL
Apr 2020 600 500
Jun 2020 570 450
Jul 2020 700 550
Aug 2020 630 600
Sep 2020 670 570
Oct 2020 800 700
Nov 2020 - 630
Dec 2020 - 670
Jan 2021 - 800
Best regards,
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
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.