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(datetime,entry_dt), 112) between '20130101' and '20131231'
and frp_ror = '1'
and bus_type_gc = 'G'
and srp_no IN ('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')
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
mahpogAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
afaik this can't be done via the IN statement.

The easiest way to pull this off would be to create another table with all of the spr_no values that you currently have in the IN statement, then re-write the T-SQL to do a LEFT JOIN on that table.  Something like...
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
where 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

Open in new window

0
 
mahpogAuthor Commented:
thx. will give it a go.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
 
mahpogAuthor Commented:
did not work, but gave same result as original code.  will not save for future reference.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.