CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Alex Gordon
Alex Gordon

Reputation: 60731

adding a filter to a HAVING query

I have a great query that works well:

select distinct f.client_id
from f_accession_daily f
left join SalesDWH..TestPractices tests
on tests.ClientID=f.CLIENT_ID
where tests.ClientID is null
group by f.client_id
having max(f.received_date) between '20120601' and '20120630'

I need to limit the result set to only f.client_id where the count(*) of that specific f.client_id was 40 or greater for the pervious month (namely between '20120501' and '20120530')

here's what i tried:

select distinct f.client_id
from f_accession_daily f
left join SalesDWH..TestPractices tests
on tests.ClientID=f.CLIENT_ID
where tests.ClientID is null
group by f.client_id
having max(f.received_date) between '20120601' and '20120630'
) NotOrderedIn6Months
on f.CLIENT_ID=NotOrderedIn6Months.CLIENT_ID
right join
(select client_id,COUNT(*) count from F_ACCESSION_DAILY
where RECEIVED_DATE between '20120501' and '20120530'
group by CLIENT_ID
having COUNT(*)>=40
) Having40
on Having40.CLIENT_ID=f.CLIENT_ID

Upvotes: 0

Views: 41

Answers (2)

Terje D.
Terje D.

Reputation: 6315

Just add 

AND f.client_id IN
(SELECT client_id FROM F_ACCESSION_DAILY
WHERE RECEIVED_DATE BETWEEN '20120501' AND '20120530'
GROUP BY client_id
HAVING COUNT(*) >= 40)

to the where clause of you original query.

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Pre-aggregate the data for the last month (there are 31 days in May, not 30), then join into it.

   select f.client_id
     from f_accession_daily f
     join (
        select client_id
          from f_accession_daily
         where received_date between '20120501' and '20120531'
      group by client_id
        having count(*) >= 40
      ) A on A.client_id = f.client_id
left join SalesDWH..TestPractices tests
       on tests.ClientID=f.CLIENT_ID
    where tests.ClientID is null
 group by f.client_id
   having max(f.received_date) between '20120601' and '20120630';

Note also that you won't need DISTINCT when you already have GROUP BY.

Upvotes: 1

Related Questions