Hi Guys, I want to include an if statement to manipulate some of the VAT codes in my data and this is what i want to achieve:
If the DrugID start with 'PRIV' and the EnterDateTime < '01/01/2020' then it should return 0, if the InventoryTaxID is '21' and the EnterDateTime < '01/09/2020' it should return '23' (instead of '21'), else (in every other case) return the InventoryTaxID.
This is my if statement:
if(DrugID like 'PRIV*',if(EnterDateTime < '01/01/2020','0',if(InventoryTax='21',if(EnterDateTime < '01/09/2020','23',InventoryTax)))) as VAT2
The problem is that the Drugs with date < 01/09/2020 are not receiving the value 23 but show up blank as per below. How should i re-write the above please? Thanks!
| AccountNumber | EnterDateTime | DrugID | InventoryTax | Vat2 |
| 1 | 02/01/2019 11:02 | CARB9 | 21 | - |
| 2 | 02/01/2019 11:02 | LENO | 21 | - |
| 3 | 02/01/2019 11:02 | VORI4 | 21 | - |
