CODEWITHSUNDEEP
sqlsql-servert-sqlcase-when
Josh
Josh

Reputation: 47

Case statement is ignoring where clause

I am trying to create a SQL statement that returns multiple counts. The count below works as I expect, but the case statement is ignoring the where clause for my query.

I'm trying to get the total number of PacketId's that meet the where criteria. Then get a second total showing the sum of PacketId's that meet the where criteria and have a StatusId of 3.

*edit Table1 and Table2 both share PacketId as a foreign key.

Select 
Count(Distinct wpq.PacketId) AS Total,
SUM(Case When wpq.StatusId = 3 THEN 1 ELSE 0 END) as OtherCount
FROM [Table1] ppo JOIN [Table2] wpq ON ppo.PacketId = wpq.PacketId
WHERE wpq.CreateDate between  '11/1/2017' and '1/1/2018' and ppo.IsSelected = 1

Upvotes: 1

Views: 474

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

I suspect you may be getting a higher number than expected for the othercount but that may be due to the use of count(distinct...) which reduces the first column result, but not the second. Perhaps introducing a subquery to select only distinct values would help?

SELECT DISTINCT
      wpq.PacketId
    , wpq.StatusId
FROM [Table1] ppo
JOIN [Table2] wpq ON ppo.PacketId = wpq.PacketId
WHERE wpq.CreateDate BETWEEN '11/1/2017' AND '1/1/2018'
AND ppo.IsSelected = 1
;

then count from that, e.g:

SELECT
      COUNT(PacketId)                                 AS total
    , COUNT(CASE WHEN StatusId = 3 THEN StatusId END) AS othercount
    , SUM(CASE WHEN StatusId = 3 THEN 1 ELSE 0 END)   AS othersum
FROM (
      SELECT DISTINCT
            wpq.PacketId
          , wpq.StatusId
      FROM [Table1] ppo
      JOIN [Table2] wpq ON ppo.PacketId = wpq.PacketId
      WHERE wpq.CreateDate BETWEEN '11/1/2017' AND '1/1/2018'
      AND ppo.IsSelected = 1
      ) AS d
;

Note: the COUNT() function ignores nulls, so I have added an alternative calculation method to consider. I prefer to use COUNT() in such a query.

Also I would like to note that your use of what appears to be M/D/YYYY date literals is NOT safe. The safest date literal format in T-SQL is YYYYMMDD. Similarly using between is not best practice for date ranges and wpuld encourage you to use >= and < instead, like so:

SELECT
      COUNT(PacketId)                                 AS total
    , COUNT(CASE WHEN StatusId = 3 THEN StatusId END) AS othercount
    , SUM(CASE WHEN StatusId = 3 THEN 1 ELSE 0 END)   AS othersum
FROM (
      SELECT DISTINCT
            wpq.PacketId
          , wpq.StatusId
      FROM [Table1] ppo
      JOIN [Table2] wpq ON ppo.PacketId = wpq.PacketId
      WHERE wpq.CreateDate >= '20171101' AND wpq.CreateDate < '20180101'
      AND ppo.IsSelected = 1
      ) AS d
;

Note I'm not sure if you do want to include 1/1/2018, if you do then use < '20180102' instead

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would suggest that you use standard date formats. Most databases support YYYY-MM-DD:

SELECT COUNT(DISTINCT wpq.PacketId) AS Total,
       SUM(Case When wpq.StatusId = 3 THEN 1 ELSE 0 END) as OtherCount
FROM [Table1] ppo JOIN
     [Table2] wpq
     ON ppo.PacketId = wpq.PacketId
WHERE wpq.CreateDate >= '2017-11-01' AND
      wpq.CreateDate <= '2018-01-01' AND
      ppo.IsSelected = 1;

It is possible that the date comparisons are really being done as strings, so they do not do what you expect.

Upvotes: 0

Related Questions