Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51

    Question Unanswered: 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

  2. #2
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51

    Re: Reporting on summarised date range data

    Anyone got any bright ideas regarding how to do this? I'm still stuck.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •