Creating a query with a column of dates that I specify without the ability to create a temporary table

Question

I have read only access in my database and I have a table that looks like this;

I want an output that looks like the following

Is this possible? I think I would need to have a table of dates to pass through but I'm not sure how to go about that. Any help would be appreciated.

Please don't embed images of data. Instead use the formatting available in the question editor to place the data as text. — MatBailie, Aug 03 '21 at 14:43
Use oracle functions to break the date and insert by parts into the new table . Search for `insert-select` https://stackoverflow.com/questions/7323407/insert-select-statement-in-oracle-11g — T.S., Aug 03 '21 at 14:49
your desired output is not clear, why you have an extra row there? why not 7-2018 ? try to explain your logic better — eshirvana, Aug 03 '21 at 15:01
My desired output would include every month and year from 1/2012 on. I only added a few as an example. Should have been more descriptive. My issue is, the table I am looking at doesn't have dates for that range so I have to create them somehow. — Sean Wilson, Aug 03 '21 at 15:04
You have not explained whether your desired result is a dense list of months, or just whatever months are in your data table. Is Change related to SomeValue or just whether there were no records in prior month? — Chris Maurer, Aug 03 '21 at 15:07

score 1 · Answer 1 · answered Aug 03 '21 at 15:08

Use a sub-query factoring clause (WITH) to generate the dates to join:

WITH dates ( dt ) AS (
  SELECT DATE '2016-07-01' FROM DUAL UNION ALL
  SELECT DATE '2017-07-01' FROM DUAL UNION ALL
  SELECT DATE '2019-08-01' FROM DUAL
)
SELECT EXTRACT(MONTH FROM d.dt) AS Month,
       EXTRACT(YEAR FROM d.dt) AS Year,
       NVL2(t.SomeValue, 'Y', 'N') AS "Change?"
FROM   dates d
       LEFT OUTER JOIN table_name t
       ON ( TRUNC(t.effective_date, 'MM') = d.dt )

My desired output would include every month and year from 1/2012 on.

Then use a recursive sub-query factoring clause:

WITH dates ( dt ) AS (
  SELECT DATE '2012-01-01' FROM DUAL
UNION ALL
  SELECT ADD_MONTHS( dt, 1 )
  FROM   dates
  WHERE  ADD_MONTHS( dt, 1 ) <= SYSDATE
)
SELECT EXTRACT(MONTH FROM d.dt) AS Month,
       EXTRACT(YEAR FROM d.dt) AS Year,
       NVL2(t.SomeValue, 'Y', 'N') AS "Change?"
FROM   dates d
       LEFT OUTER JOIN table_name t
       ON ( TRUNC(t.effective_date, 'MM') = d.dt )

I think you also need a compare to Lag(t.SomeValue) to see if there is a record that is the same from the prior month. Something like `Case When t.SomeValue Is Null Then 'N' When t.SomeValue= nvl(lag(t.SomeValue,1) Over (Order By d.dt), t.SomeValue) Then 'N' Else 'Y' End` — Chris Maurer, Aug 03 '21 at 15:20
@ChrisMaurer That really depends on how the OP is defining a "change". If a "change" is a new row in the source table (since their values in the image appear to be identical and they are showing a change) then, no, I don't need to use `LAG`; if, however, they want a difference in values then, yes, you would want to. — MT0, Aug 03 '21 at 15:21

score 0 · Answer 2 · answered Aug 03 '21 at 15:27

You can use recursion to break the records in to who years...

WITH
  fragmented (
    window_start,
    window_close,
    some_value,
    interval_start,
    interval_close
  )
AS
(
  SELECT
    window_start,
    window_close,
    some_value,
    interval_start,
    CASE
      WHEN add_months(interval_start, 12) < window_close
      THEN add_months(interval_start, 12)
      ELSE window_close
    END
      AS interval_close
  FROM
  (
    SELECT
      effective_date                                        AS window_start,
      LEAD(effective_date) OVER (ORDER BY effective_date)   AS window_close,
      some_value,
      effective_date                                        AS interval_start
    FROM
      example
  )
    lookahead
  
  UNION ALL
  
  SELECT
    window_start,
    window_close,
    some_value,
    add_months(interval_start, 12),
    CASE
      WHEN add_months(interval_start, 24) < window_close
      THEN add_months(interval_start, 24)
      ELSE window_close
    END
  FROM
    fragmented
  WHERE
    interval_close < window_close
)
SELECT
  *
FROM
  fragmented
ORDER BY
  window_start,
  interval_start
;

Demo : https://dbfiddle.uk/?rdbms=oracle_18&fiddle=ca1fef00069c178c28e09d209db35395

Creating a query with a column of dates that I specify without the ability to create a temporary table

2 Answers2