如何将requd列添加到我现有的表中...

问题描述

我的桌子是







如何将requddue列作为...

my table is



How to get the requddue column as...

id   FeeID    defineddue     ActualDue   reqdDue



1     69        1              3           2  (as 3 doesnot exist in defineddue
                                               (for Feeid 69) it should take 2)

2     69        2              3           2

3     65        1              2           2

4     65        2              2           2  (as 2 exists for 65 in defined due)

4     65        3              2           2

5     70        1              3           1  ( as 3 and 2 does not exist in
                                                defined due for 70)

 there are only 3 possible values for duedays 1,2,3 respectively ..

i guues we can achieve this with case statement..

推荐答案

WITH CTE1 (feeid, maxdefineddue, actualdue)
AS
(
    SELECT feeid, MAX(defineddue) AS maxdefineddue, actualdue
    FROM table1 GROUP BY feeid, actualdue
)
SELECT t.id, t.feeid, t.defineddue, t.actualdue,
CASE WHEN c.maxdefineddue > c.actualdue THEN c.actualdue ELSE c.maxdefineddue END AS reqddue
FROM CTE1 c JOIN table1 t ON c.feeid = t.feeid ORDER BY t.id

阅读更多关于 []

这篇关于如何将requd列添加到我现有的表中...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！