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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10891 Visits: 2084
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
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7878 Visits: 3151

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 Burstein
Alan Burstein
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32574 Visits: 8578
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


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant 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. -- Itzik Ben-Gan 2001

Alan Burstein
Alan Burstein
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32574 Visits: 8578
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


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant 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. -- Itzik Ben-Gan 2001

Nevyn
Nevyn
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7878 Visits: 3151

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 Burstein
Alan Burstein
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32574 Visits: 8578
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


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant 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. -- Itzik 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