CODEWITHSUNDEEP
sqlsql-servercase-statement
JB_Data_Analyst_KY
JB_Data_Analyst_KY

Reputation: 45

Case statement with hierarchy based on 1 field comparing to 2 others

I am trying to use one field called P.STS and combination of that and 2 more other fields to derive a pass/fail. So if they have PA in P.STS and N.TERM_DT is >=GETDATE() from the RELATION table then pass. At that point we would stop and go to the next record since the hierarchy says that if PA and there is something in the relation table not termed. If P.STS has NP or is null and there is something in the RELATION table not termed, then that would be a fail else pass if opposite. If there is not an entry in the AGGR table that is not termed out and P.STS is null or PA and there is nothing in the relation either then pass. Each ID can only have one pass or fail, not multiple as seen in the actual results below.

I've tried numerous combinations. I think the fact that I am comparing 2 fields to one field and there is some hierarchy is making it difficult for me to logically think about it.

Basic logic: 
If P.STS = PA and N.PFX IS NOT NULL then PASS END  (NO REASON TO GO BEYOND)
If P.STS IN ('NP,'') and N.PFX IS NULL then PASS END
If P.STS = PA and N.PFX IS NULL then pass FAIL
If P.STS IN ('NP,'') and N.PFX IS NOT NULL then FAIL END

 SELECT DISTINCT
    P.ID
   ,P.STS
   ,CASE WHEN (N.PFX IS NOT NULL and P.STS IN ('PA') AND N.TERM_DT >= GETDATE() 
    OR NP.PFX IS NULL and P.STS IN ('NP','') ) 
    OR (N.PFX IS NULL and P.STS IN ('NP','') AND N.TERM_DT >= GETDATE() 
    OR NP.PFX IS NOT NULL and  P.STS IN ('NP','')  
    THEN 'Pass' 
    ELSE 'Fail' 
    END AS FinalTest
FROM PROV P (NOLOCK)
   LEFT JOIN RELATION N (NOLOCK) on N.PRPR_ID = P.ID
   LEFT JOIN AGGR NP (NOLOCK) on NP.PRPR_ID = P.ID

WHERE P.TERM_DT >= GETDATE ()
   AND P.STS IN ('PA','NP','')
   AND N.TERM_DT >= GETDATE () OR NP.TERM_DT >= GETDATE () 


\begin{table}[]
\begin{tabular}{lllll}
ID & STS & FinalTest &  &  \\
1        & NP        & Pass      &  &  \\
2        & NP        & Pass      &  &  \\
3        & PA        & Fail      &  &  \\
3        & PA        & Pass      &  &  \\
4        & PA        & Fail      &  &  \\
4        & PA        & Pass      &  &  \\
5        & NP        & Pass      &  & 
\end{tabular}
\end{table}
Expected
\begin{table}[]
\begin{tabular}{lllll}
ID & STS & FinalTest &  &  \\
1  & NP        & Fail      &  &  \\
2  & NP        & Fail      &  &  \\
3  & PA        & Fail      &  &  \\
4  & PA        & Pass      &  &  \\
5  & NP        & Fail      &  &  \\
   &           &           &  & 
\end{tabular}
\end{table}

Upvotes: 0

Views: 1372

Answers (1)

TomC
TomC

Reputation: 2814

You have answered your own question in your Basic Logic. There is no need to add a whole ot of complexe OR clauses, just code with mutliple lines in your case statement, in the order you want to test.

Rewording your logic into sql would be:

case 
    when P.STS = PA and N.PFX IS NOT NULL then 'PASS' 
    when P.STS IN ('NP','') and N.PFX IS NULL then 'PASS' 
    when P.STS = PA and N.PFX IS NULL then 'FAIL'
    when P.STS IN ('NP','') and N.PFX IS NOT NULL then 'FAIL'
    else 'UNKNOWN'
end

I notice you havent added the date clauses in your basic logic, but just add them into the case statement as required.

Upvotes: 1

Related Questions