Gaps in Sequential Data

Last Post 18 Feb 2008 03:37 AM by SwePeso. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
pjmcbride
New Member
New Member

--
28 Dec 2007 06:54 AM
I have a table which holds 3 years of monthly sequential data. Another table defines the periods that the data refers to. So the secod table will hold recrods for each of the next 36-months, and the first table should contain a record for each period, plus a value for that month.

Occasionally, there are gaps in this data; these gaps are caused because, in this case, an exchange has not sent any data for a given month. When this occurs, I need the month(s) missing data to reflect the last good month for which data is present.

For example, from the first table:

1/1/2008 -.078
2/1/2008 -.065
3/1/2008 -.065
4/1/2008 -.072
7/1/2008 -.069
9/1/2008 -.069

Now, I can right outer join to the second table, to create a view like this:

1/1/2008 -.078
2/1/2008 -.065
3/1/2008 -.065
4/1/2008 -.072
5/1/2008 NULL
6/1/2008 NULL
7/1/2008 -.069
8/1/2008 NULL
9/1/2008 -.069

But what I would like to do is create a view that returns the following:

1/1/2008 -.078
2/1/2008 -.065
3/1/2008 -.065
4/1/2008 -.072
5/1/2008 -.072
6/1/2008 -.072
7/1/2008 -.069
8/1/2008 -.069
9/1/2008 -.069

What is the best way to fill in these gaps in the sequential data? Performance is somewhat of an issue; I could write a UDF that grabs the value, but that would be pretty damn slow...

Thanks in advance!
Paul
SQLUSA
New Member
New Member

--
28 Dec 2007 07:07 AM
A view cannot possibly have logic.

So you need a function or a sproc.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/highperformance2005/ The Best SQL Server 2005 Training in the World!
SQLUSA
New Member
New Member

--
09 Jan 2008 09:41 AM
>1.
>> A view cannot possibly have logic. <<
Completely false.


Russell - Pls. Explain. Can you have an IF statement within a view?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005repo...gservices/ The Best SQL Server 2005 Training in the World!
SQLUSA
New Member
New Member

--
10 Jan 2008 05:37 AM
Fair enough. Thanks
SwePeso
New Member
New Member

--
18 Feb 2008 03:14 AM
Here is a solution that can be implemented as a VIEW!

-- Prepare sample data
DECLARE @Sample TABLE (DT DATETIME, Price SMALLMONEY)

SET DATEFORMAT DMY

INSERT @Sample
SELECT '1/1/2008', -.078 UNION ALL
SELECT '2/1/2008', -.065 UNION ALL
SELECT '3/1/2008', -.065 UNION ALL
SELECT '4/1/2008', -.072 UNION ALL
SELECT '7/1/2008', -.069 UNION ALL
SELECT '9/1/2008', -.069

-- This is the solution which can be written as a view
;WITH Yak (DT, maxDT)
AS (
SELECT MIN(DT),
(SELECT MAX(DT) FROM @Sample)
FROM @Sample

UNION ALL

SELECT y.DT + 1,
y.maxDT
FROM Yak AS y
WHERE y.DT < y.maxDT
)

SELECT y.DT,
COALESCE(s.Price, (SELECT TOP 1 g.Price FROM @Sample AS g WHERE g.DT < y.DT ORDER BY g.DT DESC)) AS Price
FROM Yak AS y
LEFT JOIN @Sample AS s ON s.DT = y.DT
SwePeso
New Member
New Member

--
18 Feb 2008 03:37 AM
Of course you can have IF's in VIEWs!

SELECT *
FROM OPENQUERY(LOOPBACK, 'exec (''if 0 = 0 select cpu_busy AS a, io_busy as b from master..spt_monitor else select pack_received as a, pack_sent as b from master..spt_monitor'')')

SELECT *
FROM OPENQUERY(LOOPBACK, 'exec (''if 1 = 0 select cpu_busy AS a, io_busy as b from master..spt_monitor else select pack_received as a, pack_sent as b from master..spt_monitor'')')
You are not authorized to post a reply.

Acceptable Use Policy