Select last non null value in field

  • Marcus Farrugia

    SSCarpal Tunnel

    Points: 4718

    Hi I am struggling with how to apply this.  I want to get the status of a rental unit to populate a table showing the last status on a rental unit by month.  A range between an event status can span several months, ie, a unit is rented for a year.  I am trying to show a historical monthly view of unit status based on the max event of the historical month or the last non null status of the previous month that had a status change.  I've tried applying a max window function with the rows preceding unbounded hint unsuccessfully however because I am right outer joining the dates from a it doesn't seem to apply the partition range.  Any suggestions or help would be appreciated.

    IF OBJECT_ID('TempDB..#tmpFUnit','U') IS NOT NULL

    DROP TABLE #tmpFUnit

    --===== Create the test table with

    CREATE TABLE #tmpFUnit

    (

    rptMth varchar(6),

    Buiding varchar(4),

    Unit varchar(4),

    uStatus varchar(15)

    )

    insert into #tmpFUnit

    SELECT '201809','1820','0203','Occupied' UNION ALL

    SELECT '201810','','','' UNION ALL

    SELECT '201811','','','' UNION ALL

    SELECT '201812','1820','0203','Vacant' UNION ALL

    SELECT '201901','','','' UNION ALL

    SELECT '201902','','','' UNION ALL

    SELECT '201903','','','Unrented' UNION ALL

    SELECT '201903','','','' UNION ALL

    SELECT '201903','','','' UNION ALL

    SELECT '201904','1820','0203','Occupied' UNION ALL

    SELECT '201905','',' ','' UNION ALL

    SELECT '201906','','',''

    select * from #tmpFUnit

  • DesNorton

    SSC-Insane

    Points: 22848

    So, based on your sample data, what is your expected result?

  • Marcus Farrugia

    SSCarpal Tunnel

    Points: 4718

    Hi an edit in my month values and a diagram of what I am trying to achieve.

    IF OBJECT_ID('TempDB..#tmpFUnit','U') IS NOT NULL

    DROP TABLE #tmpFUnit

    the test table with

    CREATE TABLE #tmpFUnit

    (rptMth varchar(6),Buiding varchar(4),Unit varchar(4),uStatus varchar(15))

    insert into #tmpFUnit

    SELECT '201809','1820','0203','Occupied' UNION ALL

    SELECT '201810','','','' UNION ALL

    SELECT '201811','','','' UNION ALL

    SELECT '201812','1820','0203','Vacant' UNION ALL

    SELECT '201901','','','' UNION ALL

    SELECT '201902','','','' UNION ALL

    SELECT '201903','','','Unrented' UNION ALL

    SELECT '201904','','','' UNION ALL

    SELECT '201905','','','' UNION ALL

    SELECT '201906','1820','0203','Occupied' UNION ALL

    SELECT '201907','',' ','' UNION ALL

    select * from #tmpFUnit

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88170

    You can use OUTER APPLY to get the value:

     Select f.rptMth
    , f.Buiding
    , f.Unit
    , uStatus = coalesce(nullif(f.uStatus, ''), x.uStatus)
    From #tmpFUnit f
    Outer Apply (Select Top 1
    *
    From #tmpFUnit tf
    Where tf.uStatus > ''
    And tf.rptMth < f.rptMth
    Order By
    tf.rptMth desc
    ) As x;

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Sumathi

    SSC Rookie

    Points: 28

    You can achieve this by using the following SQL:

    Select unit.rptMth as [Date],

    COALESCE(NULLIF(unit.Buiding,''),x.Buiding) as Building,

    COALESCE(NULLIF(unit.Unit,''),x.Unit) as Unit,

    NULLIF(unit.uStatus,'') as RentStatus,

    COALESCE(nullif(unit.uStatus, ''), x.uStatus) DesiredStatus

    From #tmpFUnit unit

    Outer Apply (Select Top 1 Buiding, Unit, uStatus

    From #tmpFUnit tempUnit

    Where tempUnit.uStatus > ''

    And tempUnit.rptMth < unit.rptMth

    Order By

    tempUnit.rptMth desc

    ) As x;

  • drew.allen

    SSC Guru

    Points: 76658

    Both Jeff and Sumathi's solutions require two scans of the table.  The following only requires one scan.

    SELECT 
    tfu.rptMth
    , SUBSTRING(MAX(tfu.rptMth + NULLIF(tfu.Buiding, '')) OVER(ORDER BY tfu.rptMth ROWS UNBOUNDED PRECEDING), 7, 15) AS UnitStatus
    , SUBSTRING(MAX(tfu.rptMth + NULLIF(tfu.Unit, '')) OVER(ORDER BY tfu.rptMth ROWS UNBOUNDED PRECEDING), 7, 15) AS UnitStatus
    , SUBSTRING(MAX(tfu.rptMth + NULLIF(tfu.uStatus, '')) OVER(ORDER BY tfu.rptMth ROWS UNBOUNDED PRECEDING), 7, 15) AS UnitStatus
    FROM #tmpFUnit AS tfu;

    The big problem here is that you have blank values for some of the buildings and units.  As soon as you add a second unit and/or building, you are going to have problems with any of these approaches.

    The following is more complicated, but should work for multiple buildings/units.  You may need to add more rows to the Tally CTE if the status doesn't change in more than 12 months.

    WITH Tally AS
    (
    SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) - 1 AS n
    FROM ( VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0) ) t(n)
    )
    , unitRanges AS
    (
    SELECT
    tfu.rptMth
    , tfu.Buiding
    , tfu.Unit
    , tfu.uStatus
    , d.rptDt AS StatusBegin
    , LEAD(d.rptDt, 1, DATEADD(MONTH, 1, d.rptDt)) OVER(PARTITION BY tfu.Buiding, tfu.Unit ORDER BY tfu.rptMth) AS StatusEnd
    FROM #tmpFUnit AS tfu
    CROSS APPLY ( VALUES(CAST(tfu.rptMth + '01' AS DATE) ) ) d(rptDt)
    WHERE tfu.Buiding > ''
    )
    SELECT
    CONVERT(CHAR(6), DATEADD(MONTH, n, u.StatusBegin), 112) AS rptMth
    , u.Buiding
    , u.Unit
    , u.uStatus
    FROM unitRanges AS u
    INNER JOIN Tally t
    ON t.n < DATEDIFF(MONTH, u.StatusBegin, u.StatusEnd);

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Marcus Farrugia

    SSCarpal Tunnel

    Points: 4718

    Thank you everyone for answers.  All work, and answer my question.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply