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.
MattAnyone got any bright ideas regarding how to do this? I'm still stuck.|||First, you will not be able to list actual daily sales for clients that return weekly summarys, or actual weekly sales for clients that return monthly summarys. You just don't have the detail data.

You might be able to solve some of your problems by including a calculated field in your table:

Daily_Sales = Number_Of_Sales/datediff(day, Start_Date, End_Date)