Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Summary column values need to be zero not null Expand / Collapse
Author
Message
Posted Monday, October 21, 2013 5:03 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:33 PM
Points: 457, Visits: 476
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


  Post Attachments 
Results.bmp (2 views, 361.30 KB)
Post #1506922
Posted Monday, October 21, 2013 5:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:47 AM
Points: 6,842, Visits: 13,368
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1506926
Posted Tuesday, October 22, 2013 7:39 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:33 PM
Points: 457, Visits: 476
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
Post #1507142
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse