CODEWITHSUNDEEP
sqloracle-database
Peter
Peter

Reputation: 1

Sql query to find the holidays in a year

I have a table in which the columns are year,currency and the third column consist of a string of 100001 and it continues where 1 represents holiday and 0 represents working day in that particular year. The table I have is like this-

Currency      Current year    Date values
Dollar             2017                 100111000 till 365
Rupee            2016                  111000010 till 366
Rupee            2000                  11110011 till 366

I need to write a select query which will return the position of all the 1 i.e. the holidays along with date in that particular year. I want the output of that select query in the format

Currency      year     date of holiday
Dollar            2017     01/01/2017

Like that it will continue till it lists all the holidays for 2017 Then it will continue for the other years. I need to write a select statement by connect.

Upvotes: 0

Views: 529

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Use a numbers table and a complicated join:

with n as (
      select rownum as n from dual connect by level <= 366
     )
select currency, year,
       (to_date(year || '-01-01') + n.n - 1) as holiday_date
from t join
     n
     on substr(t.datevalues, n.n, 1) = '1';

Upvotes: 1

Related Questions