这是我的输入表:帐户
accountnumber year quarter catgeory owedamt payedamt
101 2001 1 Tax 100.0 0.0
101 2001 1 Interest 100.0 30.0
101 2001 2 Interest 100.0 20.0
102 2002 1 Tax 100.0 100.0
输出
accountnumber year quarter Tax (outstanding) Interest (outstanding)
101 2001 1 100.0 70.0
101 2001 2 0.0 80.0
102 2002 1 0.0 0.0
在此基于欠款 - 付费 - 付费
注意:我们需要将这些列作为税收和利息,但是这些列中的金额应为未偿还,以公式上方的OS计算。
。注2:对于帐户:102,计算税(未偿还(
Owedamt -payedamt是100.0-100.0 = 0.0
,但我们在那年和季度没有任何利息类别,因此产出导致0.0
乍一看,您的问题看起来像是某种滚动的平均问题,但这实际上只是变相的枢轴查询。我们可以按帐号,年度和季度汇总,并使用所欠款的金额和已支付的金额计算每个期间的未偿税和利息。
SELECT
accountnumber,
year,
quarter,
MAX(CASE WHEN catgeory = 'Tax'
THEN owedamt - payedamt ELSE 0 END) AS [Tax (outstanding)],
MAX(CASE WHEN catgeory = 'Interest'
THEN owedamt - payedamt ELSE 0 END) AS [Interest (outstanding)]
FROM yourTable
GROUP BY
accountnumber,
year,
quarter
SELECT accountnumber ,
year ,
quarter ,
SUM(CASE WHEN catgeory = 'Tax' THEN owedamt - payedamt
ELSE 0
END) AS Tax ,
SUM(CASE WHEN catgeory = 'Interest' THEN owedamt - payedamt
ELSE 0
END) AS Interest
FROM mytable
GROUP BY accountnumber ,
year ,
quarter;
编辑:不确定您是否是指:
with summaryData (accountnumber ,
year ,
quarter, tax, interest) as
(
SELECT accountnumber ,
year ,
quarter ,
SUM(CASE WHEN catgeory = 'Tax' THEN owedamt - payedamt
ELSE 0
END),
SUM(CASE WHEN catgeory = 'Interest' THEN owedamt - payedamt
ELSE 0
END)
FROM mytable
GROUP BY accountnumber ,
year ,
quarter
)
select * from summaryData
where tax > 0 or interest > 0;