Summary column values need to be zero not null

Question

Post reply

Summary column values need to be zero not null

Ron Kunce

SSCrazy

Points: 2128
More actions
October 21, 2013 at 5:03 pm

#99858

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

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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]

Ron Kunce SSCrazy Points: 2128 More actions · Answer 2

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