rollup problems - SQL Server Forums

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

Author

Topic

ratcho
Starting Member

18 Posts

Posted - 2002-08-19 : 12:00:37

Please, help me with the following problem.

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.

Any ideas?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-19 : 12:08:21

with rollup
having Sum(UnitPrice*Qty) > @minTotal.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-19 : 12:10:30

damn you nr i was just about to post

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-19 : 12:10:57

Or if you wanted a different period

from Transactions
where NbMember in (select NbMember from Transactions where DateOrder between x and y group by NbMember having Sum(UnitPrice*Qty) > @minTotal)
group by
NbMember, DateOrder
with rollup

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ratcho
Starting Member

18 Posts

Posted - 2002-08-19 : 14:33:21

Thanks, 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 when GROUPING(DateOrder)=1 and If it's true I have to keep the records for this NbMember even if TotalOrder is <@minTotal for some particular DateOrder. May be I should use variables?

Thanks a lot for your time!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources