OraFAQ Forum: SQL & PL/SQL » Custom Calendar query

Home » SQL & PL/SQL » SQL & PL/SQL » Custom Calendar query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Custom Calendar query [message #689103]

Fri, 22 September 2023 06:58

msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad

Member

Hello Gurus,

I need some help to write a query to generate a custom calendar table, in which Week starts from Monday and Ends on Sunday and if there are 53 weeks in a year, then when rolling to next Year, week# should start from 1.

i.e.

for date 12/28/2020 : which is start of 53rd Week in 2020 (Monday)
        12/29/2020 : which is 2nd day of 53rd Week in 2020 (Tuesday)
        12/30/2020 : which is 3rd day of 53rd Week in 2020 (Wednesday)
        12/31/2020 : which is 4th day of 53rd Week in 2020 (Thursday)
        01/01/2021 : ideally is the 5th day of 53rd week in 2020 (Friday) , but needs to show Week 1
        01/02/2021 : ideally is the 6th day of 53rd week in 2020 (Saturday) , but needs to show Week 1
        01/03/2021 : ideally is the 7th day of 53rd week in 2020 (Sunday) , but needs to show Week 1
        01/04/2021 : should be 1st day of Week 2

and so on

Report message to a moderator

Re: Custom Calendar query [message #689104 is a reply to message #689103]

Fri, 22 September 2023 07:53

Solomon Yakobson
Messages: 3261
Registered: January 2010
Location: Connecticut, USA

Senior Member

with dates as (
               select  date '2020-12-27' + level dt
                 from  dual
                 connect by level <= 10
              )
select  to_char(dt,'FMDay, mm/dd/yyyy') dt,
        ceil((trunc(dt,'iw') - trunc(dt,'yy')) / 7 ) + 1 week
  from  dates
  order by dates.dt
/
DT                          WEEK
--------------------- ----------
Monday, 12/28/2020            53
Tuesday, 12/29/2020           53
Wednesday, 12/30/2020         53
Thursday, 12/31/2020          53
Friday, 1/1/2021               1
Saturday, 1/2/2021             1
Sunday, 1/3/2021               1
Monday, 1/4/2021               2
Tuesday, 1/5/2021              2
Wednesday, 1/6/2021            2

10 rows selected.

SQL>

SY.

Report message to a moderator

Re: Custom Calendar query [message #689105 is a reply to message #689103]

Fri, 22 September 2023 08:32

Michel Cadot
Messages: 68550
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Is this what you want?

SQL> break on year skip 1
SQL> col year noprint
SQL> with
  2    data as (
  3      select to_date('12/28/2020','MM/DD/YYYY') dt from dual
  4      union all
  5      select to_date('12/29/2020','MM/DD/YYYY') dt from dual
  6      union all
  7      select to_date('12/30/2020','MM/DD/YYYY') dt from dual
  8      union all
  9      select to_date('12/31/2020','MM/DD/YYYY') dt from dual
 10      union all
 11      select to_date('01/01/2021','MM/DD/YYYY') dt from dual
 12      union all
 13      select to_date('01/02/2021','MM/DD/YYYY') dt from dual
 14      union all
 15      select to_date('01/03/2021','MM/DD/YYYY') dt from dual
 16      union all
 17      select to_date('01/04/2021','MM/DD/YYYY') dt from dual
 18    ),
 19    data2 as (
 20      select dt from data
 21      union all
 22      select add_months(dt, -12) from data
 23      union all
 24      select add_months(dt, 12) from data
 25      union all
 26      select add_months(dt, 24) from data
 27    )
 28  select to_char(dt-5,'YYYY') year,
 29         to_char(dt,'MM/DD/YYYY') "DATE",
 30         to_char(dt,'Day') day,
 31         trunc((to_number(to_char(dt,'DDD'))-to_number(to_char(dt,'D')))/7) + 1
 32           + case when dt >= next_day(trunc(dt,'YEAR')-1,'Monday') then 1 else 0 end
 33           week
 34  from data2
 35  order by dt
 36  /
DATE       DAY             WEEK
---------- --------- ----------
12/28/2019 Saturday          52
12/29/2019 Sunday            52
12/30/2019 Monday            53
12/31/2019 Tuesday           53
01/01/2020 Wednesday          1
01/02/2020 Thursday           1
01/03/2020 Friday             1
01/04/2020 Saturday           1

12/28/2020 Monday            53
12/29/2020 Tuesday           53
12/30/2020 Wednesday         53
12/31/2020 Thursday          53
01/01/2021 Friday             1
01/02/2021 Saturday           1
01/03/2021 Sunday             1
01/04/2021 Monday             2

12/28/2021 Tuesday           53
12/29/2021 Wednesday         53
12/30/2021 Thursday          53
12/31/2021 Friday            53
01/01/2022 Saturday           1
01/02/2022 Sunday             1
01/03/2022 Monday             2
01/04/2022 Tuesday            2

12/28/2022 Wednesday         53
12/29/2022 Thursday          53
12/30/2022 Friday            53
12/31/2022 Saturday          53
01/01/2023 Sunday             1
01/02/2023 Monday             2
01/03/2023 Tuesday            2
01/04/2023 Wednesday          2

Report message to a moderator

Previous Topic:	Jpeg Image thumbnail,height,width properties need in Oracle 19c
Next Topic:	Tuning of the update statement in oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 05 23:39:36 CST 2023