|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Petterns for SQL Tables and Stored Proceduresdid 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][PID] [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 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. Show quote "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 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][PID] [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. Show quote "Steve" <St***@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: > >> 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][PID] [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 >> >> >> >> >>  |
|||||||||||||||||||||||
Other interesting topics