I am working with a database containing time series data. In many, cases there is missing data. For example, while there might be a value for 2001-01-01T23:00:00, there is none for 2001-01-01T23:0100 (one minute later). I would like to replace the missing data with data from the previous record (if the previous record is the same date). Is that possible with T-SQL?
Sure,
UPDATE SomeTable
FROM SomeTable ST
SET SomeColumnWhereDataisMissing =
(
Select TOP (1) SomeColumn From SomeTable ST2
WHERE ST2.TimeColumn < ST.TimeColumn
)
WHERE SomeColumnWhereDataisMissing IS NULL --Or whatever means that there is no data.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Missing means there is no record for a specific datetime value. There is no null because the record does not exist.
|||
It sounds like you're looking for a list of missing dates. You can use an auxiliary numbers table to generate a list of dates and perform an OUTER JOIN against the table in question.
The following sample uses a table with one entry for each minute in a day. To me, it seems best to loop through this table on a daily basis rather than to create a date-only table that would be specific to your problem.
-- Create numbers table. You want this as a permanent table but I'm generating on-the-fly for this example.
DECLARE @.Nums TABLE (Val INT)
;WITH Numbers(n)
AS
(
SELECT 1 AS n
UNION ALL
SELECT (n + 1) AS n
FROM Numbers
WHERE
n < 1440 -- Minutes in a day
)
INSERT INTO @.Nums (Val)
SELECT n from Numbers
OPTION(MAXRECURSION 1440)
-- Create sample data
DECLARE @.Dates TABLE (PKey INT IDENTITY PRIMARY KEY, MyDate DATETIME)
INSERT INTO @.Dates (MyDate)
SELECT '2006-08-21 13:21:00'
UNION
SELECT '2006-08-21 13:22:00'
UNION
SELECT '2006-08-21 13:23:00'
UNION
SELECT '2006-08-21 13:25:00'
UNION
SELECT '2006-08-21 13:26:00'
UNION
SELECT '2006-08-21 13:30:00'
-
-- Find missing ranges.
-
DECLARE @.MinMinute INT
DECLARE @.MaxMinute INT
DECLARE @.CheckDate DATETIME -- This is the day that we're checking
SET @.CheckDate = '2006-08-21'
-- First and last time for the day of @.CheckDate
SELECT @.MinMinute = DATEDIFF(minute, @.CheckDate, MIN(MyDate)),
@.MaxMinute = DATEDIFF(minute, @.CheckDate, MAX(MyDate))
FROM @.Dates
WHERE MyDate BETWEEN @.CheckDate AND DATEADD(Day, 1, @.CheckDate)
-- Find all missing minutes in the sequence
SELECT DATEADD(minute, n.Val, @.CheckDate) AS MissingMinute, 'Missing' AS Status
FROM @.Nums n
LEFT JOIN @.Dates d ON DATEADD(minute, n.Val, @.CheckDate) = d.MyDate
WHERE n.Val BETWEEN @.MinMinute AND @.MaxMinute
and d.MyDate IS NULL
UNION ALL
SELECT MyDate, 'Exists' AS Status
FROM @.Dates
ORDER BY MissingMinute
Thanks to the following sources for information regarding auxiliary numbers tables
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
http://codeinet.blogspot.com/2006/06/sql-numbers-table-using-common-table.html
|||Slight edit - the previous code to generate an auxiliary numbers table will only work on SQL Server 2005. Use the following for SQL 2000:
CREATE TABLE dbo.Numbers
(
Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
)
WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1440
BEGIN
INSERT dbo.Numbers DEFAULT VALUES
END
