Find The Missing Entries For The Working Days And Fill The Row With The Values From The Closest Date
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
Solution 1:
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,
CASEWHEN prev_data.gap <= next_data.gap
THEN prev_data.data_value
ELSECOALESCE(next_data.data_value, prev_data.data_value)
ENDAS 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
ORDERBY
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
ORDERBY
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...
INSERTINTO
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
ORDERBY
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.
Solution 2:
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= (selectmin(Date) from test);
SET@ToDate= (selectmax(Date) from test);
--Get final result:select Tblfinal.Date,
casewhen Tblfinal.[1mA] isnullthen
( select top 1 T2.[1mA] from Test T2
where T2.Date < Tblfinal.Date and T2.[1mA] >0orderby T2.Date desc)
else Tblfinal.[1mA] endas [1mA]
from
(
select isnull( C.TheDate, T.Date) asDate ,T.[1mA]
from Test T
rightjoin (
-- all days in that periodSELECT 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
Solution 3:
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:
;WITH E1(N) AS(
SELECT1FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT1FROM E1 a CROSSJOIN E1 b),
E4(N) AS(SELECT1FROM E2 a CROSSJOIN E2 b),
CteTally(N) AS(
SELECT TOP(DATEDIFF(DAY, @startDate, @endDate) +1)
ROW_NUMBER() OVER(ORDERBY(SELECTNULL))
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
LEFTJOIN tbl t
ON t.Date = d.dt
OUTER APPLY(
SELECT TOP 1 [1mA]
FROM tbl
WHERE [Date] < d.dt
ORDERBY [Date] DESC
)x
WHERE
((DATEPART(dw, d.dt) + @@DATEFIRST) %7) NOTIN (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|Solution 4:
using recursive CTE we can generate date sequence:
MS SQL Server 2008 Schema Setup:
createtable 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@d1asdateunionallselect dateadd(day, 1, date)
from dates
where dateadd(day, 1, date) <=@d2
), lo_hi as (
select*,
(select top 1datefrom sample s where s.date <= d.date orderby s.date desc) as lower_date,
(select top 1datefrom sample s where s.date >= d.date orderby 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*,
casewhen lo_diff <= hi_diff then
(select top 1 data from sample wheredate= lower_date)
else
(select top 1 data from sample wheredate= higher_date)
endas new_data
from lo_hi_diff d
leftjoin sample s on d.date = s.date
|date|lower_date|higher_date|lo_diff|hi_diff|date|data|new_data||---------------------------|---------------------------|---------------------------|---------|---------|---------------------------|--------|----------||January,012015 00:00:00|(null)|January,022015 00:00:00|100000|1|(null)|(null)|0.2||January,022015 00:00:00|January,022015 00:00:00|January,022015 00:00:00|0|0|January,022015 00:00:00|0.2|0.2||January,032015 00:00:00|January,032015 00:00:00|January,032015 00:00:00|0|0|January,032015 00:00:00|0.3|0.3||January,042015 00:00:00|January,032015 00:00:00|January,072015 00:00:00|1|3|(null)|(null)|0.3||January,052015 00:00:00|January,032015 00:00:00|January,072015 00:00:00|2|2|(null)|(null)|0.3||January,062015 00:00:00|January,032015 00:00:00|January,072015 00:00:00|3|1|(null)|(null)|0.4||January,072015 00:00:00|January,072015 00:00:00|January,072015 00:00:00|0|0|January,072015 00:00:00|0.4|0.4||January,082015 00:00:00|January,082015 00:00:00|January,082015 00:00:00|0|0|January,082015 00:00:00|0.5|0.5||January,092015 00:00:00|January,092015 00:00:00|January,092015 00:00:00|0|0|January,092015 00:00:00|0.6|0.6||January,102015 00:00:00|January,092015 00:00:00|January,212015 00:00:00|1|11|(null)|(null)|0.6||January,112015 00:00:00|January,092015 00:00:00|January,212015 00:00:00|2|10|(null)|(null)|0.6||January,122015 00:00:00|January,092015 00:00:00|January,212015 00:00:00|3|9|(null)|(null)|0.6||January,132015 00:00:00|January,092015 00:00:00|January,212015 00:00:00|4|8|(null)|(null)|0.6||January,142015 00:00:00|January,092015 00:00:00|January,212015 00:00:00|5|7|(null)|(null)|0.6||January,152015 00:00:00|January,092015 00:00:00|January,212015 00:00:00|6|6|(null)|(null)|0.6||January,162015 00:00:00|January,092015 00:00:00|January,212015 00:00:00|7|5|(null)|(null)|0.7||January,172015 00:00:00|January,092015 00:00:00|January,212015 00:00:00|8|4|(null)|(null)|0.7||January,182015 00:00:00|January,092015 00:00:00|January,212015 00:00:00|9|3|(null)|(null)|0.7||January,192015 00:00:00|January,092015 00:00:00|January,212015 00:00:00|10|2|(null)|(null)|0.7||January,202015 00:00:00|January,092015 00:00:00|January,212015 00:00:00|11|1|(null)|(null)|0.7||January,212015 00:00:00|January,212015 00:00:00|January,212015 00:00:00|0|0|January,212015 00:00:00|0.7|0.7||January,222015 00:00:00|January,222015 00:00:00|January,222015 00:00:00|0|0|January,222015 00:00:00|0.8|0.8||January,232015 00:00:00|January,222015 00:00:00|January,272015 00:00:00|1|4|(null)|(null)|0.8||January,242015 00:00:00|January,222015 00:00:00|January,272015 00:00:00|2|3|(null)|(null)|0.8||January,252015 00:00:00|January,222015 00:00:00|January,272015 00:00:00|3|2|(null)|(null)|0.9||January,262015 00:00:00|January,222015 00:00:00|January,272015 00:00:00|4|1|(null)|(null)|0.9||January,272015 00:00:00|January,272015 00:00:00|January,272015 00:00:00|0|0|January,272015 00:00:00|0.9|0.9||January,282015 00:00:00|January,282015 00:00:00|January,282015 00:00:00|0|0|January,282015 00:00:00|0.11|0.11||January,292015 00:00:00|January,282015 00:00:00|January,302015 00:00:00|1|1|(null)|(null)|0.11||January,302015 00:00:00|January,302015 00:00:00|January,302015 00:00:00|0|0|January,302015 00:00:00|0.12|0.12||January,312015 00:00:00|January,302015 00:00:00|(null)|1|100000|(null)|(null)|0.12|
Post a Comment for "Find The Missing Entries For The Working Days And Fill The Row With The Values From The Closest Date"