[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

how do you return a field set to = 0 if field idoes not contain one or more pulldown selection.

Expert: I have a stored procedure that counts the number times "super expert" or "expert" or "good"  is found in data field called rating_type  in a specified time range . However if for example  "expert" or good or super expert is not found in the specified time range I want to return a count of "0" for that  rating so my output would read:
super expert = 10
expert = 0
good = 5

currently expert does appear in my output since expert was not set or contained in the specify time range?

Does anyone know how to fix SP below to show i.e. expert = 0 or any of that could be 0.


ALTER procedure [dbo].[GetRatingCount] 
   @startdate datetime,
   @enddate datetime
as
 
set nocount on 
select rating_type, count(*) cnt
from TableA
where Date1 >= @startdate
and  Date1 < @enddate
group by rating_type

Open in new window

0
tcmmaxt
Asked:
tcmmaxt
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you have a table with the rating_types list?

say you have it (rating_type_list)

ALTER procedure [dbo].[GetRatingCount] 
   @startdate datetime,
   @enddate datetime
as
 
set nocount on 
select rt.rating_type
   , count(a.rating_type) cnt
from rating_type_list rt
left join TableA a
  on a.rating_type = rt.rating_type
  and a.Date1 >= @startdate
  and a..Date1 < @enddate
group by rt.rating_type

Open in new window

0
 
tcmmaxtAuthor Commented:
expert asked:  Do you have a table with the rating_types list?
 Yes and it loads a dropdownlist with "super expert" or "expert" or "good" . Oherwise I only have one  table callled Table A which has a column called "rating_type" and a Date1 datetime field that timestamps when the db column "rating_type" was set to either "super expert" or "expert" or "good".  In light of this, as is will the above code do the job? Thanks for your help here!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, just fill in the correct table and field names
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now