SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Get last non null value across rows/dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dhw
Constraint Violating Yak Guru

USA
327 Posts

Posted - 12/04/2013 :  11:24:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/04/2013 :  16:40:54  Show Profile  Reply with Quote
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

Edited by - Lamprey on 12/04/2013 16:48:32
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/04/2013 :  16:47:06  Show Profile  Reply with Quote
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
Go to Top of Page

dhw
Constraint Violating Yak Guru

USA
327 Posts

Posted - 12/05/2013 :  01:27:46  Show Profile  Reply with Quote
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
Go to Top of Page

dhw
Constraint Violating Yak Guru

USA
327 Posts

Posted - 12/11/2013 :  18:45:04  Show Profile  Reply with Quote
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!
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/12/2013 :  12:12:16  Show Profile  Reply with Quote
Cool thanks for the feedback.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000