I have an SQL query that filters down purchase credit and invoices and removes any zero tax amounts, it has just come to light that there is some i need to include for me to get the correct figure. Luckily these few invoices and credit notes can be identified on there reference number, unfortunately i don not know how to write such a query. I am guessing it should be an
if statement. Any help would be much appreciated, query is below.
SELECT --T_PURCHASEINVOICE.C_ID, T_PURCHASEINVOICE.C_DATE, T_PURCHASEINVOICE.C_NUMBER, T_PURCHASEINVOICE.C_NETAMOUNT , T_PURCHASEINVOICE.C_GROSSAMOUNT, T_PURCHASEINVOICE.C_TAXAMOUNT , T_PURCHASEINVOICE.C_PERIOD, T_PURCHASEINVOICE.C_ALTERNATEREFERENCE , T_TAXRATE.C_CODE FROM T_PURCHASEINVOICE (NOLOCK) JOIN T_PURCHASEINVOICE_TAXLINE (NOLOCK) ON T_PURCHASEINVOICE.C_ID = T_PURCHASEINVOICE_TAXLINE.C__OWNER_ JOIN T_TAXRATE (NOLOCK) ON T_PURCHASEINVOICE_TAXLINE.C_TAXRATE = T_TAXRATE.C_ID WHERE T_TAXRATE.C_CODE = '01' AND T_PURCHASEINVOICE.C_ALTERNATEREFERENCE LIKE '%MRINV%' --AND T_PURCHASEINVOICE.C_TAXAMOUNT <> 0 UNION ALL SELECT --T_PURCHASECREDITNOTE.C_ID, T_PURCHASECREDITNOTE.C_DATE, T_PURCHASECREDITNOTE.C_NUMBER, case when T_PURCHASECREDITNOTE.C_NUMBER = 'PCN' THEN 1 ELSE -1 END * T_PURCHASECREDITNOTE.C_NETAMOUNT , case when T_PURCHASECREDITNOTE.C_NUMBER = 'PCN' THEN 1 ELSE -1 END * T_PURCHASECREDITNOTE.C_GROSSAMOUNT , case when T_PURCHASECREDITNOTE.C_NUMBER = 'PCN' THEN 1 ELSE -1 END * T_PURCHASECREDITNOTE.C_TAXAMOUNT , T_PURCHASECREDITNOTE.C_PERIOD, T_PURCHASECREDITNOTE.C_ALTERNATEREFERENCE, T_TAXRATE.C_CODE FROM T_PURCHASECREDITNOTE (NOLOCK) JOIN T_PURCHASECREDITNOTE_TAXLINE (NOLOCK) ON T_PURCHASECREDITNOTE.C_ID = T_PURCHASECREDITNOTE_TAXLINE.C__OWNER_ JOIN T_TAXRATE (NOLOCK) ON T_PURCHASECREDITNOTE_TAXLINE.C_TAXRATE = T_TAXRATE.C_ID WHERE T_TAXRATE.C_CODE = '01' AND T_PURCHASECREDITNOTE.C_ALTERNATEREFERENCE LIKE '%MRCR%' --AND T_PURCHASECREDITNOTE.C_TAXAMOUNT <> 0
As you can see i have commented out the
AND T_PURCHASECREDITNOTE.C_TAXAMOUNT <> 0 and
AND T_PURCHASEINVOICE.C_TAXAMOUNT <> 0 as that will remove every entry that has a zero tax amount.
The references i need to show are as follows
purchase credit: MRCR and
purchase invoice: MCINV. Below is what needs to be added to the other query, if
C_TAXAMOUNT is zero don’t show unless
C_ALTERNATIVEREFERENCE LIKE ‘%MRCR%’, that’s how i see it unfortunately haven’t got the skills to implement.
SQL works by filtering data, so the concept of
if statements doesn’t really apply here. Rather you’re using a condition to select what should be returned, which is what SQL’s
WHERE clause does; any rows matching the WHERE condition are returned, any which don’t aren’t.
You want those with zero tax
OR those with a specific reference (including if both are true; but OR covers that too).
So you can replace:
AND T_PURCHASEINVOICE.C_ALTERNATEREFERENCE LIKE '%MRINV%' --AND T_PURCHASEINVOICE.C_TAXAMOUNT <> 0
AND ( T_PURCHASEINVOICE.C_ALTERNATEREFERENCE LIKE '%MRINV%' OR T_PURCHASEINVOICE.C_TAXAMOUNT <> 0 )
Thus only if one or both of the conditions within the brackets returns true will that row be included in the results.