How to SUM the results of a multiple case statement

Transact-SQL
1

Good evening,

I am trying to find a solution for a query but I am really confused (as MS SQL newbie )!

I have a client table. The table has many values per client. Some of then are NULLs or incorrectly completed.
I wrote a case statement with multiple checks that return values based on 'problems'.

Example: ( limited cases )
SELECT ....
CASE WHEN MAX(CSSATState) = 2 THEN

								case when MIN(csatype)=0 and MAX(CLIENTCAT)= 0 AND MAX(birthdate) Is null then 'BIRTHDATE  ' ELSE '' END + '' +
								case when MIN(csatype)=0 and MAX(CLIENTCAT)= 0 AND LEN(MAX(ISNULL(AFM,'')))<>9  then 'AFM  ' ELSE '' END + '' +
								case when MIN(csatype)=0 and MAX(CLIENTCAT)= 0 AND MAX(CertNum) Is null then 'CERTIFICATION  ' ELSE '' END + '' +
								case when MIN(csatype)=0 and MAX(CLIENTCAT)= 0 AND MAX(SSN) Is null then 'SSN ' ELSE '' END 
							 ELSE '' END AS PROBLEMS

FROM CLIENT

and this query working! Result example of PROBLEMS collum is BIRTHDATE SSN for x client

Now I want one more collum that returns (if it is possible) a number that is the SUM result of numbers based on cases.
What i mean ...Example:
CASE WHEN MAX(CSSATState) = 2 THEN SUM (

								case when MIN(csatype)=0 and MAX(CLIENTCAT)= 0 AND MAX(birthdate) Is null then 10 ELSE 0 END +
								case when MIN(csatype)=0 and MAX(CLIENTCAT)= 0 AND LEN(MAX(ISNULL(AFM,'')))<>9  then 10 ELSE 0 END ' +
								case when MIN(csatype)=0 and MAX(CLIENTCAT)= 0 AND MAX(CertNum) Is null then 10 ELSE 0 END  +
								case when MIN(csatype)=0 and MAX(CLIENTCAT)= 0 AND MAX(SSN) Is null then 10 ELSE 0 END 
							 ELSE '' END )AS RATING_PROBLEMS

FROM CLIENT

and the results of this collum will be the sum of values based on cases ..For a client with only error MAX(CertNum) is null then the result is 10, for a client with 2 problems the result are 20 and so on.

Thanks a lot for your time

2

You don't need the "SUM(" after THEN, just do the totals in the CASE:

CASE WHEN MAX(CSSATState) = 2 THEN 
								case when MIN(csatype)=0 and MAX(CLIENTCAT)= 0 AND MAX(birthdate) Is null then 10 ELSE 0 END +
								case when MIN(csatype)=0 and MAX(CLIENTCAT)= 0 AND LEN(MAX(ISNULL(AFM,'')))<>9  then 10 ELSE 0 END +
								case when MIN(csatype)=0 and MAX(CLIENTCAT)= 0 AND MAX(CertNum) Is null then 10 ELSE 0 END  +
								case when MIN(csatype)=0 and MAX(CLIENTCAT)= 0 AND MAX(SSN) Is null then 10 ELSE 0 END 
							 ELSE 0 END AS RATING_PROBLEMS 
```
3

I cant believe that my problem is only that!! I was confused enough because the code i searched is a part of a big query and as newbie, i lost in code.

i tried quite a bit to find the answer on google but it was difficult even to find the right question to do the search (combine SUM, CASE and MAX).

Βy the way, let me ask you one more question about the post in the forum. When i tried to paste my code from Management Studio in the New Post Window, at first it didn't seem like code and after some random spaces I managed to has (for a part of my code) a view like in SSMS. obviously I'm doing something wrong.

Can you help me by telling me what I'm doing wrong, to make my future posts visually correct?

Thanks a lot (again) sir for your time and your help.

4

You're welcome for the help.

Sorry, I have similar issues with this forum too when posting. It has some odd quirks of how it interprets certain chars / sequences of chars.