question

ManishPapreja-8391 avatar image
0 Votes"
ManishPapreja-8391 asked MelissaMa-msft edited

Collection Targets on the basis of Previous Sales Figure

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

sql-server-generalsql-server-transact-sqlsql-server-analysis-servicessql-server-migration
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @ManishPapreja-8391,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @ManishPapreja-8391,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ManishPapreja-8391 avatar image
0 Votes"
ManishPapreja-8391 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ManishPapreja-8391 avatar image
0 Votes"
ManishPapreja-8391 answered MelissaMa-msft commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ManishPapreja-8391,

Could you please validate the updated answer and provide any update?

If it is still not working, please provide more sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·