Link to home
Start Free TrialLog in
Avatar of mahpog
mahpog

asked on

Adjust sql syntax for right result set

I have following sql sytnax:

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..............

I would have 5 lines output.



ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
to explain, the group by box_loc produced more rows...
Avatar of mahpog
mahpog

ASKER

Such a basis adjustment and PERFECT!!!!!!!!! thank you!