How to add the missing dates in sql?

[former_SQLA_member1694869]

My query:

SELECT 
    t_material, 
    t_stock, 
    t_price, 
    date(t_date) as t_date 
FROM
    tblHistory
WHERE 
    t_material = 'M3451' AND
    t_date between '2016-01-20' AND '2016-01-31 23:59:59'
ORDER BY 
    t_material,
    t_date;

Return:

t_material------t_stock------t_price-----t_date
M3451-----------343----------175---------2016-01-20
M3451-----------330----------175---------2016-01-21
M3451-----------360----------175---------2016-01-23
M3451-----------315----------175---------2016-01-24
M3451-----------420----------175---------2016-01-27
M3451-----------430----------175---------2016-01-28
M3451-----------500----------175---------2016-01-29
M3451-----------305----------175---------2016-01-30
M3451-----------307----------175---------2016-01-31

But I want it to return like this:

t_material------t_stock------t_price-----t_date
M3451-----------343----------175---------2016-01-20
M3451-----------330----------175---------2016-01-21
M3451-----------330----------175---------2016-01-22 <<<---Add missing date and use same t_stock from previous date.
M3451-----------360----------175---------2016-01-23
M3451-----------315----------175---------2016-01-24
M3451-----------315----------175---------2016-01-25 <<<---Add missing date and use same t_stock from previous date.
M3451-----------315----------175---------2016-01-26 <<<---Add missing date and use same t_stock from previous date.
M3451-----------420----------175---------2016-01-27
M3451-----------430----------175---------2016-01-28
M3451-----------500----------175---------2016-01-29
M3451-----------305----------175---------2016-01-30
M3451-----------307----------175---------2016-01-31

[VolkerBarth]

Your original query does not group data anyhow, so if a "mssing" row like

M3451-----------330----------175---------2016-01-22

is part of the table tblHistory, then I do not understand why it does not show up in your query, because it fulfills the WHERE condition...

So does the sample show all relevant details, or is something missing?

[former_SQLA_member1694869]

I was a bit unclear in my question. Table tblHistory missing rows from these dates on my example. Therefore they are not included in the results of my sql. I want to add these dates in my sql and use same t_stock value from previous date.

[former_SQLA_member1694869]

I also have a calendar table named tblCalender there field c_date is the date.

[former_SQLA_member1694885 · Accepted solution]

Assuming tblCalendar includes those dates missing in tblHistory, you can use it for an outer join and then grab the missing values using the last_value window function:

First step - get the missing dates
select h.t_material, h.t_stock, h.t_price, c.c_date
from tblCalendar c left outer join tblHistory h on c.c_date = date (h.t_date) and h.t_material = 'M3451'
where c.c_date between '2016-01-20' and '2016-01-31'
order by c.c_date; - you can't order by t_material because of the NULL values introduced by the outer join

Second step - calculate the missing values using the window aggregate
select h.t_material, last_value (h.t_material ignore nulls) over win_xtend t_mat,
h.t_stock, last_value (h.t_stock ignore nulls) over win_xtend t_stk,
h.t_price, last_value (h.t_price ignore nulls) over win_xtend t_prc, c.c_date
from tblCalendar c left outer join tblHistory h on c.c_date = date (h.t_date) and h.t_material = 'M3451'
where c.c_date between '2016-01-20' and '2016-01-31'
window win_xtend as (order by c.c_date asc rows between unbounded preceding and current row)
order by t_mat, c.c_date;

Final step - eliminate the raw values used to check the result
select last_value (h.t_material ignore nulls) over win_xtend t_mat,
last_value (h.t_stock ignore nulls) over win_xtend t_stk,
last_value (h.t_price ignore nulls) over win_xtend t_prc, c.c_date
from tblCalendar c left outer join tblHistory h on c.c_date = date (h.t_date) and h.t_material = 'M3451'
where c.c_date between '2016-01-20' and '2016-01-31'
window win_xtend as (order by c.c_date asc rows between unbounded preceding and current row)
order by t_mat, c.c_date;

If you want to use this query for a result with multiple materials, you may have to use the "partition by t_material" clause in the window spec.

HTH
Volker
DB-TecKnowledgy

[VolkerBarth · Accepted solution]

Volker, if you want to paste code as-is, it's easiest to embed that within a <pre> tag pair... - and you don't have to mask underscores and the like...

Besides that, a very elegant solution:)

[former_SQLA_member1694885 · Accepted solution]

Thank you for the hint, Volker. I knew, there must be a better approach than the global search & replace in the text editor.

[Breck_Carter]

One ugly solution...

CREATE TABLE tblHistory ( 
   t_material VARCHAR ( 5 ),
   t_stock INTEGER,
   t_price INTEGER,
   t_date DATE );

INSERT tblHistory VALUES ( 'M3451', 343, 175, '2016-01-20' );
INSERT tblHistory VALUES ( 'M3451', 330, 175, '2016-01-21' );
INSERT tblHistory VALUES ( 'M3451', 360, 175, '2016-01-23' );
INSERT tblHistory VALUES ( 'M3451', 315, 175, '2016-01-24' );
INSERT tblHistory VALUES ( 'M3451', 420, 175, '2016-01-27' );
INSERT tblHistory VALUES ( 'M3451', 430, 175, '2016-01-28' );
INSERT tblHistory VALUES ( 'M3451', 500, 175, '2016-01-29' );
INSERT tblHistory VALUES ( 'M3451', 305, 175, '2016-01-30' );
INSERT tblHistory VALUES ( 'M3451', 307, 175, '2016-01-31' );
COMMIT;

SELECT tblHistory.t_material,
       tblHistory.t_stock,
       tblHistory.t_price,
       tblHistory.t_date
  FROM tblHistory
UNION
SELECT tblHistory.t_material,
       tblHistory.t_stock,
       tblHistory.t_price,
       DATEADD ( DAY, RowGenerator.row_num - 1, '2016-01-20' ) AS missing_t_date
  FROM tblHistory,
       RowGenerator
 WHERE missing_t_date BETWEEN '2016-01-20' AND '2016-01-31 23:59:59'
   AND missing_t_date NOT IN ( SELECT t_date FROM tblHistory )
   AND tblHistory.t_date = ( SELECT TOP 1 t_date FROM tblHistory WHERE t_date < missing_t_date ORDER BY t_date DESC )
   AND tblHistory.t_date <> missing_t_date
  ORDER BY t_date;

t_material     t_stock     t_price t_date                  
---------- ----------- ----------- ----------------------- 
M3451              343         175 2016-01-20 00:00:00.0   
M3451              330         175 2016-01-21 00:00:00.0   
M3451              330         175 2016-01-22 00:00:00.0   
M3451              360         175 2016-01-23 00:00:00.0   
M3451              315         175 2016-01-24 00:00:00.0   
M3451              315         175 2016-01-25 00:00:00.0   
M3451              315         175 2016-01-26 00:00:00.0   
M3451              420         175 2016-01-27 00:00:00.0   
M3451              430         175 2016-01-28 00:00:00.0   
M3451              500         175 2016-01-29 00:00:00.0   
M3451              305         175 2016-01-30 00:00:00.0   
M3451              307         175 2016-01-31 00:00:00.0   
(12 rows)