mahpog
asked on
sql sytnx : include a count for each srp_no in range
I would like to have all srp_no's in the "IN" statement with a 0 for those that have no count.
Right now, only get srp_no's tht meet other conditions.
**********************
select srp_no, isnull(count(srp_no),0) as ttlcnt
from asrp_usage
where convert(char(10),convert(d
and frp_ror = '1'
and bus_type_gc = 'G'
and srp_no IN ('14-1','15-1','31-11','31
group by srp_no
order by srp_no
Resultset:
srp_no ttlcnt
14-1 6
15-1 5
31-14 1
31-15 5
31-5 3
31-9 1
Right now, only get srp_no's tht meet other conditions.
**********************
select srp_no, isnull(count(srp_no),0) as ttlcnt
from asrp_usage
where convert(char(10),convert(d
and frp_ror = '1'
and bus_type_gc = 'G'
and srp_no IN ('14-1','15-1','31-11','31
group by srp_no
order by srp_no
Resultset:
srp_no ttlcnt
14-1 6
15-1 5
31-14 1
31-15 5
31-5 3
31-9 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the grade. Good luck with your project. -Jim
ASKER
did not work, but gave same result as original code. will not save for future reference.
Try moving the WHERE clause to the JOIN..
CREATE TABLE #sprno (no varchar(10))
INSERT INTO #sprno (no)
VALUES ('14-1'),('15-1'),('31-11'),('31-14'),('31-15'),('31-19'),('31-20'),('31-21'),('31-5'),('31-6'),('31-9'),('GB-01'),('GB-02'),('GB-06')
-- Then your main query will be something like...
select s.srp_no, isnull(count(u.srp_no),0) as ttlcnt
from #sprno s
-- LEFT ensures that all #sprno rows are returned
LEFT JOIN asrp_usage u ON u.spr_no = s.spr_no
AND convert(char(10),convert(datetime,u.entry_dt), 112) between '20130101' and '20131231'
AND u.frp_ror = '1'
AND u.bus_type_gc = 'G'
group by s.srp_no
order by s.srp_no
ASKER