I'm writing a new report for a sales system. I have a table that records sales, and I want to total the sales for each month.
The database table is called CART
The Date field is called DATESUBMITTED
The sales figure I want to total for each month is called TOTALVAT
Is it possible to write a query that will select all sales between 2 dates (populated by dropdown calenders) and then group them by month using the month/year parts of the datetime field?
I can generate a grouped Daily report using the following:
sales = db.Query("SELECT SUM(TotalVAT) AS Total, DateSubmitted FROM Cart WHERE DateSubmitted IS NOT NULL AND DateSubmitted BETWEEN @0 AND @1 GROUP BY DateSubmitted",startdate,enddate);
All I need is the ability to define the Group By as the month, not the whole date.
Any ideas would be greatly appreciated!