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 12345»»»

Need to fill the Gaps with previous value Expand / Collapse
Author
Message
Posted Saturday, October 06, 2012 10:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, February 23, 2014 12:27 AM
Points: 94, Visits: 717
Hi experts,

I have a scenario to fill in the GAPS between the dates with previousdate+1 day.
here is the table DDL ,sample data and expected output

CREATE TABLE #SAMPLETABLE
(
DATECOL DATETIME,
WEIGHTS float
)

INSERT INTO #SAMPLETABLE
SELECT '08/09/2012',8.2 UNION ALL
SELECT '08/10/2012',9.4 UNION ALL
SELECT '08/14/2012',10 UNION ALL
SELECT '08/15/2012',9.6 UNION ALL
SELECT '08/16/2012',9.3 UNION ALL
SELECT '08/19/2012',9.7

SELECT *
FROM #SAMPLETABLE
ORDER BY DATECOL

DATECOL WEIGHTS
2012-08-09 00:00:00.000 8.2
2012-08-10 00:00:00.000 9.4
2012-08-14 00:00:00.000 10
2012-08-15 00:00:00.000 9.6
2012-08-16 00:00:00.000 9.3
2012-08-19 00:00:00.000 9.7

What i need is to fill in the GAPS between the dates with previousdate+1 day and weights is same value as previous record values.

-- Expected OutPut
2012-08-09 00:00:00.000 8.2
2012-08-10 00:00:00.000 9.4
2012-08-11 00:00:00.000 9.4
2012-08-12 00:00:00.000 9.4
2012-08-13 00:00:00.000 9.4

2012-08-14 00:00:00.000 10
2012-08-15 00:00:00.000 9.6
2012-08-16 00:00:00.000 9.3
2012-08-17 00:00:00.000 9.3
2012-08-18 00:00:00.000 9.3

2012-08-19 00:00:00.000 9.7

Please help me.

Thanks,
Post #1369495
Posted Sunday, October 07, 2012 3:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
Try this

WITH Range(MINDATE,TotalDays) AS (
SELECT MIN(DATECOL),
DATEDIFF(Day,MIN(DATECOL),MAX(DATECOL))
FROM #SAMPLETABLE),
CTE AS (
SELECT n.number+1 AS number,
ROW_NUMBER() OVER(PARTITION BY CASE WHEN s.DATECOL IS NULL THEN 1 END ORDER BY n.number) AS rn,
DATEADD(Day,n.number,r.MINDATE) AS DATECOL,
s.WEIGHTS
FROM master.dbo.spt_values n
INNER JOIN Range r ON n.number BETWEEN 0 AND r.TotalDays
LEFT OUTER JOIN #SAMPLETABLE s ON s.DATECOL = DATEADD(Day,n.number,r.MINDATE)
WHERE n.type='p')
SELECT a.DATECOL,
COALESCE(b.WEIGHTS,a.WEIGHTS) AS WEIGHTS
FROM CTE a
LEFT OUTER JOIN CTE b ON a.WEIGHTS IS NULL
AND b.WEIGHTS IS NOT NULL
AND b.rn = a.number - a.rn
ORDER BY a.number;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1369510
Posted Sunday, October 07, 2012 8:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
Careful, Mark. I don't know if the optimizer would make better use of a different plan for a larger number of rows but, right now, the execution plan has not one but two accidental cross-joins in it that produce 66 rows each (6 from the original data * 11 for all the desired dates).

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1369532
Posted Sunday, October 07, 2012 7:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:01 PM
Points: 3,590, Visits: 5,096
I don't think this has the same issue that Jeff is reporting about Mark's but it's just another option:

;WITH Tally AS (
SELECT n=number
FROM [master].dbo.spt_values Tally
WHERE [Type] = 'P' AND Number BETWEEN 1 AND 100),
MyData AS (
SELECT DATECOL, WEIGHTS
,rn=ROW_NUMBER() OVER (ORDER BY DATECOL)
FROM #SAMPLETABLE
)
SELECT DATECOL=CASE WHEN c.DATECOL IS NULL THEN a.DATECOL ELSE c.DATECOL END
, a.WEIGHTS
FROM MyData a
OUTER APPLY (
SELECT TOP 1 DATECOL, WEIGHTS
FROM MyData b
WHERE b.rn = 1 + a.rn) b
OUTER APPLY (
SELECT DATEADD(day, n-1, a.DATECOL)
FROM Tally
WHERE DATEADD(day, n, a.DATECOL) BETWEEN a.DATECOL AND b.DATECOL) c(DATECOL)


Note: Should work for any gaps of 100 days or less.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1369588
Posted Sunday, October 07, 2012 8:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
dwain.c (10/7/2012)
I don't think this has the same issue that Jeff is reporting about Mark's but it's just another option:


Gosh. Good bit of code, Dwain, but it also has a full cross join in it according to the actual execution plan. Again, I don't know if these things will "convert" to something else if the row count gets bigger or not. Guess I'll have to give it a try.

I'm really interested in this problem because I normally use the Quirky Update for "data smears" like this and I'd love to see a different method that didn't end up with a cross join in it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1369593
Posted Sunday, October 07, 2012 8:18 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:01 PM
Points: 3,590, Visits: 5,096
Jeff Moden (10/7/2012)
dwain.c (10/7/2012)
I don't think this has the same issue that Jeff is reporting about Mark's but it's just another option:


Gosh. Good bit of code, Dwain, but it also has a full cross join in it according to the actual execution plan. Again, I don't know if these things will "convert" to something else if the row count gets bigger or not. Guess I'll have to give it a try.

I'm really interested in this problem because I normally use the Quirky Update for "data smears" like this and I'd love to see a different method that didn't end up with a cross join in it.


Hmmm. I looked for that but it must have escaped my notice.

I did have a recursive CTE solution (gone now) but I expected it would be a dog so didn't post it.

I thought about a QU but didn't quite figure how it could be used to create additional records. Perhaps a Quirky Merge?

In any event, I am most curious what you come up with...



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1369596
Posted Sunday, October 07, 2012 8:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
I haven't come up with anything yet. I've been watchig you guys with great interest because even the previous row stuff in 2012 won't solve this problem. I've also confirmed that even larger number of rows still maintain the full cross join. If you add a unique clustered index to DATECOL, it cuts it down to triangular joins but 1000 dates still creates more tha 500,000 internal rows spinning off of the source table.

I believe even a "counting" rCTE would beat the cross joins that have occurred so far.

Here's the data generator that I've been using for this problem if you're interested.

CREATE TABLE #SAMPLETABLE
(
DATECOL DATETIME,
WEIGHTS float
)

INSERT INTO #SAMPLETABLE
SELECT TOP 1000
DateCol = DATEADD(mm,(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1),'1900'),
Weights = RAND(CHECKSUM(NEWID()))*10
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2

CREATE UNIQUE CLUSTERED INDEX ByDate ON #SampleTable (DateCol)



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1369599
Posted Sunday, October 07, 2012 8:41 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, February 23, 2014 12:27 AM
Points: 94, Visits: 717
Here is the another solution ;
;WITH DigitsCTE AS
(
SELECT digit
FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS D(digit)
)
, AllDatesCTE AS
(
SELECT DATEADD(DAY, N.number - 1, T.min_date) AS date
FROM (SELECT MIN(T.DATECOL) AS min_date, MAX(T.DATECOL) AS max_date
FROM #SAMPLETABLE AS T) AS T
CROSS APPLY
(SELECT TOP(DATEDIFF(DAY, T.min_date, T.max_date) + 1)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM DigitsCTE AS D0, DigitsCTE AS D1, DigitsCTE AS D2,
DigitsCTE AS D3, DigitsCTE AS D4, DigitsCTE AS D5) AS N
)
SELECT CONVERT(varchar(20), N.date , 101) AS DATECOL, T.WEIGHTS
FROM AllDatesCTE AS N
CROSS APPLY
(SELECT TOP(1) DATECOL, WEIGHTS
FROM #SAMPLETABLE AS T
WHERE T.DATECOL <= N.date
ORDER BY DATECOL DESC) AS T
ORDER BY 1 ASC

Post #1369601
Posted Sunday, October 07, 2012 8:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
Nagaram (10/7/2012)
Here is the another solution ;
;WITH DigitsCTE AS
(
SELECT digit
FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS D(digit)
)
, AllDatesCTE AS
(
SELECT DATEADD(DAY, N.number - 1, T.min_date) AS date
FROM (SELECT MIN(T.DATECOL) AS min_date, MAX(T.DATECOL) AS max_date
FROM #SAMPLETABLE AS T) AS T
CROSS APPLY
(SELECT TOP(DATEDIFF(DAY, T.min_date, T.max_date) + 1)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM DigitsCTE AS D0, DigitsCTE AS D1, DigitsCTE AS D2,
DigitsCTE AS D3, DigitsCTE AS D4, DigitsCTE AS D5) AS N
)
SELECT CONVERT(varchar(20), N.date , 101) AS DATECOL, T.WEIGHTS
FROM AllDatesCTE AS N
CROSS APPLY
(SELECT TOP(1) DATECOL, WEIGHTS
FROM #SAMPLETABLE AS T
WHERE T.DATECOL <= N.date
ORDER BY DATECOL DESC) AS T
ORDER BY 1 ASC



By jove, you've got it. All I had to do on the larger example to make it work was to remove the convert on N.date so that it would sort correctly.

Also be advised that ORDER BY ORDINAL has been deprecated.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1369605
Posted Sunday, October 07, 2012 9:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, February 23, 2014 12:27 AM
Points: 94, Visits: 717
thanks jeff for your valuable advice .

I should change the order by n.date final select as below :

SELECT CONVERT(varchar(20), N.date , 101) AS DATECOL, T.WEIGHTS
FROM AllDatesCTE AS N
CROSS APPLY
(SELECT TOP(1) DATECOL, WEIGHTS
FROM #SAMPLETABLE AS T
WHERE T.DATECOL <= N.date
ORDER BY DATECOL DESC) AS T
ORDER BY N.date ASC
Post #1369606
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse