22,300
社区成员




create table #t
(id int,公司货物名称 varchar(30),采购价格 decimal(18,1),收货数量 int,单位 varchar(10),采购总额 decimal(18,1),汇率 int,货币名称 varchar(10),采购单号 varchar(20),增值税 decimal(18,2))
insert into #t
select 1,'后勤部门物资',16.6,4,'EACH',66.4,1,'人名币','C-2019-07-01-100001',3213 union all
select 2,'(194)小牛臀肉',75,300,'KG',22500,1,'人名币','C-2019-07-13-100004',3213 union all
select 3,'(国产) 平湖老鸡',16.6,220,'KG',3652,1,'人名币','C-2019-07-16-100006',3213
-- 分摊增值税
;with t as
(select 采购总额,
增值税,
bl=采购总额/(select sum(采购总额) from #t),
rn=row_number() over(order by id)
from #t)
update t
set t.增值税=case when t.rn=(select max(rn) from t)
then t.增值税-(select sum(u.bl*u.增值税)
from t u
where u.rn<t.rn)
else t.bl*t.增值税 end
from t
-- 结果
select * from #t
/*
id 公司货物名称 采购价格 收货数量 单位 采购总额 汇率 货币名称 采购单号 增值税
----------- ------------------------------ --------------------------------------- ----------- ---------- --------------------------------------- ----------- ---------- -------------------- ---------------------------------------
1 后勤部门物资 16.6 4 EACH 66.4 1 人名币 C-2019-07-01-100001 8.14
2 (194)小牛臀肉 75.0 300 KG 22500.0 1 人名币 C-2019-07-13-100004 2757.32
3 (国产) 平湖老鸡 16.6 220 KG 3652.0 1 人名币 C-2019-07-16-100006 447.54
(3 行受影响)
*/
-- 验算
select 总增值税=sum(增值税) from #t
/*
总增值税
---------------------------------------
3213.00
(1 行受影响)
*/
DECLARE @VAT DECIMAL(12,4)
SET @VAT=100
;WITH CTE
AS
(SELECT *,ROUND((PRICE/SUM(PRICE) OVER (PARTITION BY GETDATE()))*@VAT,2) AS APPORTION,
MAX(ID) OVER (PARTITION BY GETDATE()) AS MAX_ID FROM #T)
SELECT *,
CASE WHEN ID<MAX_ID THEN APPORTION ELSE @VAT-SUBTOTAL END
FROM CTE A
CROSS APPLY (SELECT SUM(APPORTION) AS SUBTOTAL FROM CTE WHERE ID<A.ID) AS B