Hey SQL Guru's
My new approach to working out NETSALES uses a VAT table rather than CASE statements and is based on a BETWEEN operator with an INNER JOIN to match transaction dates to the correct VATRATE.
It will be the first time I have used a BETWEEN operator with an INNER JOIN so I wanted to check if this approach has any hidden dangers?
VAT TABLE
StartDate EndDate VATRATE
1994-01-01 00:00:00 2008-11-30 00:00:00 1.175
2008-12-01 00:00:00 2009-12-31 00:00:00 1.15
2010-01-01 00:00:00 NULL 1.175
I have tested it and I am happy with the results but as I could not see much on Google etc I wanted to check with the experts just to reassure myself!
My new approach to working out NETSALES uses a VAT table rather than CASE statements and is based on a BETWEEN operator with an INNER JOIN to match transaction dates to the correct VATRATE.
It will be the first time I have used a BETWEEN operator with an INNER JOIN so I wanted to check if this approach has any hidden dangers?
VAT TABLE
StartDate EndDate VATRATE
1994-01-01 00:00:00 2008-11-30 00:00:00 1.175
2008-12-01 00:00:00 2009-12-31 00:00:00 1.15
2010-01-01 00:00:00 NULL 1.175
Code:
e.g.
SELECT
<COLUMNS>
FROM tbl_Sales_Staging s
[blue]INNER JOIN [tbl_VAT] v ON
s.DATE BETWEEN v.StartDate and v.EndDate [/blue]I have tested it and I am happy with the results but as I could not see much on Google etc I wanted to check with the experts just to reassure myself!
