Get last non null value across rows/dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get last non null value across rows/dates

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2013-12-04 : 11:24:07

Hi All -
We have a historical table of some customer data that was getting loaded every week. Unfortunately, not all of the columns (it is from an excel import) were always present, nor were all of the values from the previous week present.

I am trying to piece together the data so that we have a "complete" picture from the data. The very first row might have one or two columns that were never updated in subsequent updates, there might be a column that is always updated, there might be columns that are updated every fifth week, etc. Basically, i just want to get the last non-null value (based on the load datetime) from each column per customer and across multiple dates.

I've tried using a CTE and the Row_Number() function, but I don't simply want the latest set of data, since it is not always complete. And the idea of trying to use Coalesce with 50 columns and close to a year of weekly data loads might cause me to collapse.

If anyone can help jump start my thinking process, I would greatly appreciate it.

Here is some sample table structure and data.

Declare @Customer table (CustomerID int, CustomerName varchar(100), CustStatus varchar(20) ,Employees int, EmpRank int, LoadDtTm datetime)
Insert Into @Customer (CustomerID , CustomerName, CustStatus, Employees , EmpRank , LoadDtTm )
  Values (101, 'Valley Electric', 'New',104,NULL , '2013-11-12')
	, (101, 'Valley Electric', NUll, 89, NULL, '2013-11-14')
	, (101, 'Valley Electric', NULL, 112, 87, '2013-11-22')
	, (101, 'Valley Electric', NULL, NULL, 70, '2013-12-03')
/** return the sample data **/
Select * From @Customer 

Final result should be:
CustomerID   |    CustomerName   |  CustStatus  |  Employees  |  EmpRank
101               Valley Electric	New	            112	         70

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-04 : 16:40:54

It probably will not perform very well, but you could use correlated sub-queries to do what you want:

Declare @Customer table (CustomerID int, CustomerName varchar(100), CustStatus varchar(20) ,Employees int, EmpRank int, LoadDtTm datetime)
Insert Into @Customer (CustomerID , CustomerName, CustStatus, Employees , EmpRank , LoadDtTm )
  Values (101, 'Valley Electric', 'New',104,NULL , '2013-11-12')
	, (101, 'Valley Electric', NUll, 89, NULL, '2013-11-14')
	, (101, 'Valley Electric', NULL, 112, 87, '2013-11-22')
	, (101, 'Valley Electric', NULL, NULL, 70, '2013-12-03')
	, (102, 'Foo Bar', NULL, NULL, -990, '2013-12-03')


SELECT
	Cte.CustomerID
	,(SELECT TOP 1 CustomerName FROM @Customer WHERE CustomerName IS NOT NULL AND CustomerID = Cte.CustomerID ORDER BY LoadDtTm DESC) AS CustomerName
	,(SELECT TOP 1 CustStatus FROM @Customer WHERE CustStatus IS NOT NULL AND CustomerID = Cte.CustomerID ORDER BY LoadDtTm DESC) AS CustStatus
	,(SELECT TOP 1 Employees FROM @Customer WHERE Employees IS NOT NULL AND CustomerID = Cte.CustomerID ORDER BY LoadDtTm DESC) AS Employees
	,(SELECT TOP 1 EmpRank FROM @Customer WHERE EmpRank IS NOT NULL AND CustomerID = Cte.CustomerID ORDER BY LoadDtTm DESC) AS EmpRank
	,MAX(Cte.LoadDtTm) AS LoadDtTm
FROM 
	@Customer AS Cte
GROUP BY 
	Cte.CustomerID

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-04 : 16:47:06

I was also thinking that you might be able to take advantage of a ranking function:

SELECT
	CustomerID
	,MAX(CASE WHEN CustomerNameRowNum = 1 THEN CustomerName ELSE NULL END) AS CustomerName
	,MAX(CASE WHEN CustStatusRowNum = 1 THEN CustStatus ELSE NULL END) AS CustStatus
	,MAX(CASE WHEN EmployeesRowNum = 1 THEN Employees ELSE NULL END) AS Employees
	,MAX(CASE WHEN EmpRankRowNum = 1 THEN EmpRank ELSE NULL END) AS EmpRank
	,MAX(LoadDtTm) AS LoadDtTm
FROM
	(
		SELECT 
			*
			,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CASE WHEN CustomerName IS NULL THEN '1900-01-01' ELSE LoadDtTm END DESC) AS CustomerNameRowNum
			,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CASE WHEN CustStatus IS NULL THEN '1900-01-01' ELSE LoadDtTm END DESC) AS CustStatusRowNum
			,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CASE WHEN Employees IS NULL THEN '1900-01-01' ELSE LoadDtTm END DESC) AS EmployeesRowNum
			,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CASE WHEN EmpRank IS NULL THEN '1900-01-01' ELSE LoadDtTm END DESC) AS EmpRankRowNum
		FROM 
			@Customer
	) AS A
GROUP BY
		CustomerID

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2013-12-05 : 01:27:46

Hi -
Thanks so much for the help and suggestion. I appreciate it.

I will check out your solution and let you know the results.
- will

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2013-12-11 : 18:45:04

Hi

Sorry for the delayed response, but I ended up being very busy with some other projects.

Both of your solutions worked great. We don't have a ton of data, so there isn't a performance issue really. And, looking at the execution plans, the 2nd method (with the ranking) seems the best for me.

thanks again!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-12 : 12:12:16

Cool thanks for the feedback.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources