Collection Targets on the basis of Previous Sales Figure

Manish Papreja 21 Reputation points
2021-10-04T09:39:02.997+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,528 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
369 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,002 questions
No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,116 Reputation points Microsoft Employee
    2021-10-05T05:52:57.017+00:00

    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.

    No comments

4 additional answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,116 Reputation points Microsoft Employee
    2021-10-04T09:51:01.297+00:00

    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.

    No comments

  2. Viorel 82,741 Reputation points
    2021-10-04T12:59:41.467+00:00

    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.

    No comments

  3. Manish Papreja 21 Reputation points
    2021-10-05T05:14:17.207+00:00

    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?

    No comments

  4. Manish Papreja 21 Reputation points
    2021-10-05T06:05:11.593+00:00

    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.