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.
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 click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
25 Points
184 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
Contributor
3500 Points
1300 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
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Star
10303 Points
2359 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..
Sr Technical Lead
Ahmedabad, India