t-sql 2012 sum of several fields

Question

t-sql 2012 sum of several fields

wendy elizabeth

SSCoach

Points: 16915
More actions
March 20, 2019 at 4:03 pm

#416890

In the t-sql 2012 orig sql listed below, I am trying to determine how to tell if at least 2 or more of the following values are not null:
The fields that I am looking for to be not null are the following:
S155.Student_PersonID IS NOT NULL,
S160.Student_PersonID IS NOT NULL,
S161.Student_PersonID IS NOT NULL,
S162.Student_PersonID IS NOT NULL
if there are 2 or more fields not null then the result is 1 otherwise the result is 0
Here is the existing line of code that needs to change:
CASE WHEN S155.Student_PersonID IS NOT NULL AND S160.Student_PersonID IS NOT NULL AND S161.Student_PersonID IS NOT NULL AND S162.Student_PersonID IS NOT NULL THEN 1 ELSE 0 END AS QC_15
Here is the orig sql (close to orig sql so it is not too long to look at):
; WITH Enrollment_Active AS ( SELECT * FROM Enrollment ENR WHERE        ENR.enddate IS NULL)
, Enrollment_Students AS
     (SELECT DISTINCT PersonID AS PersonID
      FROM            Enrollment_Active)
, Sped_Services AS (SELECT PER.PersonID AS Student_PersonID,    Program_Code,ROW_NUMBER() OVER (PARTITION BY PER.PersonID
      ORDER BY (SUM(ISNULL(CONVERT(INT, C1023.value), 0)) + SUM(ISNULL(CONVERT(INT, c1024.value), 0))) DESC) AS Sped_Max
FROM            Enrollment_Students PER JOIN
                         customStudent C1023 ON PER.personid = C1023.personID AND C1023.attributeID = 1023 LEFT JOIN
                        customStudent C1024 ON PER.personid = C1024.personID AND C1024.attributeID = 1024 AND C1023.date = C1024.date
GROUP BY PER.PersonID )
, Sped_155 AS
    ( SELECT DISTINCT Student_PersonID
      FROM            Sped_Services
      WHERE        Program_Code = '155' )
, Sped_160 AS
    (SELECT DISTINCT Student_PersonID
      FROM            Sped_Services
      WHERE        Program_Code = '160' )
    , Sped_161 AS
    (SELECT DISTINCT Student_PersonID
      FROM            Sped_Services
      WHERE        Program_Code = '161')
    , Sped_162 AS
    (SELECT DISTINCT Student_PersonID
      FROM            Sped_Services
      WHERE        Program_Code = '162')
   ,       , Final AS
    SELECT ENR.*
, CASE WHEN S155.Student_PersonID IS NOT NULL AND S160.Student_PersonID IS NOT NULL AND S161.Student_PersonID IS NOT NULL AND S162.Student_PersonID IS NOT NULL THEN 1 ELSE 0 END AS QC_15

      FROM            Enrollment_Active ENR INNER JOIN
                      Sped_155 S155 ON ENR.Student_PersonID = S155.Student_PersonID LEFT OUTER JOIN
                      Sped_160 S160 ON ENR.Student_PersonID = S160.Student_PersonID LEFT OUTER JOIN
                   Sped_162 S162 ON ENR.Student_PersonID = S162.Student_PersonID LEFT OUTER JOIN
                      Sped_161 S165 ON ENR.Student_PersonID = S161.Student_PersonID
      )
     SELECT * FROM Final

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 1

If you have 4 items, then to check at least 2 are not null you could do:
CASE WHEN (S155.Student_PersonID IS NOT NULL AND S160.Student_PersonID IS NOT NULL) OR (S155.Student_PersonID IS NOT NULL AND S161.Student_PersonID IS NOT NULL) OR (S155.Student_PersonID IS NOT NULL AND S162.Student_PersonID IS NOT NULL) OR (S160.Student_PersonID IS NOT NULL AND S161.Student_PersonID IS NOT NULL) OR (S160.Student_PersonID IS NOT NULL AND S162.Student_PersonID IS NOT NULL) OR (S161.Student_PersonID IS NOT NULL AND S162.Student_PersonID IS NOT NULL) THEN 1 ELSE 0 END AS QC_15

It is a bit long-winded and if you have more than about 4 items would become unmanageable.

So a better way to do it is to use a CROSS APPLY to select the count, something like:

CASE WHEN x.Count >= 2 THEN 1 ELSE 0 END AS QC_15 ... CROSS APPLY(SELECT COUNT(x.Id) Count FROM (VALUES (S155.Student_PersonID), (S160.Student_PersonID), (S162.Student_PersonID), (S161.Student_PersonID)) x(Id)) x

PS: I don't see how S155.Student_PersonID could be null as it's part of an inner join.

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 2

Another way to do it with a CASE statement is to look for when there are 3 or more columns with the value NULL as there are only 4 combinations for this.

CASE WHEN (S155.Student_PersonID IS NULL AND S160.Student_PersonID IS NULL AND S161.Student_PersonID IS NULL) OR (S155.Student_PersonID IS NULL AND S160.Student_PersonID IS NULL AND S162.Student_PersonID IS NULL) OR (S155.Student_PersonID IS NULL AND S161.Student_PersonID IS NULL AND S162.Student_PersonID IS NULL) OR (S160.Student_PersonID IS NULL AND S161.Student_PersonID IS NULL AND S162.Student_PersonID IS NULL) THEN 0 ELSE 1 END AS QC_15

ScottPletcher SSC Guru Points: 101115 More actions · Answer 3

I think it's more clear and more flexible to just test each and add up a "not null" count accordingly:

CASE WHEN CASE WHEN S155.Student_PersonID IS NULL THEN 0 ELSE 1 END + CASE WHEN S160.Student_PersonID IS NULL THEN 0 ELSE 1 END + CASE WHEN S161.Student_PersonID IS NULL THEN 0 ELSE 1 END + CASE WHEN S162.Student_PersonID IS NULL THEN 0 ELSE 1 END >= 2 THEN 1 ELSE 0 END

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 4

ScottPletcher - Thursday, March 21, 2019 7:50 AM
I think it's more clear and more flexible to just test each and add up a "not null" count accordingly:
CASE WHEN CASE WHEN S155.Student_PersonID IS NULL THEN 0 ELSE 1 END + CASE WHEN S160.Student_PersonID IS NULL THEN 0 ELSE 1 END + CASE WHEN S161.Student_PersonID IS NULL THEN 0 ELSE 1 END + CASE WHEN S162.Student_PersonID IS NULL THEN 0 ELSE 1 END >= 2 THEN 1 ELSE 0 END

Yes, nice one.

As SQL 2012 is being used IIF could be used instead of CASE, which is slightly more concise.
IIF((IIF(S155.Student_PersonID IS NULL, 0, 1) + IIF(S160.Student_PersonID IS NULL, 0, 1) + IIF(S161.Student_PersonID IS NULL, 0, 1) + IIF(S162.Student_PersonID IS NULL, 0, 1)) >= 2, 1, 0) AS QC_15

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

That query would benefit from a refactor - perhaps it's been worked over and extended one too many times. Something like this will work:

; WITH
Sped_Services AS (
SELECT
  PER.PersonID AS Student_PersonID,
  Program_Code
FROM (SELECT DISTINCT PersonID AS PersonID FROM Enrollment ENR WHERE ENR.enddate IS NULL) PER
JOIN customStudent C1023
  ON PER.personid = C1023.personID
  AND C1023.attributeID = 1023
LEFT JOIN customStudent C1024
  ON PER.personid = C1024.personID
  AND C1024.attributeID = 1024
  AND C1023.date = C1024.date
--GROUP BY PER.PersonID
)

SELECT
ENR.*,
CASE WHEN sped.cnt > 2 THEN 1 ELSE 0 END AS QC_15
FROM Enrollment ENR
CROSS APPLY (
SELECT cnt = COUNT(DISTINCT Student_PersonID)
FROM Sped_Services
WHERE Program_Code IN ('155','160','161','162')
) sped
WHERE ENR.enddate IS NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101115 More actions · Answer 6

Jonathan AC Roberts - Thursday, March 21, 2019 8:14 AM
ScottPletcher - Thursday, March 21, 2019 7:50 AM
I think it's more clear and more flexible to just test each and add up a "not null" count accordingly:
CASE WHEN CASE WHEN S155.Student_PersonID IS NULL THEN 0 ELSE 1 END + CASE WHEN S160.Student_PersonID IS NULL THEN 0 ELSE 1 END + CASE WHEN S161.Student_PersonID IS NULL THEN 0 ELSE 1 END + CASE WHEN S162.Student_PersonID IS NULL THEN 0 ELSE 1 END >= 2 THEN 1 ELSE 0 END
Yes, nice one.
As SQL 2012 is being used IIF could be used instead of CASE, which is slightly more concise.
IIF((IIF(S155.Student_PersonID IS NULL, 0, 1) + IIF(S160.Student_PersonID IS NULL, 0, 1) + IIF(S161.Student_PersonID IS NULL, 0, 1) + IIF(S162.Student_PersonID IS NULL, 0, 1)) >= 2, 1, 0) AS QC_15

IIF is not SQL though. I dislike this combining of multiple language structures into basically a "mega-language". That's why Oracle's PL is such a mess, it's a mishmash of every type of syntax.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 7

ScottPletcher - Thursday, March 21, 2019 9:56 AM
Jonathan AC Roberts - Thursday, March 21, 2019 8:14 AM
ScottPletcher - Thursday, March 21, 2019 7:50 AM
I think it's more clear and more flexible to just test each and add up a "not null" count accordingly:
CASE WHEN CASE WHEN S155.Student_PersonID IS NULL THEN 0 ELSE 1 END + CASE WHEN S160.Student_PersonID IS NULL THEN 0 ELSE 1 END + CASE WHEN S161.Student_PersonID IS NULL THEN 0 ELSE 1 END + CASE WHEN S162.Student_PersonID IS NULL THEN 0 ELSE 1 END >= 2 THEN 1 ELSE 0 END
Yes, nice one.
As SQL 2012 is being used IIF could be used instead of CASE, which is slightly more concise.
IIF((IIF(S155.Student_PersonID IS NULL, 0, 1) + IIF(S160.Student_PersonID IS NULL, 0, 1) + IIF(S161.Student_PersonID IS NULL, 0, 1) + IIF(S162.Student_PersonID IS NULL, 0, 1)) >= 2, 1, 0) AS QC_15
IIF is not SQL though. I dislike this combining of multiple language structures into basically a "mega-language". That's why Oracle's PL is such a mess, it's a mishmash of every type of syntax.

Agreed, Oracle's language is a bit of a mess.
I don't find IIF too bad if you structure and indent code well, sometimes CASE is a bit too verbose.
IIF is converted into a CASE by the compiler, just as CAST is converted into a CONVERT..