My query:
Return:
But I want it to return like this:
|
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 Second step - calculate the missing values using the window aggregate Final step - eliminate the raw values used to check the result 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 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
|
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) |
Your original query does not group data anyhow, so if a "mssing" row like
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?
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.
I also have a calendar table named tblCalender there field c_date is the date.