Applying HAVING (or equivalent) to subquery results

[tonydismukes]

I have a dynamically built query that returns monthly sales totals grouped by product sold, location and salesman. If the customer asked for results from 1/1/2006 to 2/15/2006, the query would look something like this:

select n.location,d.salesman_num, d.item_description,
(select ISNULL(SUM(convert(int,d1.qty_shipped)),0) 
from DETAIL d1, HEADER h1, NAMES n1 
where d1.item_description = d.item_description and d1.salesman_num = d.salesman_num 
and d1.customerkey = 2 and d1.headerkey = h1.headerkey and h1.customerkey = 2 
and n1.headerkey = h1.headerkey and n1.customerkey = 2 and n1.location = n.location 
and convert(datetime,h1.DeliveryDate) >= convert(datetime,'01-01-2006') 
and convert(datetime,h1.DeliveryDate) <= convert(datetime,'01-31-2006') )as Jan ,
(select ISNULL(SUM(convert(int,d2.qty_shipped)),0) 
from DETAIL d2, HEADER h2, NAMES n2  where d2.item_description = d.item_description 
and d2.salesman_num = d.salesman_num and d2.customerkey = 2 
and d2.headerkey = h2.headerkey and h2.customerkey = 2 and n2.headerkey = h2.headerkey 
and n2.customerkey = 2 and n2.location = n.location  
and convert(datetime,h2.DeliveryDate) >= convert(datetime,'02-01-2006') 
and convert(datetime,h2.DeliveryDate) <= convert(datetime,'02-15-2006') )as Feb 
from DETAIL d, HEADER h, NAMES n where h.customerkey = 2 and d.customerkey = 2 
and n.customerkey = 2 and h.headerkey = d.headerkey and h.headerkey = n.headerkey 
group by n.location,d.salesman_num, d.item_description
order by n.location,d.salesman_num, d.item_description

This works fine and would give results like the following:

Location Salesman_num item_description Jan Feb
Store#1 111 budget widgets 10 0
Store#1 111 premium widgets 0 0
Store#1 111 custom widgets 0 0
Store#2 111 budget widgets 15 30
Store#2 111 premium widgets 0 0
Store#2 111 custom widgets 0 5

The problem is that now the customer wants me to filter out all the rows with zero sales for any of the months covered by the query. (In the example above, rows 2, 3, and 5 of the output should be eliminated.)

I tried ading "having Jan > 0 and Feb > 0", but got the response "Invalid column name 'Jan'." and "Invalid column name 'Feb'.", so I guess applying HAVING to the subquery results doesn't work. I also tried "having ISNULL(SUM(convert(int,d1.qty_shipped)),0) > 0 and ISNULL(SUM(convert(int,d2.qty_shipped)),0) > 0", but it didn't like that either, since it didn't recognize d1 and d2 as being part of the query.

Any suggestions? I'm using SQL Server 2000, if that makes a difference.

 

[swampBoogie]

select * 
from 
  (select n.location,d.salesman_num, d.item_description,
(select ISNULL(SUM(convert(int,d1.qty_shipped)),0)
from DETAIL d1, HEADER h1, NAMES n1
where d1.item_description = d.item_description and d1.salesman_num = d.salesman_num
and d1.customerkey = 2 and d1.headerkey = h1.headerkey and h1.customerkey = 2
and n1.headerkey = h1.headerkey and n1.customerkey = 2 and n1.location = n.location
and convert(datetime,h1.DeliveryDate) >= convert(datetime,'01-01-2006')
and convert(datetime,h1.DeliveryDate) <= convert(datetime,'01-31-2006') )as Jan ,
(select ISNULL(SUM(convert(int,d2.qty_shipped)),0)
from DETAIL d2, HEADER h2, NAMES n2  where d2.item_description = d.item_description
and d2.salesman_num = d.salesman_num and d2.customerkey = 2
and d2.headerkey = h2.headerkey and h2.customerkey = 2 and n2.headerkey = h2.headerkey
and n2.customerkey = 2 and n2.location = n.location  
and convert(datetime,h2.DeliveryDate) >= convert(datetime,'02-01-2006')
and convert(datetime,h2.DeliveryDate) <= convert(datetime,'02-15-2006') )as Feb
from DETAIL d, HEADER h, NAMES n where h.customerkey = 2 and d.customerkey = 2
and n.customerkey = 2 and h.headerkey = d.headerkey and h.headerkey = n.headerkey
group by n.location,d.salesman_num, d.item_description) dt
where jan = 0 and feb = 0
order by dt.location,dt.salesman_num, dt.item_description

 

[tonydismukes]

Um, swampBoogie, I'm not sure how that will work.

Even if I put the where clause in the right place (before the group by) and change it to "Jan > 0 and Feb > 0" (which is what I want), SQL Server still gives me the "Invalid column name" error.

Anyway, I'm pretty certain I need something that gives me the equivalent functionality as a HAVING clause, since I'm eliminating rows after grouping rather than before.

 

[PHV]

And this ?
select n.location,d.salesman_num, d.item_description,
(select ISNULL(SUM(convert(int,d1.qty_shipped)),0)
from DETAIL d1, HEADER h1, NAMES n1
where d1.item_description = d.item_description and d1.salesman_num = d.salesman_num
and d1.customerkey = 2 and d1.headerkey = h1.headerkey and h1.customerkey = 2
and n1.headerkey = h1.headerkey and n1.customerkey = 2 and n1.location = n.location
and convert(datetime,h1.DeliveryDate) >= convert(datetime,'01-01-2006')
and convert(datetime,h1.DeliveryDate) <= convert(datetime,'01-31-2006') )as Jan ,
(select ISNULL(SUM(convert(int,d2.qty_shipped)),0)
from DETAIL d2, HEADER h2, NAMES n2 where d2.item_description = d.item_description
and d2.salesman_num = d.salesman_num and d2.customerkey = 2
and d2.headerkey = h2.headerkey and h2.customerkey = 2 and n2.headerkey = h2.headerkey
and n2.customerkey = 2 and n2.location = n.location
and convert(datetime,h2.DeliveryDate) >= convert(datetime,'02-01-2006')
and convert(datetime,h2.DeliveryDate) <= convert(datetime,'02-15-2006') )as Feb
from DETAIL d, HEADER h, NAMES n where h.customerkey = 2 and d.customerkey = 2
and n.customerkey = 2 and h.headerkey = d.headerkey and h.headerkey = n.headerkey
group by n.location,d.salesman_num, d.item_description
HAVING (select ISNULL(SUM(convert(int,d1.qty_shipped)),0)
from DETAIL d1, HEADER h1, NAMES n1
where d1.item_description = d.item_description and d1.salesman_num = d.salesman_num
and d1.customerkey = 2 and d1.headerkey = h1.headerkey and h1.customerkey = 2
and n1.headerkey = h1.headerkey and n1.customerkey = 2 and n1.location = n.location
and convert(datetime,h1.DeliveryDate) >= convert(datetime,'01-01-2006')
and convert(datetime,h1.DeliveryDate) <= convert(datetime,'01-31-2006') ) > 0
OR (select ISNULL(SUM(convert(int,d2.qty_shipped)),0)
from DETAIL d2, HEADER h2, NAMES n2 where d2.item_description = d.item_description
and d2.salesman_num = d.salesman_num and d2.customerkey = 2
and d2.headerkey = h2.headerkey and h2.customerkey = 2 and n2.headerkey = h2.headerkey
and n2.customerkey = 2 and n2.location = n.location
and convert(datetime,h2.DeliveryDate) >= convert(datetime,'02-01-2006')
and convert(datetime,h2.DeliveryDate) <= convert(datetime,'02-15-2006') ) > 0
order by n.location,d.salesman_num, d.item_description

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tonydismukes]

That does it PHV, thanks!

 

[swampBoogie]

Even if I put the where clause in the right place ...

The where clause is not in the wrong place. I typed the conditions incorrectly but it is located where I intended it to be. Note the use of a derived table.