0

In my main table, I have a date, an article and a category column. Rows are only added when a category changes. I am looking to build a query that shows the date range in-between the category changes. I have a separate table that contains the full date range.

So the query should copy in the missing dates between when the article was first and then next added to the table. Then copy down the article and it's category until the next addition.

Before:

Date    Article Category
20190101    1234    A
20190105    1234    C

After:

Date    Article Category
20190101    1234    A
20190102    1234    A
20190103    1234    A
20190104    1234    A
20190105    1234    C
Krissy
  • 1
  • Are you using Oracle or DB2? – jarlh May 27 '19 at 13:51
  • I removed the inconsistent database tags. Please tag only with the database you are really using. – Gordon Linoff May 27 '19 at 13:51
  • Do you store dates as `int` values as you showed? Or as normal `date` values? Why do you mention another table with "full date range"? Why would we need it? – Mark Barinstein May 27 '19 at 18:24
  • I believe they are int values. I don't have access to the actual DB, I can just view it via queries. The full date range is day by day from 20000101 till 20201231. My approach was to look up the missing values in-between from the full date range table using <> than the dates in the main table. – Krissy May 27 '19 at 18:49
  • 1
    Possible duplicate of [Filling in missing dates DB2 SQL](https://stackoverflow.com/questions/11548419/filling-in-missing-dates-db2-sql) – mustaccio May 27 '19 at 20:41
  • @Krissy Have you got an answer to your question reading the the link above? Or do you still need the resolution? – Mark Barinstein May 29 '19 at 07:04
  • Hi, unfortunately it is not quite the same. The Date is not actually a date value and I need to fill in the missing dates based on the article as in the real table I have several different articles between each unique values and those have different dates. – Krissy May 29 '19 at 08:05

1 Answers1

0

Try this:

with tab (Date, Article, Category) as (values
  (20190101, 1234, 'A')
, (20190105, 1234, 'C')
--, (20190108, 1234, 'D')
)
-- We use date arithmetic below, 
-- so it's better to understand if we convert int to date beforehand
, mytab (Date, Article, Category) as (
select date(to_date(char(date), 'YYYYMMDD')) as Date, Article, Category
from tab
)
, a (date_max, date, article, category) as (
select g.date_max, m.date, m.article, m.category
from 
(
  select 
    article
  , min(date) date_min
  , max(date) date_max
  from mytab
  group by article
) g
join mytab m on m.article=g.article and m.date=g.date_min
  union all
select 
  a.date_max, a.date + 1 day as date
, a.article
, coalesce(
(
select m.category 
from mytab m 
where m.article=a.article and m.date=a.date + 1 day
)
, a.category) as category
from a
where a.date_max <> a.date
)
select 
  --back to int from date
  int(to_char(date, 'YYYYMMDD')) as date
, article, category
from a;
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
  • Hi Mark, thank you for your suggestion. I couldn't make it work just yet but will continue trying and will let you know. KR, Krissy – Krissy May 29 '19 at 17:59