Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  Rolling Monthly Average for Sales - Help!

Author  Topic  ken4656 Starting Member 1 Post Posted - 2009-06-08 : 10:21:48 Hey all! Desperately need some help as I'm a SQL Newbie. In our CRM software we use SQL 2005 and our CEO needs a report from SQL that will calculate by customers on a rolling month to month list for a given date range the average monthly value per customer for a given customer category and then spit out the total average monthly value at the end of the report (phew!) So we have 3 tables that I'll have to call in here: Table: Account Columns needed: Account, Category Table: Quotes Columns Closedate, RES_ID (this is the sales person's name), ID (this is the sales order ID), account (this matches the unique identifier in the account table to tie the two tables together) Table: Itemlist Columnes needed: subttl (and also itemlist.modelno<>'SHIP' as I need to exclude shipping and the itemlist.id matches the quotes.id above and is the unique identifier to tie this table to one above. So let's say I want to run the report from May 08 to May 09 (this table has long format close dates by the way: 5/5/2009 12:00:00 AM) So I need the months across the top of the report and the accounts in alphabetical order down the left hand side. In the table body I need a grid of their month to month purchase totals (subttl as mentioned above) and then a sum value all the way to the right. The value should only be an average of any order greater than zero and exclude months where they did not purchase. At the end of the report I need them a total average in dollars of all customers in this category. In the query their category would be 'D' for instance for distributor. Any help or suggestions would be greatly appreciated!