How do/should i use coalesce for this query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources