CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
flozzza
flozzza

Reputation: 33

How do I turn a Start Date column, an End Date column and a column with a Name into running dates and a column with that name for all dates in between

I need help in order to turn table A

DATES Start_DATE END_DATE NAME
1.1.20 1.1.20 3.1.20 Name1
2.1.20
3.1.20
4.1.20
5.1.20 5.1.20 5.1.20 Name2
6.1.20

into table B using SQL.

DATES NAME
1.1.20 Name1
2.1.20 Name1
3.1.20 Name1
4.1.20
5.1.20 Name2
6.1.20

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

Upvotes: 3

Views: 204

Answers (2)

Koushik Roy
Koushik Roy

Reputation: 7387

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.

my output

Upvotes: 1

Ann L.
Ann L.

Reputation: 13965

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.

Upvotes: 0

Related Questions