Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

get list of last non null values Expand / Collapse
Author
Message
Posted Monday, February 10, 2014 10:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:36 AM
Points: 904, Visits: 1,878
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)



Post #1539863
Posted Monday, February 10, 2014 11:00 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 8:15 AM
Points: 628, Visits: 2,129
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

Post #1539883
Posted Monday, February 10, 2014 3:34 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:06 AM
Points: 612, Visits: 2,853
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.

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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"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

My blog
Post #1539969
Posted Monday, February 10, 2014 4:15 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:06 AM
Points: 612, Visits: 2,853
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"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

My blog
Post #1539981
Posted Monday, February 10, 2014 5:41 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 8:15 AM
Points: 628, Visits: 2,129

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.
Post #1539996
Posted Monday, February 10, 2014 5:56 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:06 AM
Points: 612, Visits: 2,853
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 there still a very good solution!


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"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

My blog
Post #1540001
Posted Tuesday, February 11, 2014 12:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 6:43 AM
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
Post #1540065
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse