CODEWITHSUNDEEP
sql-serversql-server-2008c#-4.0
Lord Relix
Lord Relix

Reputation: 942

Pulling off a SQL "IF/Else" in complex query

I need to make a statement for some accounts that pull data off from a database I didn't create. Now this specific table is a bit weird and I am having trouble pulling it off. Basically here's the query at the moment...

string query = "SELECT Date, TransCode, Amount, BalanceDebito, BalanceCredito FROM ledger WHERE (DR_Socio = @SocioNum) OR (CR_SOCIO = @SocioNum) ORDER BY Date DESC";

I have been reading about TRANSACT and other things but I can't manage to pull out a query that determines that if DR_Socio is the same as @SocioNum then BalanceDebito becomes a Balance column, or else if CR_SOCIO is the same as @SocioNum then a column called BalanceCredit becomes the Balance Column and if both CR_Socio and DR_Socio columns are the same as @SocioNum, then it should not pass any value whatsoever. Basically....

If DR_Socio = @SocioNum THEN
BalanceDebito as Balance 
Else if CR_Socio @SocioNum then
BalanceCredito as Balance
Else If CrSocio and Dr_Socio = @SocioNum then
Do Nothing
End If

That's the gist of it. I need to turn that into a SQL query. BalanceDebito and BalanceCredito are two different columns that I need them to become as one for reporting purposes, as I am creating a PDF (God Bless iTextSharp) but I can't have two columns ofr BalanceDebito and BalanceCredito. Is this possible? I am pretty stuck and don't know how to proceed. Help would be really appreciated. I am programming in C#, ASP.NET, MVC, using the iTextSharp dlls for PDF creating purposes (and that works wonderfully), and SQL Server 2008 for the database.

Upvotes: 0

Views: 65

Answers (2)

Vlad G.
Vlad G.

Reputation: 2147

A CASE statement should do what you're looking for:

SELECT

CASE 
    WHEN Cr_Socio = @SocioNum AND Dr_Socio = @SocioNum THEN NULL
    WHEN DR_Socio = @SocioNum THEN BalanceDebito 
    WHEN CR_Socio = @SocioNum THEN BalanceCredito 
END AS Balance 

FROM ...

Upvotes: 1

Lamak
Lamak

Reputation: 70658

This query should give you what you want:

SELECT  Date, 
        TransCode, 
        Amount, 
        CASE WHEN DR_Socio = @SocioNum THEN BalanceDebito
        WHEN CR_SOCIO = @SocioNum THEN BalanceCredito END Balance
FROM ledger 
WHERE (DR_Socio = @SocioNum OR CR_SOCIO = @SocioNum)
AND DR_Socio <> CR_SOCIO
ORDER BY [Date] DESC

Upvotes: 1

Related Questions