SQLTeam.com | Weblogs | Forums

T-sql 2012 sum of several fields

#1

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
0 Likes

#2
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 AS QC_15
0 Likes