Summary column values need to be zero not null

  • I have set of subreports each returning a specific group count value with individual WHERE clauses. ISNULL(COUNT(*), 0) is not usefull as if WHERE clause has not data to return the value is always null. How do I respecify it to return zero (0) instead of null for the specific columns list in the below query?

    WITH TotalAssignments

    AS

    ( SELECT

    TeamsRSF

    ,TeamSupervisor

    ,AssignedWorker

    ,ISNULL(COUNT(AssignmentID),'0') AS TotalAssignments

    FROM Reports.InvestigationAssignments

    GROUP BY TeamsRSF,TeamSupervisor,AssignedWorker

    )

    ,PriorAssigned

    AS

    ( SELECT

    TeamsRSF

    ,TeamSupervisor

    ,AssignedWorker

    ,ISNULL(COUNT(AssignmentID),'0') AS PriorAssigned

    FROM Reports.InvestigationAssignments

    Where (AssignmentStartDate < Convert(char(10),RptFirstDay, 20))

    GROUP BY TeamsRSF,TeamSupervisor,AssignedWorker

    )

    ,NewAssigned

    AS

    ( SELECT

    TeamsRSF

    ,TeamSupervisor

    ,AssignedWorker

    ,ISNULL(COUNT(AssignmentID),'0') AS NewAssigned

    FROM Reports.InvestigationAssignments

    Where (AssignmentStartDate >= Convert(char(10),RptFirstDay, 20))

    GROUP BY TeamsRSF,TeamSupervisor,AssignedWorker

    )

    ,Completed

    AS

    ( SELECT

    TeamsRSF

    ,TeamSupervisor

    ,AssignedWorker

    ,ISNULL(COUNT(AssignmentID),'0') AS Completed

    FROM Reports.InvestigationAssignments

    WHERE (InvestigationEndDate >= Convert(char(10),RptFirstDay, 20)

    AND InvestigationEndDate <= Convert(char(10),RptLastDay, 20))

    GROUP BY TeamsRSF,TeamSupervisor,AssignedWorker

    )

    SELECT DISTINCT

    ISNULL(IA.TeamsRSF, 'Not Provided') AS 'Teams RSF'

    ,ISNULL(IA.TeamSupervisor, 'Not Provided') AS 'Teams Supervisor'

    ,ISNULL(IA.AssignedWorker, 'Not Provided') AS Worker

    ,TA.TotalAssignments

    ,PA.PriorAssigned

    ,NA.NewAssigned

    ,CA.Completed

    FROM Reports.InvestigationAssignments IA

    LEFT OUTER JOIN TotalAssignments TA

    ON TA.TeamsRSF = IA.TeamsRSF

    AND TA.TeamSupervisor = IA.TeamSupervisor

    AND TA.AssignedWorker = IA.AssignedWorker

    LEFT OUTER JOIN PriorAssigned PA

    ON PA.TeamsRSF = IA.TeamsRSF

    AND PA.TeamSupervisor = IA.TeamSupervisor

    AND PA.AssignedWorker = IA.AssignedWorker

    LEFT OUTER JOIN NewAssigned NA

    ON NA.TeamsRSF = IA.TeamsRSF

    AND NA.TeamSupervisor = IA.TeamSupervisor

    AND NA.AssignedWorker = IA.AssignedWorker

    LEFT OUTER JOIN Completed CA

    ON CA.TeamsRSF = IA.TeamsRSF

    AND CA.TeamSupervisor = IA.TeamSupervisor

    AND CA.AssignedWorker = IA.AssignedWorker

    The Results look like that in the attached .BMP image where the last four columns should be zero not null.

    Ron K.

    "Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler

  • something along those lines should help:

    SELECT DISTINCT

    ISNULL(IA.TeamsRSF, 'Not Provided') AS 'Teams RSF'

    ,ISNULL(IA.TeamSupervisor, 'Not Provided') AS 'Teams Supervisor'

    ,ISNULL(IA.AssignedWorker, 'Not Provided') AS Worker

    ,ISNULL(TA.TotalAssignments,0) AS TotalAssignments

    ,ISNULL(PA.PriorAssigned,0) AS PriorAssigned

    ,ISNULL(NA.NewAssigned,0) NewAssigned

    ,ISNULL(CA.Completed,0) Completed



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • DUH! In this case it is sometimes difficult to see the tree because the forest gets in the way.

    Thanks very much!! I spent way too much time on this for something overly obvious.

    Ron K.

    "Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply