SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need to fill the GAPS with previous values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ranjit.ileni
Posting Yak Master

India
182 Posts

Posted - 10/07/2012 :  00:34:54  Show Profile  Reply with Quote
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,

--Irk

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
264 Posts

Posted - 10/07/2012 :  01:44:25  Show Profile  Reply with Quote
Try something like this:

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 N.date 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




For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha
Go to Top of Page

Ranjit.ileni
Posting Yak Master

India
182 Posts

Posted - 10/07/2012 :  03:01:15  Show Profile  Reply with Quote
Thanks malpashaa! Wow it looks great . Code will take some time for me to digest the code :)

--Irk
Go to Top of Page

Ranjit.ileni
Posting Yak Master

India
182 Posts

Posted - 10/07/2012 :  04:26:53  Show Profile  Reply with Quote
Hi malpashaa,

In AllDatesCTE to select all the dates instead of using DigitsCTE with 6 cross joins
we can go for recursive CTE to loop through the min and max date to select all the dates.

Thanks,
Ranjit

--Irk

Edited by - Ranjit.ileni on 10/07/2012 04:27:43
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
264 Posts

Posted - 10/07/2012 :  05:24:26  Show Profile  Reply with Quote
quote:
Originally posted by Ranjit.ileni

Hi malpashaa,

In AllDatesCTE to select all the dates instead of using DigitsCTE with 6 cross joins
we can go for recursive CTE to loop through the min and max date to select all the dates.

Thanks,
Ranjit

--Irk


You can, and you can use tally table if you have one.



For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000