Subqueries VS. UNION - 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

SQL Server Development (2000)

Subqueries VS. UNION

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2001-12-30 : 22:55:08

Hiya!
I need a resultset like this:
*Name* *0-29* *30-59* *60-89* *90-119* *120+ Days* *Total*(sum)
AA $10 $0 $50 $0 $25 $85
BB $0 $20 $100 $30 $0 $150

Representing # of days of aging for open items for customers. The Orders table, has fields for Price,Date of Order, etc. which are shown above. My question is, what is the best type of query to use. Subqueries for all columns is a royal pain, as there is an extensive WHERE clause for each since the user can choose a date range,certain types of items, certain types of customers, etc. making each subquery very long. Using UNION with GROUP BY is an even worse pain. Also, what will yield the best performance, considering that the Orders table might be very large(several hundred thousand rows)?

Thanks,
Sarah

izaltsman
A custom title

1139 Posts

Posted - 2001-12-30 : 23:32:53

You could try the CASE function...


SELECT [NAME], 
       , SUM(CASE WHEN DATEDIFF(DD, ORDERDATE, GETDATE()) BETWEEN 0 AND 29 THEN ordertotal ELSE 0 END) as [0-29]
	,  ....
	, sum (ordertotal) as total
FROM orders
GROUP BY [NAME]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources