How to use dynamic if condition in sql

Question

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?

your query seem to be ok and according to T-sql ,it is giving correct output.you must have over look something>if output is not correct then something wrong with your where condition.This is correct ABS(SUM(Subtotal-TransactionAmount)) as [Comission].OR SOMETHING wrong with the formula — KumarHarsh
– KumarHarsh, Commented Oct 12, 2015 at 6:24
I tried of using ABS(SUM(Subtotal-TransactionAmount)) as [Comission] and SUM(Subtotal-TransactionAmount) as [Comission] but getting the same result :( — Sabyasachi Mishra
– Sabyasachi Mishra, Commented Oct 12, 2015 at 6:29
set @TransactionMode='select TransactionMode from TransactionDetails' Little worried about this line as @TransactionMode will never be equals to CARDS and you will always execute else condition because 'select TransactionMode from TransactionDetails' is a string not a select statement also the subquery will return more than 1 value. — Suprabhat Biswal
– Suprabhat Biswal, Commented Oct 12, 2015 at 6:36
Suprabhat's comment is the answer actually. Beside that, your formulas ABS(SUM(a-b)) seem strange. This equals ABS(SUM(a) - SUM(b)), i.e. you subtract one sum from the other and then make this positive. Doesn't it matter whether the result is negative or positive??? — Thorsten Kettner
– Thorsten Kettner, Commented Oct 12, 2015 at 6:43

Thorsten Kettner · Accepted Answer · 2015-10-12 06:50:47Z

1

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?)

answered Oct 12, 2015 at 6:50

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KumarHarsh Over a year ago

Wow ! Well caught.But there was no error in query.My full attention was in query.

Collectives™ on Stack Overflow

How to use dynamic if condition in sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related