How to add the missing dates in sql?

0

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

asked 10 Dec '16, 08:07

Rolle's gravatar image

Rolle
558●49●51●61
accept rate: 0%

edited 10 Dec '16, 08:08

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?

(10 Dec '16, 08:26) Volker Barth

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.

(10 Dec '16, 08:34) Rolle

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

(10 Dec '16, 10:33) Rolle
2 Answers:
active answersoldest answersnewest answerspopular answers
1

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

permanent link

answered 10 Dec '16, 17:43

Volker%20DB-TecKy's gravatar image

Volker DB-TecKy
545●3●7●15
accept rate: 25%

edited 10 Dec '16, 17:59

1

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:)

(11 Dec '16, 09:51) Volker Barth

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

(11 Dec '16, 10:17) Volker DB-TecKy
1

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)

permanent link

answered 10 Dec '16, 15:09

Breck%20Carter's gravatar image

Breck Carter
32.5k●540●724●1050
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

learn more about Markdown

Question tags:

×104

question asked: 10 Dec '16, 08:07

question was seen: 3,268 times

last updated: 12 Dec '16, 06:58

Related questions

How do I turn off 16.0.0.1512 ISQL parenthesis highlighting?

What is the effect of DBISQL option "auto_commit"?

dbisql: file not found ?

Running a script file with dbisql (command-line)

Add values from one table to another with sql

DBISQL 12.0.1.3324 does not display the single result set of a stored procedure by default

Interactive SQL auto-fill

Dateadd function and dates from table

Sum rows two and two sql?

Programmatically Change Isql Settings