I need help in order to turn table A

into table B using SQL.

So I want 'NAME' next to all 'DATES' in the range of the 'START_DATE' and 'END_DATE' of 'NAME'

I am completely lost and would appreciate your help!
Thank you

I tried to group the data by START_DATE or by NAME or by END_DATE eg.

count(END_Date) over (ORDER BY DATE) AS grp

and than use

first_value(NAME) over (partition by grp order by DATE)

to fill the blanks. Did not work

CodePudding user response：

you can use cross join and then distinct to do this.
Pls refer to below SQL -

select distinct 
dates ,
case when dates between b.Start_DATE and    b.END_DATE then b.NAME else null end as name
from tablea a 
left join ( select distinct Start_DATE, END_DATE,   NAME from tableA) b on 
case when dates between b.Start_DATE and b.END_DATE then 1 else 0 end =1

select distinct Start_DATE, END_DATE, NAME from tableA - This will give you all combination of start,end,name columns.
case when dates between b.Start_DATE and b.END_DATE then 1 else 0 end =1 - this will ensure you dont have unnecessary cross joins but still some cross join will be there.

I did some R&D with my DB, and here are the output.

CodePudding user response：

Well, basically, you need two data sets (tables, views, or CTEs):

• A list of dates, and
• A list of holiday date ranges

Fortunately, table A is both of those!

Let's do this with a CTE (Common Table Expression):

WITH Calendar as (
   SELECT a.Dates as CalendarDate
   FROM   TableA a
)
SELECT  c.CalendarDate
        n.Name
FROM    Calendar c
  LEFT JOIN TableA n
    ON  c.CalendarDate between n.StartDate and n.EndDate

This won't handle the case where multiple names have overlapping date ranges, but you didn't indicate how you needed that handled. In the above, if "Name1" and "Name2" had overlapping ranges, you would see a record for each name for each date (from Calendar) that overlapped.

CodePudding user response：

You can use a user defined table function to carry a value through the date range that's specified in the columns:

-- Sample data:
create or replace temp table T1 as 
select 
COLUMN1::date as "DATES",
COLUMN2::date as "START_DATE",
COLUMN3::date as "END_DATE",
COLUMN4::string as "NAME"
from (values
('2020-01-01','2020-01-01','2020-01-03','Name1'),
('2020-01-02',null,null,''),
('2020-01-03',null,null,''),
('2020-01-04',null,null,''),
('2020-01-05','2020-01-05','2020-01-05','Name2'),
('2020-01-06',null,null,'')
);

-- Create UDTF:
create or replace function 
    CONTINUE_UNTIL(CUR_DATE date, UNTIL date, VAL string, EMPTY_VAL string)
returns table (VAL string)
language javascript
strict immutable
as
$$
{
    initialize: function (argumentInfo, context) {
        this.val = null;
        this.until = null;
    },
    processRow: function (row, rowWriter, context) {
        if (row.UNTIL !== row.EMPTY_VAL && row.UNTIL != null) {
            this.until = row.UNTIL;
            this.val = row.VAL;
        }
        if (row.CUR_DATE <= this.until) {
            rowWriter.writeRow({VAL:this.val});
        } else {
            rowWriter.writeRow({VAL:row.EMPTY_VAL});
        }
    },
}
$$;

-- Call UDTF:
select DATES, VAL as NAME from T1
, table(CONTINUE_UNTIL(DATES, END_DATE, NAME, '') 
        over (partition by 1 order by DATES));

Output: