Select last non null value in field

Question

Post reply

Select last non null value in field

Marcus Farrugia

SSCarpal Tunnel

Points: 4718
More actions
November 11, 2019 at 5:32 pm
Go to Answer
#3696808

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

Sumathi

Valued Member

Points: 61
More actions
November 11, 2019 at 9:40 pm
Answer
#3696877

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;

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

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

DesNorton SSC-Insane Points: 24401 More actions · Answer 1

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

How to post data/code on a forum to get the best help.

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 2

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 SSC Guru Points: 90304 More actions · Answer 3

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;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

drew.allen SSC Guru Points: 76996 More actions · Answer 4

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 More actions · Answer 5

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