问题描述:

Not sure how to show a results chart of what I want, but see below what I'm trying to do. I'm trying to create the rcnew column to take where an rc is in '098000' or '999998' and change that to the rc from the closest previous paydate where the rc is not in '098000' or '999998'. As you can see, my code has where paydate is equal, that's because I can't figure out how to go about this. If there is no equal paydate, I want to take the rpevious one. The NULL in the results below should be 200000.

ern rc paydate rcnew

123 098000 20151221 NULL

123 200000 20151214 200000

456 098000 20151221 200000

456 200000 20151221 200000

SELECT

ern, rc, paydate,

CASE WHEN fss1.rc in ('098000', '999998')

THEN (SELECT TOP 1 rc

FROM [FIN_DataMart].[dbo].[FSSpaydetl] fss

WHERE fss.ern = fss1.ern

AND rc not in ('098000', '999998')

AND fss.paydate = fss1.paydate

ORDER BY paydate DESC)

ELSE fss1.rc

END rcnew

FROM [FIN_DataMart].[dbo].[FSSpaydetl] fss1

GROUP BY ern, rc, paydate

ORDER BY ern, paydate desc

网友答案:

lag() will not work if you have multiple special values in a row. An alternative is outer apply:

select fss.ern, rss.rc, fss.paydate,
       (case when fss.rc in ('098000', '999998') then fss2.rc else fss.rc end)
from FIN_DataMart].[dbo].[FSSpaydetl] fss outer apply
     (select top 1 t2.*
      from FIN_DataMart].[dbo].[FSSpaydetl] fss fss2
      where fss2.paydate < fss2.paydate and
            fss2.ern = fss.ern and
            fss2.rc not in ('098000', '999998')
      order by fss2.paydate desc
     ) fss2;
网友答案:

You can use the LAG Function, if you meant "rc from the closest previous paydate" but as @GordonLinoff appropriately devised it looks like you are actually looking for the "rc from the closest previous paydate that is not '098000', '999998' in which case this solution will not work.

DECLARE @Table AS TABLE (enc INT, rc CHAR(6), paydate DATE)
INSERT INTO @Table (enc, rc, paydate)
VALUES (123,'098000','20151221')
,(123,'200000','20151214')
,(456,'098000','20151221')
,(456,'200000','20151221')

SELECT
    *
    ,LagResult = LAG(rc,1,0) OVER (ORDER BY paydate)
    ,rcnew = CASE
       WHEN rc IN ('098000','999998') THEN LAG(rc,1,0) OVER (ORDER BY paydate)
       ELSE rc
    END
FROM
    @Table
ORDER BY
    paydate
相关阅读:
Top