February 10, 2014 at 10:23 am
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)
February 10, 2014 at 11:00 am
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
February 10, 2014 at 3:34 pm
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[/url] 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
SELECTdateadd(day,datevalue,@startdate) AS datevalue,
totalvalue
FROM TotalsData_prep;
--SELECT * FROM dbo.DimDate;
--SELECT * FROM dbo.TotalsData;
SELECTdatevalue,
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
-- Itzik Ben-Gan 2001
February 10, 2014 at 4:15 pm
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
SELECTdateadd(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;
-- Itzik Ben-Gan 2001
February 10, 2014 at 5:41 pm
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.
February 10, 2014 at 5:56 pm
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:hehe: still a very good solution!
-- Itzik Ben-Gan 2001
February 11, 2014 at 12:59 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply