Reporting On Summarised Date Range Data

Hi all

Suppliers send my client sales figures in different formats. Some send data for each sale, some summarise by week, month or quarter.

I need to report on this data, showing estimates as to how many sales per day, week, month or quarter. To give you an example of the data I receive, see a simplified script below.

CREATE TABLE dbo.sales_summary (
summary_id int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
start_date datetime NOT NULL ,
end_date datetime NOT NULL ,
number_of_sales int not null
)
GO

insert into sales_summary VALUES ( '20030101', '20030131', 100)
insert into sales_summary VALUES ( '20030101', '20030120', 150)
insert into sales_summary VALUES ( '20030111', '20030131', 200)
insert into sales_summary VALUES ( '20030201', '20030228', 120)
insert into sales_summary VALUES ( '20030201', '20030207', 50)
go

As you can see, I essentially receive a date range and a number of sales in each row. The data in the real system is received from more than 100 suppliers and the sales_summary table has more than a million rows in it.

Can anyone suggest an efficient way of being able to create a report that lists sales
- for each day
- for each week
- for each month
etc.

An example of the daily report might look something like

Date Number of Sales
01-Jan-03 20
02-Jan-03 0
03-Jan -03 15
etc.

An example of the weekly report might look something like

Week Starting Number of Sales
01-Jan-03 100
08-Jan-03 135
15-Jan-03 54
etc.

This has been driving me nuts for a while so any help is appreceiated.
Matt