CODEWITHSUNDEEP
sql-serverif-statementgroup-by
noobInTraining
noobInTraining

Reputation: 42

Where do I put the if statement?

I wrote this to create a summary of invoices for the past 30 days. While checking the invoices I notice I pulled a lot of nulls because most of the customers are tax exempt. I would like to put a IF statement (IF t.TAXABL_23 = 'N' then something to make the tax = 1... it gets kinda gray here) Where in this mess would the if statement go, or better yet is using an IF statement the logical way to do this. Thanks.

Select  c.ordnum_31 as "Sales Order Number",
    c.invce_31  as "Invoice Number",    
    c.custpo_31 as "Job Name",
    c.ordid_31  as "Other Job Name", 
    c.invdte_31 as "Invoice Date",
    cast (sum((1-(m.DSCRTE_23/100)) * (o.price_32 * o.shpqty_32)) as decimal    (8,2)) as "Net Amount",
    cast (c.frtamt_31 as decimal(8,2)) as Freight,
    cast( ((t.TAXRTE_25 * .01) * c.TAXTOT_31)  as decimal (8,2)) as Tax,
    cast (c.MSCAMT_31 as decimal(8,2)) as MISC,
    cast( round (sum((1-(m.DSCRTE_23/100)) * (o.price_32 * o.shpqty_32)) + c.frtamt_31 + c.MSCAMT_31 + ((t.TAXRTE_25 * .01) * c.TAXTOT_31),1 ) as decimal(8,2) ) as "Invoice Total" 

from Invoice_Master c
   left join Invoice_Detail o on c.ORDNUM_31 = o.ORDNUM_32
   left join Customer_Master m on c.CUSTID_31 = m.CUSTID_23
   left join Tax_master t on m.TXCDE1_23 = t.TAXCDE_25

where c.invdte_31 >= DATEADD(day,-30, getdate())

group by
   c.ORDNUM_31,
   c.CUSTID_31,
   c.INVCE_31,
   c.CUSTPO_31,
   c.ORDID_31,
   c.INVDTE_31,
   c.frtamt_31,
   c.taxtot_31,
   m.dscrte_23,
   c.MSCAMT_31,
   t.taxrte_25

order by "Invoice Number"

Upvotes: 0

Views: 75

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Use COALESCE to convert NULLS

 SELECT COALESCE(fieldWithNull, 'N');

Upvotes: 0

Heinzi
Heinzi

Reputation: 172270

You are probably looking for a CASE expression:

...
... as Freight,
CASE WHEN t.TAXABL_23 = 'N'
     THEN 0                  -- exempt from taxes
     ELSE ...calculate tax...
END as Tax,
... as MISC,
...

Otherwise, if you just want to convert NULLs to some value, you can use an ISNULL expression:

...
ISNULL(...calculate tax..., 0) AS tax   -- yields 0 when the result of the expression is NULL
...

Upvotes: 1

Related Questions