SQL report direction needed...

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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

SQL report direction needed...

Author

Topic

JDAustin
Starting Member

2 Posts

Posted - 2009-08-19 : 14:44:44

So I have a bunch of usage data. This data is broken down by customer and by period.

For example customer A has a usage amount of 330 between 3/15/09 and 4/13/09 (dates are stored in both mm/dd/yyyy format and Year/day of year format (so 3/15/09 would be 2009074)).

Now the date period varies from one customer to another and the length can vary from 4 to 45+ days.

Each customer has a cutoff date. With that cutoff date, I need to know the average usage for the 6 30day periods before the cutoff and the 6 30day period after the cutoff.

Any suggestions on the route I should go here? Anything in the OLAp/Analysis Services that can help?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-19 : 16:03:16

are the date ranges stored in one record or multiple records? can you show us a few sample rows? that would make it easy to help you write a query

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-19 : 16:24:52

I would first create queries to generate the data I want, then create a SP which dynamically generates this query and executes it, reading the usage, cut off and date ranges from tables

JDAustin
Starting Member

2 Posts

Posted - 2009-08-19 : 17:27:05

quote:
Originally posted by russell

are the date ranges stored in one record or multiple records? can you show us a few sample rows? that would make it easy to help you write a query



CustomerNumber  StartDate  StartDay    EndDate     EndDay     Period#Days  Usage
000001          10/31/08   2008305     11/24/08    2008329    24           1196
000001          11/24/08   2008329     12/23/08    2008358    29           797
000001          12/23/08   2008358     1/23/09     2009023    31           701
000001          1/23/09    2009023     2/23/09     2009054    31           772
000001          2/23/09    2009054     3/24/09     2009083    29           724
000001          3/24/09    2009083     4/22/09     2009112    29           731
000001          4/22/09    2009112     5/21/09     2009141    29           807
000001          5/21/09    2009141     6/23/09     2009174    33           1083
000001          6/23/09    2009174     7/24/09     2009205    31           953

Here is a sample of the data i'm working with. My record set size is about 500k+ records.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources