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 2000 Forums  Transact-SQL (2000)  rollup problems again

Author  Topic  ratcho Starting Member 18 Posts Posted - 2002-08-21 : 11:26:30 Hi, I posted a question 2 days ago with the following text: "I use a ROLLUP clause to show the details and the totals of orders for each member for a given period. The query looks like this: select NbMember, DateOrder, Sum(Qty), Sum(UnitPrice*Qty) As TotalOrder from Transactions group by NbMember, DateOrder with rollup The problem is that I want to show the details only for those members whose Total for the given period is more than @minTotal. " Thanks a lot nr, for your answer. This gives me some directions, but may be I was not clear enough. I will give an exemple: The result from my query above looks like this:(including ROLLUP) NbNumber DateOrder Qty TotalOrder 1 2002/08/01 1 20.00 1 2002/08/10 1 10.00 1 NULL 2 30.00 2 2002/08/01 1 40.00 2 2002/08/09 1 50.00 2 NULL 2 90.00 NULL NULL 4 120.00 NOW, if ,for exemple, @minTotal=60.00, I need to check if the Total for august for each member is more than 60.00 and in this case I have to show the details for this member, Like this: NbMember DateOrder Qty TotalOrder 2 2002/08/01 1 40.00 2 2002/08/09 1 50.00 2 NULL 2 90.00 NULL NULL 2 90.00 Do you think it will be possible with rollup or I should calculate the Total for the whole period first and then use CASE or UNION may be. The difficulty here is that I should check if TotalOrder is >@minTotal only when GROUPING(DateOrder)=1 and If it's true I have to keep the lines for this NbMember even if TotalOrder is <@minTotal for some particular DateOrder. May be I should use variables? What do you think? Thanks a lot for your time!