SQL Server How to store multiple rows of a table in different variables using the stored procedure

r1zhe5dt 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(119)

I am creating a stored procedure in SQL Server. In it, I need to multiply different rows of a table and I need to store the results in different variables.

For example, let's suppose I have this table:

| Column A | Column B | Column C |
   | -------- | -------- |----------| 
   | Cell 1   | Cell 2   | Cell 3   |
   | Cell 4   | Cell 5   | Cell 6   |
   | Cell 7   | Cell 8   | Cell 9   |

The calculation will look like this

var1 = (Cell1 * Cell8) - Cell2/109
  var2 = (Cell5 * Cell7) - Cell1
  var3 = (Cell2 * Cell3) - Cell9 * Cell3

Helpful, if anyone has idea how a solution for this problem can be implemented.

Edit: More Clarification added.

I need to do a complex tax calculation which is currently done manually via excel. Below is how the tax table looks like and this table will be used for the calculation of tax.

| N (lower range) | N (Higher Range) | Rate |
   | ----------------| ---------------- |------| 
   |       0         |  20000           |  0   |
   |     20001       |  80000           | .30  |
   |     80001       |  above           | .45  |

Here N Stands for Salary

The calculation to calculate the tax is as follows:

If NetSalary < 20000 then
tax=0
If NetSalary > 20001 and NetSalary < 80000 then
tax= ( NetSalary*26 - 20000 * .30 ) / 26
If NetSalary > 80001 
tax = ( NetSalary*26 - ( ( 20000 * .45 ) + ( (80000 -20000) * ( .45 - .30) ) ) ) /26

Here 26 is a constant value. All other numerical values are populated from the tax table. Below sample shows how this calculation looks in excel.

|   NL     |    NH    | Rate    |
   | -------- | -------- |---------| 
   |   NL1    |    NH1   | Rate1   |
   |   NL2    |    NH2   | Rate2   |
   |   NL3    |    NH3   | Rate3   |


 If NetSalary < NH1 then
    tax=0
    If NetSalary > NL2 and NetSalary < NH2 then
    tax= ( NetSalary*26 - NH1 * Rate2 ) / 26
    If NetSalary > 80001 
    tax = ( NetSalary*26 - ( ( NH1 * Rate3 ) + ( (NH2 - NH1) * ( Rate3 - Rate2) ) ) ) /26

As it is evident that this is not straightforward tax calculation. It needs input from the previous cells as well. My idea is to put the calculation which is taking input from the tax table into a variable and then subtract it from NetSalary*26.

I need to understand if this can be implemented via the stored proc.

sql-server

来源：https://stackoverflow.com/questions/77096950/how-to-store-multiple-rows-of-a-table-in-different-variables-using-the-stored-pr

1条答案

按热度按时间

atmip9wb1#

For tax tier/bracket calculation logic I suggest you do the following:

Calculate "accumulated" tax amount for each bracket and store it in its own column. This amount is made up of tax amounts for all preceding tiers.
| NL (lower range) | NH (Higher Range) | Rate | Accumulated |
| ------------ | ------------ | ------------ | ------------ |
| 0 | 20000 | 0 | 0 |
| 20001 | 80000 | .30 | 0 |
| 80001 | above | .45 | 1800 |

I suggest using a very large number as the NH value for the top tier instead of "above" string e.g. 1000000000 (1 billion)

Then all you would need to calculate tax is a simple query

SELECT ((salary - NL) * Rate ) + Accumulated AS TaxAmount
FROM x
WHERE salary BETWEEN NL AND NH

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server How to store multiple rows of a table in different variables using the stored procedure

1条答案

相关问题

热门标签

最新问答