Collection Targets on the basis of Previous Sales Figure

Question

Dear Experts,

I have having following Yearwise and Monthwise Sales Figures.

S. No Month FY SaleAmt
1 Jan 2020 500
2 Feb 2020 450
3 Mar 2020 550
4 Apr 2021 600
5 Jun 2021 570
6 Jul 2021 700
7 Aug 2021 630
8 Sep 2021 670
9 Oct 2021 800
10 Nov 2021 -
11 Dec 2021 -
12 Jan 2021 -

I need to have one more column of “Collection Target” in which I need to fill collection targets from current to coming 3 months (Total 4 Months). The collection target of current month should be the 3Months Old SaleAmt figure. In other words for October month the collection target figure is Rs.700.

Following is the desired output

S. No Month FY SaleAmt Collection Target
1 Jan 2020 500
2 Feb 2020 450
3 Mar 2020 550
4 Apr 2021 600
5 Jun 2021 570
6 Jul 2021 700
7 Aug 2021 630
8 Sep 2021 670
9 Oct 2021 800 700
10 Nov 2021 - 630
11 Dec 2021 - 670
12 Jan 2021 - 800

CREATE TABLE mytable(
S_No INTEGER NOT NULL PRIMARY KEY
,Month VARCHAR(3) NOT NULL
,FY INTEGER NOT NULL
,SaleAmt VARCHAR(3) NOT NULL
);
INSERT INTO mytable(S_No,Month,FY,SaleAmt) VALUES (1,'Jan',2020,'500');
INSERT INTO mytable(S_No,Month,FY,SaleAmt) VALUES (2,'Feb',2020,'450');
INSERT INTO mytable(S_No,Month,FY,SaleAmt) VALUES (3,'Mar',2020,'550');
INSERT INTO mytable(S_No,Month,FY,SaleAmt) VALUES (4,'Apr',2021,'600');
INSERT INTO mytable(S_No,Month,FY,SaleAmt) VALUES (5,'Jun',2021,'570');
INSERT INTO mytable(S_No,Month,FY,SaleAmt) VALUES (6,'Jul',2021,'700');
INSERT INTO mytable(S_No,Month,FY,SaleAmt) VALUES (7,'Aug',2021,'630');
INSERT INTO mytable(S_No,Month,FY,SaleAmt) VALUES (8,'Sep',2021,'670');
INSERT INTO mytable(S_No,Month,FY,SaleAmt) VALUES (9,'Oct',2021,'800');
INSERT INTO mytable(S_No,Month,FY,SaleAmt) VALUES (10,'Nov',2021,'-');
INSERT INTO mytable(S_No,Month,FY,SaleAmt) VALUES (11,'Dec',2021,'-');
INSERT INTO mytable(S_No,Month,FY,SaleAmt) VALUES (12,'Jan',2021,'-');

Can anybody help my in achieving above collection targets on the basis of previous sales figures.

Manish

Answer 1

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.

Answer 2

Hi @Manish Papreja ,

Welcome to Microsoft Q&A!

If you have SQL Server 2012 and later verison, please try with below and check whether it is helpful.

select *,lag(SaleAmt,3) over (order by S_No) [Collection Target] from mytable  

If you have a very old verison, you could also refer to below:

  select a.*,b.SaleAmt [Collection Target] from  mytable a  
  left join mytable b   
  on a.S_No=b.S_No+3  

Output:

S_No Month FY SaleAmt Collection Target  
1 Jan 2020 500 NULL  
2 Feb 2020 450 NULL  
3 Mar 2020 550 NULL  
4 Apr 2021 600 500  
5 Jun 2021 570 450  
6 Jul 2021 700 550  
7 Aug 2021 630 600  
8 Sep 2021 670 570  
9 Oct 2021 800 700  
10 Nov 2021 - 630  
11 Dec 2021 - 670  
12 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.

Answer 3

Try a statement:

;
with Q as
(
    select *, convert(date, concat([Month], ' ', FY), 106) d
    from mytable
)
update q1
set q1.[Collection Target] = q2.SaleAmt
from Q q1
left join Q q2 on q2.d = dateadd(month, -3, q1.d)
where q1.d > eomonth(getdate(), -1)

Also check if your data (years) are valid.

Answer 4

Many Thanks MelissaMa-msft for extremely helpful answer. It seems to be perfect answer but in my actual query I don't have S_No column. Is it possible to convert same query with the help of Row_Number() instead of S_No?

Answer 5

Many Thanks Viorel-1 for your help. I am trying to relate the suggested answer with my actual query and would revert to you after implementing the same.