CODEWITHSUNDEEP
sqloracle-database
Coopernicus
Coopernicus

Reputation: 95

How to populate a calendar in Oracle DB and join it to a list of Names from another table

I'm not sure if this is possible but... I have a list of names from a table in Oracle DB

Select Staff_Name
From Staff_List

What I'd like to do is then create a calendar and join it on so that every name appears in column A and the days for the current year in column B. Hence every name would appear 365 times in Column A, for each day of the year in column B.

I've no idea how...

Upvotes: 0

Views: 527

Answers (2)

Ted at ORCL.Pro
Ted at ORCL.Pro

Reputation: 1612

Try this:

select staff_name, dt from staff_list 
cross join (select (TRUNC(SYSDATE,'YEAR')-1) + level as dt from dual connect by level <= ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12)-TRUNC(SYSDATE,'YEAR'))

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142743

For a current year (because, not every year has 365 days, right?):

SQL> with
  2  staff_list (staff_name) as
  3    (select 'Little' from dual union all
  4     select 'Foot'   from dual
  5    ),
  6  calendar (c_date) as
  7    (select trunc(sysdate, 'yyyy') + level - 1
  8     from dual
  9     connect by level <= add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy')
 10    )
 11  select s.staff_name, c.c_date
 12  from staff_list s cross join calendar c
 13  order by c.c_date, s.staff_name;

STAFF_ C_DATE
------ ----------
Foot   01.01.2019
Little 01.01.2019
Foot   02.01.2019
Little 02.01.2019
Foot   03.01.2019
Little 03.01.2019
Foot   04.01.2019
Little 04.01.2019
Foot   05.01.2019
Little 05.01.2019

snip

Foot   30.12.2019
Little 30.12.2019
Foot   31.12.2019
Little 31.12.2019

730 rows selected.

SQL>

Upvotes: 1

Related Questions