I read through a lot of old posts but I didn't see anything that covered my particular situation, so please bear with me. I have a view (MS SQL 2005) that contains sales data:-SalespersonNo-CustomerNo-ProductType-PostingDate-DollarsSold-QtySoldI haven't yet decided what to use for a front-end (it's either Access or Crystal Reports), but I want to try to do as much sorting on the server side before the data is imported to the reporting tool. I need to produce a report that shows sales ($ and qty) by ProductType, by Customer, by Salesperson for a given month with comparison data for the same month in the prior year. Something like this:Salesperson | Customer | ProductType | DollarsSoldJan09 | DollarsSoldJan08 | %change | QtySoldJan09 | QtySoldJan08 | %changeIdeally I'd like the final table/view to show summary data by month for each ProductType for CY (09) and LY (08). I thought I could do this in Access with a Crosstab query, and I was able to make a crosstab query for each year with the data I needed, but when I tried to join them I got some really funky results. So I'd prefer to do as much on the server side as possible. Here's what I've got so far for the 2008 view:SELECT TOP (100) PERCENT SalespersonName, CustomerName, ProductType, SUM(DollarsSold) AS DollarsSold08, SUM(QuantitySold) AS QtySold08, MONTH(PostingDate) AS MonthFROM dbo.uvwMoSalesRpt_08GROUP BY MONTH(PostingDate), SalespersonName, CustomerName, PostingDate, ProductTypeORDER BY SalespersonName, CustomerName, ProductLineDescription
At least with this I can get the month in a 2-digit format, but it's not summarized by month (single entry for each ProductType for each month). I appreciate some help in putting together a proper view(s).
Need Help Grouping by Month & Year
Posted - 2009-03-30 : 20:48:03
