Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Summary column values need to be zero not null


Summary column values need to be zero not null

Author
Message
Ron Kunce
Ron Kunce
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 496
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
Attachments
Results.bmp (2 views, 361.00 KB)
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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
Ron Kunce
Ron Kunce
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 496
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search