CODEWITHSUNDEEP
sql
Kirankumar Gajanan Gawade
Kirankumar Gajanan Gawade

Reputation: 79

How to subtract one column and one alise in same table?

This is my query which I am executing in SQL DBX. But I want to subtract in this query which is not working. I want to subtract the actual_amount and Depri.

But in my query is not working:

SELECT 
   b.bank_v_id
   ,a.venumber
   ,b.recept_date
   ,b.dayly_amount
   ,b.actual_amount
   ,(DATEDIFF(b.recept_date,'2015-03-09')*b.dayly_amount) "Depri"
FROM 
   bank_vehicle_master a, tax_details b
WHERE 
   a.bank_v_id = b.bank_v_id
   AND a.del_rec = 0
   AND b.del_rec = 0
ORDER BY 
   a.bank_v_id

Output:

bank_v_id   venumber    recept_date      dayly_amount   actual_amount   Depri       
    3     MH 07 Q 1313  17-12-2014          300           30000        -24600       
    4     MH 07 Q 1414  16-12-2014          150           15000        -12450       
    5     MH 07 Q 1555  16-12-2014          160           16000        -13280

I want subtraction of two column in this above that is subtraction of actual_amount and Depri.

How can I write the query?

Upvotes: 0

Views: 128

Answers (2)

Mike D.
Mike D.

Reputation: 4104

Referring to the alias rather than rewriting the whole formula again is certainly good practice. Makes it much easier to maintain. In order to do that you need to move your subtraction to an outer-query otherwise you cannot refer to your alias in the SELECT clause of the query.

The other answer from NoDisplayName also makes a very good point about using an INNER JOIN rather than the old comma style joins.

So something like this:

SELECT bank_v_id,
    venumber,
    recept_date,
    dayly_amount,
    actual_amount
    Depri,
    actual_amount-Depri AS actualMinusDepri
FROM (
    SELECT b.bank_v_id,
        a.venumber,
        b.recept_date,
        b.dayly_amount,
        b.actual_amount,
        DATEDIFF(b.recept_date,'2015-03-09')*b.dayly_amount AS Depri
    FROM bank_vehicle_master a
    INNER JOIN tax_details b ON a.bank_v_id=b.bank_v_id
    WHERE a.del_rec=0 AND b.del_rec=0
) AS t
ORDER BY bank_v_id

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Directly use the DateDiff part in subtraction.

Also use proper INNER JOIN syntax instead of comma separated join which is not readable.

SELECT b.bank_v_id,
       a.venumber,
       b.recept_date,
       b.dayly_amount,
       b.actual_amount,
       ( Datediff(b.recept_date, '2015-03-09') * b.dayly_amount )                   Depri,
       b.actual_amount - ( Datediff(b.recept_date, '2015-03-09') * b.dayly_amount ) AS Subtraction
FROM   bank_vehicle_master a
       INNER JOIN tax_details b
               ON a.bank_v_id = b.bank_v_id
WHERE  a.del_rec = 0
       AND b.del_rec = 0
ORDER  BY a.bank_v_id

Upvotes: 1

Related Questions