Simplest Method for creating YTD, MTD, LYTD, LYMTD stats report for Sales System
Posted on 2004-12-01
I have a tempory table that I create with a stored procedure as neccessary. In this table are revenue, customer, vendor and sales person. I've tried several types of queries to simplify report generation, but they are all failing because I may have 12 summary records for a given vendor for this year, but perhaps only 9 summary records for last year. Running to separate queries in this instance (one for this years data and one for last years) obviously does not work. I've tried unions, sub-selects, etc. to try and get at this date, but I have reached the end of my rope.
The reports I want to generate are:
1) Salesperson Report detailing each sales staffer's monthly sales IN THE ORDER OF Sept 2004, Sept 2003, Oct 2004, Oct 2003, Nov 2004, Nov 2003, etc.. Time across the top axis, sales people down the y axis. I have had success generating this report because THERE ARE NO EMPTY MONTHS of revenue.
2) Customer Report with same setup, except Vendors purchase from on the y axis.
3) Vendor report, again the same time in x axis, but customers who purchased on the y axis
4) Vendor report, same as 3 above, but with Sales people on the y axis.
Any help on this would be appreciated! Severe time contraint here!