CODEWITHSUNDEEP
sqlsql-server
Sabyasachi Mishra
Sabyasachi Mishra

Reputation: 1749

How to use dynamic if condition in sql

I have a SQL query as follows

select TransactionMode as [Modes],
TransactionCode as [Codes], 
TransactionAmount as [Amount],SubTotal,Total 
from TransactionDetails where CurrentTime> CAST(GETDATE()as date)

It gives o/p as follows

  Modes        Codes     Amount     SubTotal      Total 
  CARDS        ICICI      12         13.18        13.18
  CARDS        ICICI      200        219.7        219.7
  CARDS        ICICI      500        549.25       549.25
  BUY          COD        7000       42.898       38.67
  CARDS        SBI        400        439.4        439.4

I want to calculate commission on the basis of TransactionMode columns.I tried my query as follows

Declare @TransactionMode varchar(250);
set @TransactionMode='select TransactionMode from TransactionDetails'

IF @TransactionMode = 'CARDS'
    select transactioncode as [Type], count(TransactionCode) as [No of Trans], SUM(Total) as [Amount],
ABS(SUM(Subtotal-TransactionAmount)) as [Comission] from dbo.TransactionDetails  where CurrentTime> CAST(GETDATE()as date) and
     Status='Active' group by transactioncode

ELSE
   select transactioncode as [Type], count(TransactionCode) as [No of Trans], SUM(Total) as [Amount],
ABS(SUM(Subtotal-Total)) as [Comission] from dbo.TransactionDetails  
   where CurrentTime> CAST(GETDATE()as date)
    and Status='Active' group by transactioncode

I am expecting o/p as 

Type  No Of Trans   Amount    Comission
COD       1         38.67      4.228
ICICI     3         782.13     70.13
SBI       1         439.4      39.4

Instead I am getting 

Type  No Of Trans   Amount    Comission
COD       1         38.67      4.228
ICICI     3         782.13     0
SBI       1         439.4      0

Where I am wrong?

Upvotes: 0

Views: 64

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Here is your error: You fill the variable @TransactionMode with the string 'select TransactionMode from TransactionDetails'. This will never equal 'CARDS', so you always execute the ELSE branch.

Instead look at a record's transaction mode:

select 
  transactioncode as [Type], 
  count(transactioncode) as [No of Trans], 
  sum(total) as [Amount],
  abs(sum(case when transactionmode = 'cards' then subtotal - transactionamount 
                                              else subtotal - total end)) as [Comission] 
from dbo.transactiondetails  
where currenttime > cast(getdate() as date) 
and status = 'active' 
group by transactioncode;

(The formulas abs(sum(a-b)) seem strange, by the way. Shouldn't it matter whether a result is positive or negative?)

Upvotes: 1

Related Questions