CODEWITHSUNDEEP
sqloracle-databaseplsql
J. Adam
J. Adam

Reputation: 1641

Count in sql birthday on a weekend

I'm trying to write a program in pl/sql (oracle) that must calculate how many times someone 's birthday was on a weekend.

This is what i got, but im missing somthing like an extraction at everyloop (-1 year) from 2018 to 1990 for example.

Can someone help me out please?

SET SERVEROUTPUT ON;
DECLARE
    v_counter number default 0;
    v_real_birthdate date default to_date('01/01/1990', 'DD/MM/YYYY');
    v_birthdate date default to_date('01/01/2018', 'DD/MM/YYYY');
BEGIN

    WHILE v_counter < 28
    LOOP
        v_leeftijd := v_leeftijd +1;
        dbms_output.put_line( ( TO_CHAR( v_birthdate, 'DAY' ) ) );
    END LOOP;

END;

Upvotes: 0

Views: 304

Answers (2)

Radagast81
Radagast81

Reputation: 3006

You can use the following query (assuming Feb 06, 1981 is the birthday):

WITH b AS (SELECT TO_DATE('02/06/1981', 'MM/DD/RRRR') birthday FROM dual)
SELECT SUM(CASE WHEN TO_CHAR(birthday + (INTERVAL '1' YEAR) * (LEVEL -1), 'FMD') IN ('1','7') THEN 1 END)
  FROM b
 CONNECT BY birthday + (INTERVAL '1' YEAR) * (LEVEL - 1) <= sysdate

Where TO_CHAR(...,'FMD') gives the day of the week from 1 = Sunday till 7 = Saturday

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

If we suppose that I was born on today's day 2010 (which would then be 2010-09-12 (yyyy-mm-dd)), the result would be as follows, step by step.

  • MY_BIRTHDAY represents what we agreed to be my birthday
  • YEARS uses hierarchical query and produces my birthdays from 2010 to current year (2018)
  • DAYS extracts day name from my birthday for every year, using English language
  • the final result filters out weekends (sat, sun)

If you're interested in finding out what every CTE returns, run it one by one and you'll see.

SQL> with
  2    my_birthday as
  3      (select date '&par_birthday' birthday from dual),
  4    years as
  5      (select to_date((extract(year from birthday) + level - 1) ||'-'||
  6                         case when to_char(birthday, 'mm-dd') = '02-29' then '02-28'
  7                              else to_char(birthday, 'mm-dd')
  8                         end,
  9                      'yyyy-mm-dd'
 10                     ) birthday_yr
 11       from my_birthday
 12      connect by level <= extract(year from sysdate) -
 13                          extract(year from birthday) + 1
 14     ),
 15   days as
 16     (select birthday_yr,
 17        to_char(birthday_yr, 'dy', 'nls_date_language=english') dy
 18      from years
 19     )
 20  select birthday_yr, dy
 21  from days
 22  where dy in ('sat', 'sun');
Enter value for par_birthday: 2010-09-12

BIRTHDAY_Y DY
---------- ---
2010-09-12 sun
2015-09-12 sat

SQL> /
Enter value for par_birthday: 2012-02-29

BIRTHDAY_Y DY
---------- ---
2015-02-28 sat
2016-02-28 sun

SQL>

Upvotes: 2

Related Questions