select rptdate as mmddyyyy,
SUM(CASE WHEN box_loc = 'SMCBUYR' THEN 1 ELSE 0 END) AS 'SMCBUYR'
from a_smc_rpt_helper
left join a_smc
on rptdate = date_rcvd
where rptdate between '04/07/2008' and '04/11/2008'
group by rptdate, box_loc
order by rptdate
And the result set is:
mmddyyyy SMCBUYR
4/7/2008 12:00:00.000 AM 0
4/8/2008 12:00:00.000 AM 0
4/8/2008 12:00:00.000 AM 2
4/8/2008 12:00:00.000 AM 0
4/8/2008 12:00:00.000 AM 0
4/9/2008 12:00:00.000 AM 0
4/9/2008 12:00:00.000 AM 3
4/9/2008 12:00:00.000 AM 0
4/10/2008 12:00:00.000 AM 0
4/11/2008 12:00:00.000 AM 0
I should only have one entry for 4/08 and 4/09 with 2 and 3 respectively.........
Otherwise result set would be perfect..............