SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


get list of last non null values


get list of last non null values

Author
Message
winston Smith
winston Smith
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4231 Visits: 2071
I have two tables, a dates table and a values table. They are joined on the date column.
The date table has a range, say from today as far as 20 days from now, incrementing by 1 day each row.

The values table may have a row for a day, and may not.
If the day has a value I want to display that value.
If the day does not have a value in the values table I want to display the last known value.

I think this can be done with windowing functions in a set based manner but have not been able to work it out. I have done it procedurally but im not happy with that at all, and really want to see if this is possible in a set based manner.

Below is some simplified code to allow testing with sample data. Appreciate if someone could point me in the right direction on this.

Thanks,


create table DimDate
(
DateCol date
)

create table TotalsData
(
DateCol date
,Value int
)

insert into Dimdate
values(getdate())
,(dateadd(day, 1,getdate()))
,(dateadd(day, 2,getdate()))
,(dateadd(day, 3,getdate()))
,(dateadd(day, 4,getdate()))
,(dateadd(day, 5,getdate()))
,(dateadd(day, 6,getdate()))
,(dateadd(day, 7,getdate()))
,(dateadd(day, 8,getdate()))
,(dateadd(day, 9,getdate()))
,(dateadd(day, 10,getdate()))
,(dateadd(day, 11,getdate()))
,(dateadd(day, 12,getdate()))

insert into TotalsData
values(getdate(), 1)
,(dateadd(day, 3, getdate()), 55)
,(dateadd(day, 7, getdate()), 66)
,(dateadd(day, 9, getdate()), 77)
,(dateadd(day, 11, getdate()), 88)




Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3496 Visits: 3149

create table dimdate (datevalue date)
create table totalsdata (datevalue date, totalvalue int)

insert into Dimdate
values(getdate())
,(dateadd(day, 1,getdate()))
,(dateadd(day, 2,getdate()))
,(dateadd(day, 3,getdate()))
,(dateadd(day, 4,getdate()))
,(dateadd(day, 5,getdate()))
,(dateadd(day, 6,getdate()))
,(dateadd(day, 7,getdate()))
,(dateadd(day, 8,getdate()))
,(dateadd(day, 9,getdate()))
,(dateadd(day, 10,getdate()))
,(dateadd(day, 11,getdate()))
,(dateadd(day, 12,getdate()))

insert into TotalsData
values(getdate(), 1)
,(dateadd(day, 3, getdate()), 55)
,(dateadd(day, 7, getdate()), 66)
,(dateadd(day, 9, getdate()), 77)
,(dateadd(day, 11, getdate()), 88)


SELECT datevalue,totalvalue FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY d.datevalue ORDER BY t.datevalue DESC) AS rownum,d.datevalue, COALESCE(t.totalvalue,0) totalvalue
FROM dimdate d
LEFT JOIN totalsdata t ON d.datevalue >= t.datevalue
) derived

drop table dimdate
drop table totalsdata


Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13348 Visits: 8001
First, Cudo's to Google Chrome. I just started typing this, then lost power. When I rebooted and opened Chrome my comment was still here.w00t

Below is a better way to create the sample data. The technique uses a tally table; see this article by Jeff Moden for more details. Among other things the tally table will help you produce sample data faster and with cleaner code.

I also tweaked to Nevyn's solution so that it will produce the correct answer (Nevyn's solution is very good but I think it should include WHERE rownum=1.)


-- use this as a base date (so you don't have to keep calculating the date
DECLARE @startdate date=getdate();

-- populate dimDate
-- TOP(13) used for this example
WITH iTally(n) AS
( SELECT TOP(13) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns)
INSERT dbo.DimDate
SELECT dateadd(day,n,@startdate) AS datevalue
FROM iTally;

-- populate dimDate
WITH TotalsData_prep AS
( SELECT datevalue, totalvalue
FROM (VALUES (3,55),(7,66),(9,77),(11,88)) t(datevalue,totalvalue))
INSERT dbo.TotalsData
SELECT dateadd(day,datevalue,@startdate) AS datevalue,
totalvalue
FROM TotalsData_prep;

--SELECT * FROM dbo.DimDate;
--SELECT * FROM dbo.TotalsData;

SELECT datevalue,
totalvalue
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY d.datevalue ORDER BY t.datevalue DESC) AS rownum,
d.datevalue,
COALESCE(t.totalvalue,0) AS totalvalue
FROM dimdate d
LEFT JOIN totalsdata t ON d.datevalue >= t.datevalue
) derived
WHERE rownum=1;




Edit: Type in code comments

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13348 Visits: 8001
Had a few minutes to kill... Here's a solution using what is commonly referred to as the "Quirky Update". I included the code to make sample data...


USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.dimdate') IS NOT NULL DROP TABLE dbo.DimDate;
IF OBJECT_ID('tempdb.dbo.totalsdata') IS NOT NULL DROP TABLE dbo.totalsdata;

create table dimdate (datevalue date);
create table totalsdata (datevalue date, totalvalue int);
GO

DECLARE @startdate date=getdate();

-- populate dimDate
WITH iTally(n) AS
( SELECT TOP(13) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns)
INSERT dbo.DimDate
SELECT dateadd(day,n,@startdate) AS datevalue
FROM iTally;

-- populate dimDate
WITH TotalsData_prep AS
( SELECT datevalue, totalvalue
FROM (VALUES (3,55),(7,66),(9,77),(11,88)) t(datevalue,totalvalue))
INSERT dbo.TotalsData
SELECT dateadd(day,datevalue,@startdate) AS datevalue,
totalvalue
FROM TotalsData_prep;
GO

-- using the "Quirky Update"
DECLARE @x TABLE(datevalue date, tv int);
DECLARE @tv int=0;

INSERT @x
SELECT d.datevalue, coalesce(t.totalvalue,0)
FROM dimdate d
LEFT JOIN dbo.TotalsData t
ON d.datevalue=t.datevalue;

UPDATE @x
SET @tv=tv= CASE WHEN tv<>0 THEN tv ELSE @tv END
FROM dbo.TotalsData

SELECT *
FROM @x;



-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3496 Visits: 3149

I also tweaked to Nevyn's solution so that it will produce the correct answer (Nevyn's solution is very good but I think it should include WHERE rownum=1.)



Yep. Was trying to get the answer in before lunch was over and I had to run to a meeting, and forgot that rather important detail.
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13348 Visits: 8001
Nevyn (2/10/2014)

I also tweaked to Nevyn's solution so that it will produce the correct answer (Nevyn's solution is very good but I think it should include WHERE rownum=1.)



Yep. Was trying to get the answer in before lunch was over and I had to run to a meeting, and forgot that rather important detail.


Been thereHehe still a very good solution!

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
manishpatva
manishpatva
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 14
Try this - short and sweet

;with mycte as (
select T1.Datecol, ISNULL(T2.Value,0) Val, ROW_NUMBER() OVER(order by T1.datecol) RN
from Dimdate T1 left join TotalsData T2 ON T1.DateCol=T2.DateCol
)


select Datecol,(select top 1 Val FROM mycte T where T.RN<mycte.RN and val<>0 order by RN DESC) val
from mycte
where val=0
UNION
SELECT Datecol,Val FROM mycte where val>0
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search