Stuck with CASE statement in SQL

Question

0.00/5 (No votes)

See more:

I am stuck with CASE statement in SQL in select command
can someone please convert following IF condtion into SQL case statement thank you.

SQL

SELECT [Column1]
,SUM(IF A=0
	BEGIN
	IF B=1 OR B=2
		BEGIN
		[column0]
		END
	ELSE 
		BEGIN
		NULL
		END
	END
ELSE IF A=1
	BEGIN
	IF B=1
		BEGIN
		[column0]
		END
	ELSE 
		BEGIN
		NULL
		END
	END
)AS Alias_name
FROM dbo.Table
GROUP BY [column1]

Posted 10-Nov-13 12:05pm

MAbubakar.Riaz

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rajat-Indiandotnet · Accepted Answer · 2013-11-10T15:10:00

Solution 1

Hi ,
You can write below lines hope this will work for you

SQL

SELECT [Column1],
 SUM (CASE WHEN A=0 AND B=2 THEN [Column0]
           WHEN A=0 AND B=1 THEN [Column0]
		   WHEN A=1 AND B=1 THEN [Column0]
		   ELSE NULL
	  END) AS AliasName
FROM 
FROM dbo.Table
GROUP BY [column1]

Posted 10-Nov-13 15:10pm

Rajat-Indiandotnet

ArunRajendra · Accepted Answer · 2013-11-10T16:22:00

Solution 2

A modified version for solution posted by Rajat-Indiandotnet. You need to return 0 in else because if there is one record returns NULL then the final result will be NULL. For ex. 1+4+NULL = NULL and NOT 5.

SQL

SELECT [Column1],
 SUM (CASE WHEN A=0 AND (B=2 or B=1)THEN [Column0]
           WHEN A=1 AND B=1 THEN [Column0]
           ELSE 0
      END) AS AliasName
FROM dbo.Table1
GROUP BY [column1]

Posted 10-Nov-13 16:22pm

ArunRajendra

Comments

MAbubakar.Riaz 12-Nov-13 11:52am

Thank you ArunRajendra, i had it figured it out the same solution, my orginal query was having subquries thats y i was stuck.... as i dont know case statement that much so i did it with Plus sign. now m going to convert it into your solution. Thank you

select id_CUST ,SUM(CASE WHEN DATEDIFF(DD,(SELECT MAX(SYSDTM) FROM sixty.PCI_CER.DBO.CERAM2),GETDATE())=1 AND DAY(payment_date)=DAY(GETDATE()) OR DAY(payment_date)=DAY(GETDATE()-1) THEN [amt_paid] ELSE 0 END)
+ SUM(CASE WHEN DATEDIFF(DD,(SELECT MAX(SYSDTM) FROM sixty.PCI_CER.DBO.CERAM2),GETDATE())=0 AND DAY(payment_date)=DAY(GETDATE()) THEN [amt_paid] ELSE 0 END) AS PAID_TODAY