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

Posted on 2009-04-22
Last Modified: 2012-05-06
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


set nocount on 

select rating_type, count(*) cnt

from TableA

where Date1 >= @startdate

and  Date1 < @enddate

group by rating_type

Open in new window

Question by:tcmmaxt
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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
    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


    Author Comment

    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!
    LVL 142

    Accepted Solution

    yes, just fill in the correct table and field names

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now