I am looking to report the last 6 months Gross Total Sales on a website using ASP VbSCript.
eg. Jan|Feb|Mar….
eg. 10000|5000|32445…
I have successfully create a monthly mapping table in Access with 2 columns of static data MonthNo (1-12), MonthName (Jan-Dec). Then i wrote a quick Query in Access called ‘Monthly’ as follows:
SELECT TOP 6 Months.MonthName, IIf([MonthNo]>Month(Now()),Year(Now())-1,Year(Now())) AS [Year], Months.MonthNo
FROM Months
ORDER BY IIf([MonthNo]>Month(Now()),Year(Now())-1,Year(Now())) DESC , Months.MonthNo DESC;
The above returns the last 6 months in correct descending order by Year and then Month.
My second Query matches the above Select statement to the Orders table using the following SQL:
SELECT Sum(Orders.OrderTotal) AS Total, Monthly.Year, Monthly.MonthNo
FROM Monthly LEFT JOIN Orders ON (Monthly.Year = Year(Orders.OrderDate)) AND (Monthly.MonthNo = Month(Orders.OrderDate))
GROUP BY Monthly.Year, Monthly.MonthNo
ORDER BY Monthly.Year DESC , Monthly.MonthNo DESC;
Which perfectly returns the Gross Sales Total for the past 6 months.
I have built it this way so i can always get 6 months worth of data even if NO sales were made in any given month.
ISSUE: How can i convert the above Query’s using IF statements into a singular proper MSSQL statement so i can return the recordset on my asp page?
Or perhaps someone has an alternate SQL statement to perform the same trick?
