troubleshooting Question

Adjust sql syntax for right result set

Avatar of mahpog
mahpog asked on
SQL
3 Comments1 Solution199 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros