Select Monthly Sales for a Rep and Customer for 3 yearsRSS

• • Reply
  RuthlessRoth

  Member

  22 Points

  139 Posts

  Select Monthly Sales for a Rep and Customer for 3 years

  Jan 02, 2019 10:41 PM|RuthlessRoth|LINK

  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

• • Reply
  Ackerly Xu

  Participant

  1920 Points

  747 Posts

  Re: Select Monthly Sales for a Rep and Customer for 3 years

  Jan 04, 2019 02:31 AM|Ackerly Xu|LINK

  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 

  MSDN Community Support 
  Please remember to "Mark as Answer" the responses that resolved your issue. 
• • Reply
  yrb.yogi

  Star

  10073 Points

  2324 Posts

  Re: Select Monthly Sales for a Rep and Customer for 3 years

  Jan 04, 2019 05:06 AM|yrb.yogi|LINK

  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
  
  

  Yogesh Bhadauriya
  Technical Lead
  Ahmedabad, India