I'm working with invoice data that has multiple line items for each invoice. The issue is that the tax field has the entire invoice tax value on each line item as shown below:
| Invoice | Item | Amount | Tax |
|---------|------|--------|-----|
| 1 | A | 10 | 6 |
| 1 | B | 20 | 6 |
| 1 | C | 30 | 6 |
What I would want
| Invoice | Item | Amount | Tax |
|---------|------|--------|-----|
| 1 | A | 10 | 6 |
| 1 | B | 20 | 0 |
| 1 | C | 30 | 0 |
So essentially I want to keep the tax value on the row with the first occurrence of the invoice number and then every occurrence of that invoice number after I want to set the tax value to 0. Is this even possible in ETL or SQL.