CODEWITHSUNDEEP
sql
Pierre Manson
Pierre Manson

Reputation: 13

sql query result with same data

Morning Guys, I have written a SQL code. 

    Select if(Tax = 0,amount,0.00) as zerotax,  
    if(Date < '2018-04-01',amount,0.00) as xivVat,  
    if(Date >= '2018-04-01',amount,0.00) as xvVat,  
    account, reference, paiddate, rep, amount, amount_due, code, date into 
    "Memory\Temp"  
from debttran    
where code in ('IN','CN') and (PaidDate Between '2018-01-13' and '2018-08-13')
order by PaidDate ascending

Basically what the code is doing is to get data from invoices that does not have any tax, then get data from invoices that were invoiced before the 1st of April due to vat changes and then those that were invoiced on the 1st of April and afterward.

when I run the code it works perfect but the only problem is when there is on zerotax column amount if will also show either on the xixVat or xvVat.

How can I write the code to basically check if it is the same and minus it?

SQL RESULT:

SQL RESULT

Upvotes: 1

Views: 38

Answers (2)

Gil Allen
Gil Allen

Reputation: 1237

Not sure I fully understand what logic are you trying to apply, but you can use case as follow:

 Select 
     **case 
         when  if(Date < '2018-04-01',amount,0.00) < if(Date >= '2018-04-01',amount,0.00)
         then if(Date < '2018-04-01',amount,0.00) --choose what to show in this case, it can be the difference between the 2 columns
         else if(Date >= '2018-04-01',amount,0.00) --Same is here
     end as FinalVat**,
    if(Date < '2018-04-01',amount,0.00) as xivVat,  
    if(Date >= '2018-04-01',amount,0.00) as xvVat
from

I'm not sure what you want to show in each case.

Upvotes: 0

Nick
Nick

Reputation: 147206

I think you can just change your IF statements:

if(Date < '2018-04-01' AND Tax > 0,amount,0.00) as xivVat,  
if(Date >= '2018-04-01' AND Tax > 0,amount,0.00) as xvVat,

This will prevent those fields showing a value when there was no tax paid.

Upvotes: 1

Related Questions