asked on

Select last non null value in field

Hi I am struggling with how to apply this in MS SQL. 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 '201904','','','' UNION ALL
SELECT '201905','','','' UNION ALL
SELECT '201906','1820','0203','Occupied' UNION ALL
SELECT '201907]','',' ','' UNION ALL
SELECT '201906','','',''

select * from #tmpFUnit

Dustin Saunders

Maybe I'm not understanding correctly, what would the desired output look like from this table?

It sounds like you're looking for something like

SELECT MAX(CONVERT(DATE,rptMth+'01')) AS [rptMth],Buiding,Unit,uStatus 
FROM #tmpFUnit
WHERE (uStatus != '' AND uStatus IS NOT NULL)
GROUP BY Buiding,Unit,uStatus

Open in new window

But I'm not 100% sure if that's what you are asking?

Scarlett72

ASKER

Hi Dustin, thanks for taking the time to reply. I've tweaked my ddl to correct the months field and added an image of my desired output. The first 4 columns is what I currently have. Basically a right outer join from a calendar table yyyy-mm to a max unit status by month. but where month status doesn't change in a month and is null populate the last non null value from the last month with a status.

Dustin Saunders

If the building and unit are empty, how are you determining that all of the records belong to the same building and unit?

Dustin Saunders

Pending question above, this should generate the view you are looking for

select
	t.rptMth AS [Date],
	t.Buiding AS [Building],
	t.Unit AS [Unit],
	t.uStatus AS [RentStatus],
	t2.uStatus AS [DesiredRentStatus]
from #tmpFUnit t
CROSS APPLY (
	SELECT MAX(rptMth) AS [rptMth]
	FROM #tmpFUnit
	WHERE rptMth <= t.rptMth
	AND uStatus != ''
) x
INNER JOIN #tmpFUnit t2
	ON t2.rptMth = x.rptMth

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scarlett72

ASKER

Thanks Dustin, Sharath, I selected Sharath as my solution, as it worked as printed. Dustin, I think the grouping on the max needed tweaking in the cross apply when two event status's repeated. Thanks to both.