November 1, 2010 at 1:51 pm
I need the latest value of each column, where 'latest' is defined as the most recent not null value, and 'most recent' being defined as the sequence in which the order by on the row_number command is returned, which in the example below is based on date. Difficult to say it in English, maybe the best way I can describe this is with an example:
declare @a table(cust int, b int, c int, d int, e int, f datetime)
insert @a select 1,null,null, 1, 1, '1 jan 2010'
insert @a select 1, 2,null, 1, 1, '2 jan 2010'
insert @a select 1, 4, 3, 1, 1, '3 jan 2010'
insert @a select 1, 5, 41, 4, 1, '5 jan 2010'
insert @a select 1, 6, 2, 1, 5, '4 jan 2010'
insert @a select 1, 7,null, 1, 1, '6 jan 2010'
insert @a select 2, 8, 1, 71, 1, '7 jan 2010'
insert @a select 2, 9, 1,null, 1, '8 jan 2010'
insert @a select 2,null, 11,null, 31, '9 jan 2010'
select cust, b, c, d, e, ROW_NUMBER() over (partition by cust order by f desc) z, f
from @a
This gives this result set:
cust b c d e z
----------- ----------- ----------- ----------- ----------- --------------------
1 7 NULL 1 1 1
1 5 41 4 1 2
1 6 2 1 5 3
1 4 3 1 1 4
1 2 NULL 1 1 5
1 NULL NULL 1 1 6
2 NULL 11 NULL 31 1
2 9 1 NULL 1 2
2 8 1 71 1 3
The result set I want for cust,b,c,d,e is........
1,7,41,1,1
2,9,11,71,31
Sure this can be done with cursors, or a while loop updating where not null, etc, but it's very unwieldy and I'm sure there has to be a better way!?
November 1, 2010 at 2:02 pm
An alternative is going to be a #tmp with specific indexes using the Quirky Update method, inverting the row numbering, using quirky to carry values down the nulls, then inverting the select to only pull the last entry for each cust.
Middle of a different test harness, get back to you on that if that's not enough to go end to end with it. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 2:32 pm
Is there a limit to the number of entries for a customer?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 1, 2010 at 2:35 pm
No limit.
I've resorted to a while loop, to hit each row with an update, but would still love a better approach as I'm using this in a few places (pardon the sloppy layout below)
Note : pk column added to table, called pk, numberd 1,2,3 etc, and 'done' bit, defaulted to 0
declare @pk int
while exists(select 1 from @a where done = 0)
begin
select top 1 @pk = pk from @a where done = 0
update a set b = isnull(a.b,a2.b), c = ISNULL(a.c,a2.c), d = ISNULL(a.d,a2.d)
from @a a join @a a2 on a.cust = a2.cust and a.z + 1 = a2.z
update @a set done = 1 where pk = @pk
end
There must be a better way!
November 1, 2010 at 3:04 pm
Try this:
Be aware, this uses a serial updating methodology and I don't always follow every rule from Jeff's Quirky Update post here on SSC. This will work, however, as long as you completely control the #tmp. This should be wrapped (during the update) with the safety cte found here for complete assurance: http://www.sqlservercentral.com/Forums/Topic802558-203-4.aspx#bm980118
DROP TABLE #tmp
GO
declare @a table(cust int, b int, c int, d int, e int, f datetime)
insert @a select 1,null,null, 1, 1, '1 jan 2010'
insert @a select 1, 2,null, 1, 1, '2 jan 2010'
insert @a select 1, 4, 3, 1, 1, '3 jan 2010'
insert @a select 1, 5, 41, 4, 1, '5 jan 2010'
insert @a select 1, 6, 2, 1, 5, '4 jan 2010'
insert @a select 1, 7,null, 1, 1, '6 jan 2010'
insert @a select 2, 8, 1, 71, 1, '7 jan 2010'
insert @a select 2, 9, 1,null, 1, '8 jan 2010'
insert @a select 2,null, 11,null, 31, '9 jan 2010'
select cust, b, c, d, e, ROW_NUMBER() over (partition by cust order by f desc) z, f
from @a
CREATE TABLE #tmp
(cust INT,
b INT,
c INT,
d INT,
e INT,
f DATETIME,
OrderingInt INT)
INSERT INTO #tmp
SELECT
Cust, b, c, d, e, f,
ROW_NUMBER() over ( partition by cust order by f ASC)
FROM
CREATE CLUSTERED INDEX idx_#tmp ON #tmp (Cust, OrderingInt)
SELECT * FROM #tmp
DECLARE @cust INT, @b-2 INT, @C INT, @d INT, @e INT, @f INT
UPDATE#tmp
SET@b-2 = b = CASE WHEN @Cust = CUST THEN ISNULL( b, @b-2) ELSE b END,
@C = c = CASE WHEN @Cust = CUST THEN ISNULL( c, @C) ELSE c END,
@d = d = CASE WHEN @Cust = CUST THEN ISNULL( d, @d) ELSE d END,
@e = e = CASE WHEN @Cust = CUST THEN ISNULL( e, @e) ELSE e END,
@cust = cust
OPTION ( MAXDOP 1)
SELECT * from #tmp
SELECT
t.cust,
t.b,
t.c,
t.d,
t.e,
t.f
FROM
#tmp AS t
JOIN
(SELECT
cust, MAX(OrderingInt) AS MaxOI
FROM
#tmp AS t2
GROUP BY
cust
) AS drv
ONt.cust = drv.cust
AND t.OrderingInt = drv.MaxOI
EDIT: There was an error in the index declaration. Please re-review that statement.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply