Petterns for SQL Tables and Stored Procedures

I am looking for some patterns in SQL Server. The Patterns and Paractices
did not seem to have what I am looking for, but it would seem to me that it
is a very commmon scenario that must have been covered.
Basically, it is the problem of aggregating data for overviews.
Assume the following:
Table Services
[SID][ServiceName][ServiceCategoryID][PI
D] [AmmountDue]
Table Payer
[PID][PayerCategory][PayerName]
Table Payments
[PayID][SID][Date][Amount][PID] ** Occasionally a third party might pay
for someone else
Table ServiceCategory
[ServiceCategoryID][ServiceCategoryName]
Table PayerCategory
[PayerCategoryID][PayerCategoryName]
Now, I want to get a summary of the data very quickly that breaks things
down like:
By ServiceCategory
TotalAmountPaid TotalDue AmountDueFrom30DaysAgo ADF31-60DaysAgo
adf61-90Days Ago
Then break these down by PayerCategory
This would seem like a common type of thing, and Ican think of ways to do
this but that take a lot of time, if there are millions of rows, and I can
imagine that triggers might be useful here to keep up to date, but I am
unfamiliar with them.
If you can give me any guidance on this it owuld be helpful. For extra
points, what about being able to dynamically change the periods from say
0-30days to 0-15 days)
Thanks a lot
BBFor starters, you need to post some ddl, sample data and expected results.
Not just a narrative.
I can tell you this though - without dates in your services table or
payments table to know when the service and payments took place, what you're
looking for is impossible.
"bobbyballgame" wrote:

> I am looking for some patterns in SQL Server. The Patterns and Paractices
> did not seem to have what I am looking for, but it would seem to me that i
t
> is a very commmon scenario that must have been covered.
> Basically, it is the problem of aggregating data for overviews.
> Assume the following:
> Table Services
> [SID][ServiceName][ServiceCategoryID][PI
D] [AmmountDue]
> Table Payer
> [PID][PayerCategory][PayerName]
> Table Payments
> [PayID][SID][Date][Amount][PID] ** Occasionally a third party might pay
> for someone else
> Table ServiceCategory
> [ServiceCategoryID][ServiceCategoryName]
> Table PayerCategory
> [PayerCategoryID][PayerCategoryName]
>
> Now, I want to get a summary of the data very quickly that breaks things
> down like:
> By ServiceCategory
> TotalAmountPaid TotalDue AmountDueFrom30DaysAgo ADF31-60DaysAgo
> adf61-90Days Ago
> Then break these down by PayerCategory
>
> This would seem like a common type of thing, and Ican think of ways to do
> this but that take a lot of time, if there are millions of rows, and I can
> imagine that triggers might be useful here to keep up to date, but I am
> unfamiliar with them.
> If you can give me any guidance on this it owuld be helpful. For extra
> points, what about being able to dynamically change the periods from say
> 0-30days to 0-15 days)
> Thanks a lot
> BB
>
>
>|||Steve,
Thanks. The tables are internal ( I would not be allowed to post them) and a
lot more complicated. For example the Payments Table has 31 fields in it, so
I was trying to simplify.
The Service does have a Date field. Sorry about the ommission. Really, I am
looking for a general pattern for the problem of needing aggregate data from
many, amny rows quickly, so I thought a narrative would be more useful.
I will work on a model that is a little more simple, and for what is worth,
I need the data in XML format from SQL 2000.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:070D02CF-7367-4593-91F4-6533588D830E@.microsoft.com...
> For starters, you need to post some ddl, sample data and expected results.
> Not just a narrative.
> I can tell you this though - without dates in your services table or
> payments table to know when the service and payments took place, what
> you're
> looking for is impossible.
>
> "bobbyballgame" wrote:
>