Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How do/should i use coalesce for this query?

Author  Topic 

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-09-11 : 06:46:25
Hello,

I'm trying to do a monthly count query but when there are no records for the field I am using to group the count by, it doesn't return the field. As I'm doing a monthly count, if there is no data I want it to return a zero. In the first column there should be all TeamAgencyCodes but the below is only returning those that have data. I googled and someone said to use Coalesce to fix this. I tried to follow their example but it won't work. I've took it out of the code for now. Is coalesce there correct way for this please?

J

SELECT Tbl_FamiliesProgress_1.TeamAgencyCode,
(SELECT COUNT(dbo.Tbl_FamiliesProgress.FamiliesID) AS [July 2014]
FROM dbo.Tbl_FamiliesProgress INNER JOIN
dbo.Tbl_Families ON dbo.Tbl_FamiliesProgress.FamiliesID = dbo.Tbl_Families.FamiliesID
WHERE (dbo.Tbl_Families.Withdrawn IS NULL) AND (dbo.Tbl_Families.DateApproved IS NULL) AND (dbo.Tbl_FamiliesProgress.FamiliesActionDate BETWEEN
CONVERT(DATETIME, '2014-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2014-07-31 00:00:00', 102)) AND
(dbo.Tbl_FamiliesProgress.FamiliesAction = N'panel scheduled for:')) AS 'July 2014',
(SELECT COUNT(Tbl_FamiliesProgress_2.FamiliesID) AS [Aug 2014]
FROM dbo.Tbl_FamiliesProgress AS Tbl_FamiliesProgress_2 INNER JOIN
dbo.Tbl_Families AS Tbl_Families_2 ON Tbl_FamiliesProgress_2.FamiliesID = Tbl_Families_2.FamiliesID
WHERE (Tbl_Families_2.Withdrawn IS NULL) AND (Tbl_Families_2.DateApproved IS NULL) AND (Tbl_FamiliesProgress_2.FamiliesActionDate BETWEEN
CONVERT(DATETIME, '2014-08-01 00:00:00', 102) AND CONVERT(DATETIME, '2014-08-31 00:00:00', 102)) AND
(Tbl_FamiliesProgress_2.FamiliesAction = N'panel scheduled for:')) AS 'Aug 2014'
FROM dbo.Tbl_FamiliesProgress AS Tbl_FamiliesProgress_1 INNER JOIN
dbo.Tbl_Families AS Tbl_Families_1 ON Tbl_FamiliesProgress_1.FamiliesID = Tbl_Families_1.FamiliesID
WHERE (Tbl_Families_1.Withdrawn IS NULL) AND (Tbl_Families_1.DateApproved IS NULL) AND (Tbl_FamiliesProgress_1.FamiliesActionDate BETWEEN CONVERT(DATETIME,
'2014-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2014-07-31 00:00:00', 102)) AND (Tbl_FamiliesProgress_1.FamiliesAction = N'panel scheduled for:')
GROUP BY Tbl_FamiliesProgress_1.FamiliesAction, Tbl_FamiliesProgress_1.FamiliesActionDate, Tbl_FamiliesProgress_1.TeamAgencyCode, Tbl_Families_1.DateApproved,
Tbl_Families_1.Withdrawn

Jim

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-11 : 09:28:09
Try this:

I used ISNULL instead of COALESCE though the effect is the same.

Also note that I reformatted your query for easier reading. I used Poor Man's T-SQL Formatter (free extension to SSMS AND VS) but you can use whatever you like:


SELECT Tbl_FamiliesProgress_1.TeamAgencyCode
,(
SELECT ISNULL(COUNT(dbo.Tbl_FamiliesProgress.FamiliesID),0) AS [July 2014]
FROM dbo.Tbl_FamiliesProgress
INNER JOIN dbo.Tbl_Families ON dbo.Tbl_FamiliesProgress.FamiliesID = dbo.Tbl_Families.FamiliesID
WHERE (dbo.Tbl_Families.Withdrawn IS NULL)
AND (dbo.Tbl_Families.DateApproved IS NULL)
AND (
dbo.Tbl_FamiliesProgress.FamiliesActionDate BETWEEN CONVERT(DATETIME, '2014-07-01 00:00:00', 102)
AND CONVERT(DATETIME, '2014-07-31 00:00:00', 102)
)
AND (dbo.Tbl_FamiliesProgress.FamiliesAction = N'panel scheduled for:')
) AS 'July 2014'
,(
SELECT ISNULL(COUNT(Tbl_FamiliesProgress_2.FamiliesID),0) AS [Aug 2014]
FROM dbo.Tbl_FamiliesProgress AS Tbl_FamiliesProgress_2
INNER JOIN dbo.Tbl_Families AS Tbl_Families_2 ON Tbl_FamiliesProgress_2.FamiliesID = Tbl_Families_2.FamiliesID
WHERE (Tbl_Families_2.Withdrawn IS NULL)
AND (Tbl_Families_2.DateApproved IS NULL)
AND (
Tbl_FamiliesProgress_2.FamiliesActionDate BETWEEN CONVERT(DATETIME, '2014-08-01 00:00:00', 102)
AND CONVERT(DATETIME, '2014-08-31 00:00:00', 102)
)
AND (Tbl_FamiliesProgress_2.FamiliesAction = N'panel scheduled for:')
) AS 'Aug 2014'
FROM dbo.Tbl_FamiliesProgress AS Tbl_FamiliesProgress_1
INNER JOIN dbo.Tbl_Families AS Tbl_Families_1 ON Tbl_FamiliesProgress_1.FamiliesID = Tbl_Families_1.FamiliesID
WHERE (Tbl_Families_1.Withdrawn IS NULL)
AND (Tbl_Families_1.DateApproved IS NULL)
AND (
Tbl_FamiliesProgress_1.FamiliesActionDate BETWEEN CONVERT(DATETIME, '2014-07-01 00:00:00', 102)
AND CONVERT(DATETIME, '2014-07-31 00:00:00', 102)
)
AND (Tbl_FamiliesProgress_1.FamiliesAction = N'panel scheduled for:')
GROUP BY Tbl_FamiliesProgress_1.FamiliesAction
,Tbl_FamiliesProgress_1.FamiliesActionDate
,Tbl_FamiliesProgress_1.TeamAgencyCode
,Tbl_Families_1.DateApproved
,Tbl_Families_1.Withdrawn

Go to Top of Page

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-09-25 : 04:05:34
Apologies, I didn't see you had replied. I have tried what you did for me and it works brilliantly. Thank you very much for this :)

Jim

Jim
Go to Top of Page
   

- Advertisement -