locked
Select Monthly Sales for a Rep and Customer for 3 years RRS feed

  • Question

  • User1215529056 posted

    Hi,

    I have done this 36 times:

    IsNull((Select Sum(ARTransaction.Netval)
    FROM ARTransaction
    Where Customer.Account=ARTransaction.CustAccount
    AND ARTransaction.TransactionType='Invoice'
    AND SalesRep.Code=ARTransaction.SalesRepCode
    AND Month(ARTransaction.TransactionDate)=1
    AND Year(ARTransaction.TransactionDate)=@year),0) as Jan1

    change the Month() from 1-12

    and change the @year from @year to @year-1 to @year-2

    and Jan1 to Jan2 to Jan3 etc.

    Making a total of 36 fields

    Seems to me I should be able to make 12 fields and one year and have it cycle through as many years as requested.

    I Foxpro I would have made a query to a temp table scanned through it and loaded the rows as needed. I don't know how to do that in SQL.

    The stored procedure will be used to load a Crystal Report.

    The parameters would be:

    Last year

    Howe many years do you want to go back?

    Ex: 2018  3 would go back to to 2016

    Wednesday, January 2, 2019 10:41 PM

Answers

  • User-2082239438 posted

    You may use this kind of query to iterate the years..

    DECLARE @NoofYearsReq INT=3
    
    ;WITH CTE AS
    (
    SELECT DATEPART(YEAR,GETDATE()) AS Year,1 AS StartYear
    
    UNION ALL
    
    SELECT CTE.Year-1 AS Year,CTE.StartYear+1 AS StartYear
    FROM CTE
    WHERE CTE.StartYear+1<=@NoofYearsReq
    
    ),CTE1 AS 
    (
    SELECT 1 AS StartMonth
    
    UNION ALL
    
    SELECT CTE.StartMonth+1 AS StartMonth
    FROM CTE1 AS CTE
    WHERE CTE.StartMonth+1<=12
    )
    SELECT Year,StartMonth
    FROM CTE
    CROSS JOIN CTE1
    ORDER BY Year,StartMonth
    
    ------------OUTPUT------------
    --Year	StartMonth
    --2017	1
    --2017	2
    --2017	3
    --2017	4
    --2017	5
    --2017	6
    --2017	7
    --2017	8
    --2017	9
    --2017	10
    --2017	11
    --2017	12
    --2018	1
    --2018	2
    --2018	3
    --2018	4
    --2018	5
    --2018	6
    --2018	7
    --2018	8
    --2018	9
    --2018	10
    --2018	11
    --2018	12
    --2019	1
    --2019	2
    --2019	3
    --2019	4
    --2019	5
    --2019	6
    --2019	7
    --2019	8
    --2019	9
    --2019	10
    --2019	11
    --2019	12
    
    
    -------------------Use in your query like below (not tested)
    DECLARE @NoofYearsReq INT=3
    
    ;WITH CTE AS
    (
    	SELECT DATEPART(YEAR,GETDATE()) AS Year,1 AS StartYear
    
    	UNION ALL
    
    	SELECT CTE.Year-1 AS Year,CTE.StartYear+1 AS StartYear
    	FROM CTE
    	WHERE CTE.StartYear+1<=@NoofYearsReq
    
    ),CTE1 AS 
    (
    	SELECT 1 AS StartMonth
    
    	UNION ALL
    
    	SELECT CTE.StartMonth+1 AS StartMonth
    	FROM CTE1 AS CTE
    	WHERE CTE.StartMonth+1<=12
    ),FinalCTE AS
    (
    	SELECT Year,StartMonth
    	FROM CTE
    	CROSS JOIN CTE1
    )
    SELECT IsNull((Select Sum(ARTransaction.Netval)
    FROM ARTransaction
    LEFT JOIN FinalCTE ON Month(ARTransaction.TransactionDate)=FinalCTE.StartMonth
    	AND YEAR(ARTransaction.TransactionDate)=FinalCTE.Year
    Where Customer.Account=ARTransaction.CustAccount
    AND ARTransaction.TransactionType='Invoice'
    AND SalesRep.Code=ARTransaction.SalesRepCode
    
    
    ------------OUTPUT------------
    --Year	StartMonth
    --2017	1
    --2017	2
    --2017	3
    --2017	4
    --2017	5
    --2017	6
    --2017	7
    --2017	8
    --2017	9
    --2017	10
    --2017	11
    --2017	12
    --2018	1
    --2018	2
    --2018	3
    --2018	4
    --2018	5
    --2018	6
    --2018	7
    --2018	8
    --2018	9
    --2018	10
    --2018	11
    --2018	12
    --2019	1
    --2019	2
    --2019	3
    --2019	4
    --2019	5
    --2019	6
    --2019	7
    --2019	8
    --2019	9
    --2019	10
    --2019	11
    --2019	12
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 4, 2019 5:06 AM

All replies

  • User-893317190 posted

    Hi RuthlessRoth,

    One way to execute many times in sqlserver like in c# is to use while loop.

    Please refer to https://www.techonthenet.com/sql_server/loops/for_loop.php

    To store the result of every loop , you could use temp table or table variable.

    https://codingsight.com/introduction-to-temporary-tables-in-sql-server/

    https://odetocode.com/articles/365.aspx

    These two tables will not exist all the time like real table so that you could save space in your database.

    If you don't need to  save space or you are not familiar with them , you could also use real table.

    Structure is like:

    year   1  2 3 4 5 6 7 8 9 10 11 12.

    Then you could use a for loop to insert or update the table , for example at first 2017-01 ,secondly 2017-02...

    At last , you could return the result set of the table.

    Best regards,

    Ackerly Xu 

    Friday, January 4, 2019 2:31 AM
  • User-2082239438 posted

    You may use this kind of query to iterate the years..

    DECLARE @NoofYearsReq INT=3
    
    ;WITH CTE AS
    (
    SELECT DATEPART(YEAR,GETDATE()) AS Year,1 AS StartYear
    
    UNION ALL
    
    SELECT CTE.Year-1 AS Year,CTE.StartYear+1 AS StartYear
    FROM CTE
    WHERE CTE.StartYear+1<=@NoofYearsReq
    
    ),CTE1 AS 
    (
    SELECT 1 AS StartMonth
    
    UNION ALL
    
    SELECT CTE.StartMonth+1 AS StartMonth
    FROM CTE1 AS CTE
    WHERE CTE.StartMonth+1<=12
    )
    SELECT Year,StartMonth
    FROM CTE
    CROSS JOIN CTE1
    ORDER BY Year,StartMonth
    
    ------------OUTPUT------------
    --Year	StartMonth
    --2017	1
    --2017	2
    --2017	3
    --2017	4
    --2017	5
    --2017	6
    --2017	7
    --2017	8
    --2017	9
    --2017	10
    --2017	11
    --2017	12
    --2018	1
    --2018	2
    --2018	3
    --2018	4
    --2018	5
    --2018	6
    --2018	7
    --2018	8
    --2018	9
    --2018	10
    --2018	11
    --2018	12
    --2019	1
    --2019	2
    --2019	3
    --2019	4
    --2019	5
    --2019	6
    --2019	7
    --2019	8
    --2019	9
    --2019	10
    --2019	11
    --2019	12
    
    
    -------------------Use in your query like below (not tested)
    DECLARE @NoofYearsReq INT=3
    
    ;WITH CTE AS
    (
    	SELECT DATEPART(YEAR,GETDATE()) AS Year,1 AS StartYear
    
    	UNION ALL
    
    	SELECT CTE.Year-1 AS Year,CTE.StartYear+1 AS StartYear
    	FROM CTE
    	WHERE CTE.StartYear+1<=@NoofYearsReq
    
    ),CTE1 AS 
    (
    	SELECT 1 AS StartMonth
    
    	UNION ALL
    
    	SELECT CTE.StartMonth+1 AS StartMonth
    	FROM CTE1 AS CTE
    	WHERE CTE.StartMonth+1<=12
    ),FinalCTE AS
    (
    	SELECT Year,StartMonth
    	FROM CTE
    	CROSS JOIN CTE1
    )
    SELECT IsNull((Select Sum(ARTransaction.Netval)
    FROM ARTransaction
    LEFT JOIN FinalCTE ON Month(ARTransaction.TransactionDate)=FinalCTE.StartMonth
    	AND YEAR(ARTransaction.TransactionDate)=FinalCTE.Year
    Where Customer.Account=ARTransaction.CustAccount
    AND ARTransaction.TransactionType='Invoice'
    AND SalesRep.Code=ARTransaction.SalesRepCode
    
    
    ------------OUTPUT------------
    --Year	StartMonth
    --2017	1
    --2017	2
    --2017	3
    --2017	4
    --2017	5
    --2017	6
    --2017	7
    --2017	8
    --2017	9
    --2017	10
    --2017	11
    --2017	12
    --2018	1
    --2018	2
    --2018	3
    --2018	4
    --2018	5
    --2018	6
    --2018	7
    --2018	8
    --2018	9
    --2018	10
    --2018	11
    --2018	12
    --2019	1
    --2019	2
    --2019	3
    --2019	4
    --2019	5
    --2019	6
    --2019	7
    --2019	8
    --2019	9
    --2019	10
    --2019	11
    --2019	12
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 4, 2019 5:06 AM