The problem splits into two parts.
How to check which working days are missing from my database, if some are missing then add them and fill the row with the values from the closest date.
First part, check and find the days. Should i use a gap approach like in the example below?
SELECT t1.col1 AS startOfGap, MIN(t2.col1) AS endOfGap
FROM
(SELECT col1 = theDate + 1 FROM sampleDates tbl1
WHERE NOT EXISTS(SELECT * FROM sampleDates tbl2
WHERE tbl2.theDate = tbl1.theDate + 1)
AND theDate <> (SELECT MAX(theDate) FROM sampleDates)) t1
INNER JOIN
(SELECT col1 = theDate - 1 FROM sampleDates tbl1
WHERE NOT EXISTS(SELECT * FROM sampleDates tbl2
WHERE tbl1.theDate = tbl2.theDate + 1)
AND theDate <> (SELECT MIN(theDate) FROM sampleDates)) t2
ON t1.col1 <= t2.col1
GROUP BY t1.col1;
Then i need to see which is the closest date to the one i was missing and fill the new inserted date (the one which was missing) with the values from the closest. Some time ago, I came up with something to get the closest value from a row, but this time i need to adapt it to check both down and upwards.
SELECT
t,A, C,Y,
COALESCE(Y,
(SELECT TOP (1) Y
FROM tableT AS p2
WHERE
p2.Y IS NOT NULL
AND p2.[t] <= p.[t] and p.C = p2.C
ORDER BY p2.[t] DESC)) as 'YNew'
FROM tableT AS p
order by c, t
How to combine those into one?
Thanks
EDIT: Expected result
Date 1mA
20.12.2012 0.152
21.12.2012 0.181
22 weekend so it's skipped (they are skipped automatically)
23 weekend -,-
24 missing
25 missing
26 missing
27.12.2012 0.173
28.12.2012 0.342
Date 1mA
20.12.2012 0.152
21.12.2012 0.181
22 weekend so it's skipped (they are skipped automatically)
23 weekend 0.181
24 missing 0.181
25 missing 0.181
26 missing 0.173
27.12.2012 0.173
28.12.2012 0.342
So, 24,25,26 are not even there with null values. They are simply not there.
EDIT 2:
For taking the closest value, let's consider the scenario in which i'm always looking above. So always going back 1 when it's missing.
Date 1mA
20.12.2012 0.152
21.12.2012 0.181
22 weekend so it's skipped (they are skipped automatically)
23 weekend 0.181
24 missing 0.181
25 missing 0.181
26 missing 0.181
27.12.2012 0.173
28.12.2012 0.342
For these types of query you gain significant performance benefits from creating a calendar table containing every date you'll ever need to test. (If you're familiar with the term "dimension tables", this is just one such table to enumerate every date of interest.)
Also, the query as a whole can become significantly simpler.
SELECT
cal.calendar_date AS data_date,
CASE WHEN prev_data.gap <= next_data.gap
THEN prev_data.data_value
ELSE COALESCE(next_data.data_value, prev_data.data_value)
END
AS data_value
FROM
calendar AS cal
OUTER APPLY
(
SELECT TOP(1)
data_date,
data_value,
DATEDIFF(DAY, data_date, cal.calendar_date) AS gap
FROM
data_table
WHERE
data_date <= cal.calendar_date
ORDER BY
data_date DESC
)
prev_data
OUTER APPLY
(
SELECT TOP(1)
data_date,
data_value,
DATEDIFF(DAY, cal.calendar_date, data_date) AS gap
FROM
data_table
WHERE
data_date > cal.calendar_date
ORDER BY
data_date ASC
)
next_data
WHERE
cal.calendar_date BETWEEN '2015-01-01' AND '2015-12-31'
;
EDIT Reply to your comment with a different requirement
To always get "the value above" is easier, and to insert those values in to a table is easy enough...
INSERT INTO
data_table
SELECT
cal.calendar_date,
prev_data.data_value
FROM
calendar AS cal
CROSS APPLY
(
SELECT TOP(1)
data_date,
data_value
FROM
data_table
WHERE
data_date <= cal.calendar_date
ORDER BY
data_date DESC
)
prev_data
WHERE
cal.calendar_date BETWEEN '2015-01-01' AND '2015-12-31'
AND cal.calendar_date <> prev_data.data_date
;
Note: You could add WHERE prev_data.gap > 0 to the bigger query above to only get dates that don't already have data.
As suggested by Aaron Bertrand you can write a query as:
-- create a calendar table at run time if you don't have one:
DECLARE @FromDate DATETIME, @ToDate DATETIME;
SET @FromDate = (select min(Date) from test);
SET @ToDate = (select max(Date) from test);
--Get final result:
select Tblfinal.Date,
case when Tblfinal.[1mA] is null then
( select top 1 T2.[1mA] from Test T2
where T2.Date < Tblfinal.Date and T2.[1mA] > 0
order by T2.Date desc)
else Tblfinal.[1mA] end as [1mA]
from
(
select isnull( C.TheDate, T.Date) as Date ,T.[1mA]
from Test T
right join (
-- all days in that period
SELECT TOP (DATEDIFF(DAY, @FromDate, @ToDate)+1)
TheDate = DATEADD(DAY, number, @FromDate)
FROM [master].dbo.spt_values
WHERE [type] = N'P'
)C on T.Date= C.TheDate
) Tblfinal
DEMO
Use a Tally Table to generate all dates from @startDate to @endDate. Then with that, use a LEFT JOIN and OUTER APPLY to achieve the desired result:
SQL Fiddle
;WITH E1(N) AS(
SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
CteTally(N) AS(
SELECT TOP(DATEDIFF(DAY, @startDate, @endDate) + 1)
ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM E4
),
CteDates(dt) AS(
SELECT
DATEADD(DAY, N-1, @startDate)
FROM CteTally ct
)
SELECT
d.dt,
[1mA] = ISNULL(t.[1mA], x.[1mA])
FROM CteDates d
LEFT JOIN tbl t
ON t.Date = d.dt
OUTER APPLY(
SELECT TOP 1 [1mA]
FROM tbl
WHERE [Date] < d.dt
ORDER BY [Date] DESC
)x
WHERE
((DATEPART(dw, d.dt) + @@DATEFIRST) % 7) NOT IN (0, 1)
The WHERE clause
((DATEPART(dw, d.dt) + @@DATEFIRST) % 7) NOT IN (0, 1)
excludes weekends regardless of @@DATEFIRST.
RESULT
| dt | 1mA |
|------------|-------|
| 2012-12-20 | 0.152 |
| 2012-12-21 | 0.181 |
| 2012-12-24 | 0.181 |
| 2012-12-25 | 0.181 |
| 2012-12-26 | 0.181 |
| 2012-12-27 | 0.173 |
| 2012-12-28 | 0.342 |
using recursive CTE we can generate date sequence:
SQL Fiddle
MS SQL Server 2008 Schema Setup:
create table sample (date datetime, data money)
insert sample (date, data)
values
('2015-01-02', 0.2),
('2015-01-03', 0.3),
('2015-01-07', 0.4),
('2015-01-08', 0.5),
('2015-01-09', 0.6),
('2015-01-21', 0.7),
('2015-01-22', 0.8),
('2015-01-27', 0.9),
('2015-01-28', 0.11),
('2015-01-30', 0.12)
Query 1:
declare @d1 datetime = '2015-01-01', @d2 datetime = '2015-01-31'
;with dates as (
select @d1 as date
union all
select dateadd(day, 1, date)
from dates
where dateadd(day, 1, date) <= @d2
), lo_hi as (
select
*,
(select top 1 date from sample s where s.date <= d.date order by s.date desc) as lower_date,
(select top 1 date from sample s where s.date >= d.date order by s.date asc) as higher_date
from dates d
), lo_hi_diff as (
select
*,
isnull(datediff(day, lower_date, date), 100000) as lo_diff,
isnull(datediff(day, date, higher_date), 100000) as hi_diff
from lo_hi
)
select
*,
case
when lo_diff <= hi_diff then
(select top 1 data from sample where date = lower_date)
else
(select top 1 data from sample where date = higher_date)
end as new_data
from lo_hi_diff d
left join sample s on d.date = s.date
Results:
| date | lower_date | higher_date | lo_diff | hi_diff | date | data | new_data |
|---------------------------|---------------------------|---------------------------|---------|---------|---------------------------|--------|----------|
| January, 01 2015 00:00:00 | (null) | January, 02 2015 00:00:00 | 100000 | 1 | (null) | (null) | 0.2 |
| January, 02 2015 00:00:00 | January, 02 2015 00:00:00 | January, 02 2015 00:00:00 | 0 | 0 | January, 02 2015 00:00:00 | 0.2 | 0.2 |
| January, 03 2015 00:00:00 | January, 03 2015 00:00:00 | January, 03 2015 00:00:00 | 0 | 0 | January, 03 2015 00:00:00 | 0.3 | 0.3 |
| January, 04 2015 00:00:00 | January, 03 2015 00:00:00 | January, 07 2015 00:00:00 | 1 | 3 | (null) | (null) | 0.3 |
| January, 05 2015 00:00:00 | January, 03 2015 00:00:00 | January, 07 2015 00:00:00 | 2 | 2 | (null) | (null) | 0.3 |
| January, 06 2015 00:00:00 | January, 03 2015 00:00:00 | January, 07 2015 00:00:00 | 3 | 1 | (null) | (null) | 0.4 |
| January, 07 2015 00:00:00 | January, 07 2015 00:00:00 | January, 07 2015 00:00:00 | 0 | 0 | January, 07 2015 00:00:00 | 0.4 | 0.4 |
| January, 08 2015 00:00:00 | January, 08 2015 00:00:00 | January, 08 2015 00:00:00 | 0 | 0 | January, 08 2015 00:00:00 | 0.5 | 0.5 |
| January, 09 2015 00:00:00 | January, 09 2015 00:00:00 | January, 09 2015 00:00:00 | 0 | 0 | January, 09 2015 00:00:00 | 0.6 | 0.6 |
| January, 10 2015 00:00:00 | January, 09 2015 00:00:00 | January, 21 2015 00:00:00 | 1 | 11 | (null) | (null) | 0.6 |
| January, 11 2015 00:00:00 | January, 09 2015 00:00:00 | January, 21 2015 00:00:00 | 2 | 10 | (null) | (null) | 0.6 |
| January, 12 2015 00:00:00 | January, 09 2015 00:00:00 | January, 21 2015 00:00:00 | 3 | 9 | (null) | (null) | 0.6 |
| January, 13 2015 00:00:00 | January, 09 2015 00:00:00 | January, 21 2015 00:00:00 | 4 | 8 | (null) | (null) | 0.6 |
| January, 14 2015 00:00:00 | January, 09 2015 00:00:00 | January, 21 2015 00:00:00 | 5 | 7 | (null) | (null) | 0.6 |
| January, 15 2015 00:00:00 | January, 09 2015 00:00:00 | January, 21 2015 00:00:00 | 6 | 6 | (null) | (null) | 0.6 |
| January, 16 2015 00:00:00 | January, 09 2015 00:00:00 | January, 21 2015 00:00:00 | 7 | 5 | (null) | (null) | 0.7 |
| January, 17 2015 00:00:00 | January, 09 2015 00:00:00 | January, 21 2015 00:00:00 | 8 | 4 | (null) | (null) | 0.7 |
| January, 18 2015 00:00:00 | January, 09 2015 00:00:00 | January, 21 2015 00:00:00 | 9 | 3 | (null) | (null) | 0.7 |
| January, 19 2015 00:00:00 | January, 09 2015 00:00:00 | January, 21 2015 00:00:00 | 10 | 2 | (null) | (null) | 0.7 |
| January, 20 2015 00:00:00 | January, 09 2015 00:00:00 | January, 21 2015 00:00:00 | 11 | 1 | (null) | (null) | 0.7 |
| January, 21 2015 00:00:00 | January, 21 2015 00:00:00 | January, 21 2015 00:00:00 | 0 | 0 | January, 21 2015 00:00:00 | 0.7 | 0.7 |
| January, 22 2015 00:00:00 | January, 22 2015 00:00:00 | January, 22 2015 00:00:00 | 0 | 0 | January, 22 2015 00:00:00 | 0.8 | 0.8 |
| January, 23 2015 00:00:00 | January, 22 2015 00:00:00 | January, 27 2015 00:00:00 | 1 | 4 | (null) | (null) | 0.8 |
| January, 24 2015 00:00:00 | January, 22 2015 00:00:00 | January, 27 2015 00:00:00 | 2 | 3 | (null) | (null) | 0.8 |
| January, 25 2015 00:00:00 | January, 22 2015 00:00:00 | January, 27 2015 00:00:00 | 3 | 2 | (null) | (null) | 0.9 |
| January, 26 2015 00:00:00 | January, 22 2015 00:00:00 | January, 27 2015 00:00:00 | 4 | 1 | (null) | (null) | 0.9 |
| January, 27 2015 00:00:00 | January, 27 2015 00:00:00 | January, 27 2015 00:00:00 | 0 | 0 | January, 27 2015 00:00:00 | 0.9 | 0.9 |
| January, 28 2015 00:00:00 | January, 28 2015 00:00:00 | January, 28 2015 00:00:00 | 0 | 0 | January, 28 2015 00:00:00 | 0.11 | 0.11 |
| January, 29 2015 00:00:00 | January, 28 2015 00:00:00 | January, 30 2015 00:00:00 | 1 | 1 | (null) | (null) | 0.11 |
| January, 30 2015 00:00:00 | January, 30 2015 00:00:00 | January, 30 2015 00:00:00 | 0 | 0 | January, 30 2015 00:00:00 | 0.12 | 0.12 |
| January, 31 2015 00:00:00 | January, 30 2015 00:00:00 | (null) | 1 | 100000 | (null) | (null) | 0.12 |
