Как мне написать инструкцию sql для вычисления итогов на основе групп из другой таблицы?

ProgramBox

Как мне написать инструкцию sql для вычисления итогов на основе групп из другой таблицы?

Post author:admin
Запись опубликована:2 мая, 2023
Post category:Вопросы по программированию

#sql #sql-server #tsql

Вопрос:

Мне нужно вычислить процент от совокупного значения. Процентная ставка, применяемая к каждому значению в каждой строке, зависит от ставок, взятых из другой таблицы. Расчет процентной ставки должен выполняться многоуровневым образом, поскольку налог может рассчитываться с прибыли.

например: Заработная плата = 1000
600 * 10% [Первые 600 долларов, рассчитанные по более низкой налоговой ставке]
400 * 30% [Оставшаяся сумма, рассчитанная по более высокой налоговой ставке]

Итак, я пытался заставить это работать, но не могу разобраться. Администратор базы данных отсутствует, поэтому его передали мне. С большинством SQL у меня все в порядке, но я не уверен, как подойти к этой проблеме, или что мне следует искать в Google, поэтому прошу прощения за то, что это простой поиск, пожалуйста, просто направьте меня по URL, и я попробую разобраться с этим сам!

В любом случае, ниже приведен пример формата таблицы данных (#v) и пример таблицы диапазонов (#уровни), а также то, как я справлялся до сих пор. Мне нужен новый столбец, в котором содержится расчет ‘cval’ с правильными уровнями процентных ставок, как я объяснил выше.

Надеюсь, кто-нибудь сможет помочь или указать мне правильное направление! Спасибо, J.

 create table #v(
id nvarchar(50),
val money,
tid int
)

insert into #v values ('a',30,1)
insert into #v values ('b',50,1)
insert into #v values ('c',10,1)
insert into #v values ('d',30,1)
insert into #v values ('e',-80,1)


create table #tiers (
tid int,
threshold money,
amount money
)

insert into #tiers values (1,0,30)
insert into #tiers values (1,40,40)
insert into #tiers values (1,100,50)


select * from
(
select v1.id, v1.tid, v1.val,sum(v2.val) cval
from #v v1
inner join #v v2 on v1.id >= v2.id
group by v1.id, v1.val, v1.tid
) a
left join
(
       select a.tid, a.id, a.threshold [lower], b.threshold [upper] from
       (
               select rank() over (order by threshold) as id, tid, threshold, amount from #tiers
       ) a
       left join
       (
               select rank() over (order by threshold) as id, tid, threshold, amount from #tiers
       ) b on a.id = b.id-1
) b on (a.cval >= lower and a.cval < upper) or (a.cval >= lower and upper is null)

Ответ №1:

Если ваша фактическая логика содержит намного больше правил, чем эта, вам лучше написать это на процедурном языке, таком как PL / SQL или T-SQL, потому что есть вероятность, что .. другие приложения могут захотеть использовать эту логику, используя ..скажем .. *get_tax_for_pay (i_pay) * или что-то в этом роде.

Но если это все, что вам нужно, то приведенный ниже SQL должен быть достаточно хорош.

Тестировал это в Oracle, поскольку на данный момент у меня нет доступа к SQL server. Если у вас возникли какие-либо проблемы, напишите об этом в комментариях. Примечания в конце.

 create table gross_pay(
  pay number);

insert into gross_pay values (1523);
insert into gross_pay values (500);
insert into gross_pay values (5600);
insert into gross_pay values (3523);
commit;

create table tax_range(
  min_pay number,
  max_pay number,
  tax_percent number);

insert into tax_range values (1000, 2000, 10);
insert into tax_range values (2000, 3000, 20);
insert into tax_range values (3000, 4000, 30);
insert into tax_range values (4000, 100000, 35);
commit;

SQL> select * from gross_pay;

       PAY
----------
      1523
       500
      5600
      3523

SQL> select * from tax_range;

   MIN_PAY    MAX_PAY TAX_PERCENT
---------- ---------- -----------
      1000       2000          10
      2000       3000          20
      3000       4000          30
      4000     100000          35

select g.pay, t.min_pay, t.max_pay, t.tax_percent,
  (g.pay-t.min_pay) diff, (t.max_pay-t.min_pay) diff2,
  (case when g.pay > t.min_pay then
        least((t.max_pay-t.min_pay),(g.pay-t.min_pay))
        else 0
    end) Taxable
  from gross_pay g, tax_range t
  order by pay, min_pay
SQL> /

       PAY    MIN_PAY    MAX_PAY TAX_PERCENT       DIFF      DIFF2    TAXABLE
---------- ---------- ---------- ----------- ---------- ---------- ----------
       500       1000       2000          10       -500       1000          0
       500       2000       3000          20      -1500       1000          0
       500       3000       4000          30      -2500       1000          0
       500       4000     100000          35      -3500      96000          0
      1523       1000       2000          10        523       1000        523
      1523       2000       3000          20       -477       1000          0
      1523       3000       4000          30      -1477       1000          0
      1523       4000     100000          35      -2477      96000          0
      3523       1000       2000          10       2523       1000       1000
      3523       2000       3000          20       1523       1000       1000
      3523       3000       4000          30        523       1000        523

       PAY    MIN_PAY    MAX_PAY TAX_PERCENT       DIFF      DIFF2    TAXABLE
---------- ---------- ---------- ----------- ---------- ---------- ----------
      3523       4000     100000          35       -477      96000          0
      5600       1000       2000          10       4600       1000       1000
      5600       2000       3000          20       3600       1000       1000
      5600       3000       4000          30       2600       1000       1000
      5600       4000     100000          35       1600      96000       1600

 select pay, sum(tax) from (
 select pay, min_pay, max_pay, tax_percent, Taxable,
        (Taxable* tax_percent/100) tax from (
 select g.pay, t.min_pay, t.max_pay, t.tax_percent,
        (g.pay-t.min_pay) diff, (t.max_pay-t.min_pay) diff2,
        (case when g.pay > t.min_pay then
              least((t.max_pay-t.min_pay),(g.pay-t.min_pay))
              else 0
         end) Taxable
   from gross_pay g, tax_range t
   order by pay, min_pay

       PAY   SUM(TAX)
---------- ----------
      1523       52.3
      3523      456.9
       500          0
      5600       1160

Для вычисления…

Вас интересует налогообложение только тех сумм, в которых конкретная выплата превышает заданный порог для налоговой категории. Пример .. 1000 $ не будут облагаться налогом в налоговой категории 3-5 тыс.
В случаях, когда сумма превышает пороговое значение, вы будете взимать минимум из a) (max-min) в этом пороговом значении b) (pay-threshold minimum) таким образом. при оплате в 5500 долларов вы будете взимать только 1000 долларов в налоговой категории 1000-2000 долларов. при оплате 1200 долларов вы будете взимать только 200 долларов в налоговой категории 1000-2000 долларов.
Если у вас нет значений min и max в разных столбцах, вы можете использовать функции lead / lag или самосоединение, чтобы получить их оба в той же строке, что и в моей тестовой таблице. Если у вас нет максимального значения для последнего диапазона, используйте NVL или соответствующую функцию, чтобы присвоить действительно большое значение для определения диапазона. (или код для нулей :))

1. Спасибо за ваш ответ, но вместо этого я использовал ответ Томаса, поскольку он был ближе к моему исходному сообщению. Кроме того, я не смог протестировать ваш, поскольку MS Sql не поддерживает некоторые используемые вами синтаксисы.

2. Нет проблем. Я думаю, что я узнал пару вещей, пытаясь ответить и на этот вопрос … и у меня был доступ только к Oracle. Я согласен.. Сообщение Томаса, вероятно, было бы вам более полезным. Удачи!

Ответ №2:

Предполагая, что столбец суммы в таблице уровней должен быть налоговой ставкой, вы можете сделать что-то вроде:

 With VData As
    (
    Select V1.id, V1.val, V1.tid, Sum(V2.val) As CVal
    From #V As V1
        Join #V As V2
            On V2.id <= V1.id
    Group By V1.id, V1.val, V1.tid
    )
    , Tiers As
    (
    Select T1.tid
        , T1.Amount
        , T1.threshold As MinThreshold
        , Min(Coalesce(T2.threshold, 2147483647)) As MaxThreshold
    From #tiers As T1
        Left Join #tiers As T2
            On T2.threshold > T1.threshold
    Group By T1.tid, T1.Amount, T1.threshold
    )
Select V.id, V.val, V.tid, V.CVal
    , Sum(
        Case
        When CVal > T.MaxThreshold Then T.Amount / 100.00 * T.MaxThreshold
        When CVal >= T.MinThreshold Then T.Amount / 100.00 * (V.CVal - T.MinThreshold)
        End) As TotalTax
From VData As V
    Join Tiers As T
        On T.tid = V.tid
Group By V.id, V.val, V.tid, V.CVal