Click here to Skip to main content
15,920,005 members
Please Sign up or sign in to vote.
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

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]
 
Share this answer
 
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]
 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900