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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
to explain, the group by box_loc produced more rows...
ASKER
Such a basis adjustment and PERFECT!!!!!!!!! thank you!