01.SET NOCOUNT ON;
02.DECLARE @TaxTable5 TABLE (IncomeBase NUMERIC(11, 2),
03. IncomeTop NUMERIC(11, 2),
04. TaxRate NUMERIC(5, 3),
05. PrevTop NUMERIC(11, 2),
06. CumulTax money
07. )
08.
09.DECLARE @TaxTable7 TABLE(IncomeBase NUMERIC(15, 2),
10. IncomeTop NUMERIC(15, 2),
11. TaxRate NUMERIC(5, 2),
12. PrevTop NUMERIC(15, 2),
13. CumulTax money
14. )
15.
16.INSERT INTO @TaxTable5 VALUES ( 0.00, 150000, 0.00, 0, 0),
17. ( 150000.01, 500000, 0.10, 150000, 0),
18. ( 500000.01, 1000000, 0.20, 500000, 35000),
19. (1000000.01, 4000000, 0.30, 1000000, 135000),
20. (4000000.01, 999999999, 0.37, 4000000, 1035000)
21.
22.INSERT INTO @TaxTable7 VALUES( 0.00, 150000, 0.00, 0, 0.00),
23. ( 150000.01, 300000, 0.05, 150000, 0.00),
24. ( 300000.01, 500000, 0.10, 300000, 7500.00),
25. ( 500000.01, 750000, 0.15, 500000, 27500.00),
26. ( 750000.01, 1000000, 0.20, 750000, 65000.00),
27. ( 1000000.01, 2000000, 0.25, 1000000, 115000.00),
28. ( 2000000.01, 4000000, 0.30, 2000000, 365000.00),
29. ( 4000000.01, 900000000, 0.35, 4000000, 965000.00),
30. (900000000.01, 1000000000, 0.35, 900000000, 34565000.00)
31.
32.DECLARE @Salary TABLE (EmpID int,
33. TotalYearlyIncome NUMERIC(12, 2)
34. )
35.INSERT INTO @Salary VALUES (1, -0.99),
36. (2, 149999.99),
37. (3, 300000.00),
38. (4, 3000000.00),
39. (5, 300000000.00),
40. (6, 600000000.00)
41.
42.SELECT *
43.FROM @Salary a
44.CROSS APPLY (
45. SELECT ((a.TotalYearlyIncome - b.PrevTop) * b.TaxRate) + b.CumulTax AS Tax FROM @TaxTable5 b
46. WHERE a.TotalYearlyIncome BETWEEN b.IncomeBase AND b.IncomeTop
47. ) z ORDER BY a.EmpID
48.
49.SELECT *
50.FROM @Salary a
51.OUTER APPLY (
52. SELECT ((a.TotalYearlyIncome - b.PrevTop) * b.TaxRate) + (b.CumulTax) AS Tax FROM @TaxTable7 b
53. WHERE a.TotalYearlyIncome BETWEEN b.IncomeBase AND b.IncomeTop
54. ) z ORDER BY a.EmpID
