Recipe8.6.Counting the Occurrences of Weekdays in a Year

Recipe 8.6. Counting the Occurrences of Weekdays in a Year

Problem

You want to count the number of times each weekday occurs in one year.

Solution

To find the number of occurrences of each weekday in a year, you must:

1. Generate all possible dates in the year.

2. Format the dates such that they resolve to the name of their respective weekdays.

3. Count the occurrence of each weekday name.

DB2

Use recursive WITH to avoid the need to SELECT against a table with at least 366 rows. Use the function DAYNAME to obtain the weekday name for each date, and then count the occurrence of each:

  1 with x (start_date,end_date)  2 as (  3 select start_date,  4        start_date + 1 year end_date  5   from (  6 select (current_date  7         dayofyear(current_date) day)  8         +1 day as start_date  9   from t1 10        ) tmp 11  union all 12 select start_date + 1 day, end_date 13   from x 14  where start_date + 1 day < end_date 15 ) 16 select dayname(start_date),count(*) 17   from x 18  group by dayname(start_date) 

MySQL

Select against table T500 to generate enough rows to return every day in the year. Use the DATE_FORMAT function to obtain the weekday name of each date, and then count the occurrence of each name:

  1 select date_format(  2           date_add(  3               cast(  4             concat(year(current_date),'-01-01')  5                    as date),  6                    interval t500.id-1 day),  7                    '%W') day,  8        count(*)  9   from t500 10  where t500.id <= datediff( 11                          cast( 12                        concat(year(current_date)+1,'-01-01') 13                               as date), 14                          cast( 15                        concat(year(current_date),'-01-01') 16                               as date)) 17 group by date_format( 18             date_add( 19                 cast( 20               concat(year(current_date),'-01-01') 21                      as date), 22                      interval t500.id-1 day), 23                      '%W') 

Oracle

If you are on Oracle9 i Database or later, you can use the recursive CONNECT BY to return each day in a year. If you are on Oracle8 i Database or earlier, select against table T500 to generate enough rows to return every day in a year. In either case, use the TO_CHAR function to obtain the weekday name of each date, and then count the occurrence of each name.

First, the CONNECT BY solution:

  1 with x as (  2 select level lvl  3   from dual  4  connect by level <= (  5    add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')  6  )  7 )  8 select to_char(trunc(sysdate,'y')+lvl-1,'DAY'), count(*)  9   from x 10  group by to_char(trunc(sysdate,'y')+lvl-1,'DAY') 

and next, the solution for older releases of Oracle:

 1 select to_char(trunc(sysdate,'y')+rownum-1,'DAY'), 2        count(*) 3   from t500 4  where rownum <= (add_months(trunc(sysdate,'y'),12) 5                      - trunc(sysdate,'y')) 6  group by to_char(trunc(sysdate,'y')+rownum-1,'DAY') 

PostgreSQL

Use the built-in function GENERATE_SERIES to generate one rows for every day in the year. Then use the TO_CHAR function to obtain the weekday name of each date. Finally, count the occurrence of each weekday name. For example:

  1 select to_char(  2           cast(  3     date_trunc('year',current_date)  4                as date) + gs.id-1,'DAY'),  5        count(*)  6   from generate_series(1,366) gs(id)  7  where gs.id <= (cast  8                      ( date_trunc('year',current_date) +  9                           interval '12 month' as date) - 10 cast(date_trunc('year',current_date) 11                       as date)) 12  group by to_char( 13              cast( 14        date_trunc('year',current_date) 15           as date) + gs.id-1,'DAY') 

SQL Server

Use the recursive WITH to avoid the need to SELECT against a table with at least 366 rows. If you are on a version of SQL Server that does not support the WITH clause, see the alternative Oracle solution as a guideline for using a pivot table. Use the DATENAME function to obtain the weekday name of each date, and then count the occurrence of each name. For example:

  1 with x (start_date,end_date)  2 as (  3 select start_date,  4        dateadd(year,1,start_date) end_date  5   from (  6 select cast(  7        cast(year(getdate( )) as varchar) + '-01-01'  8             as datetime) start_date  9   from t1 10        ) tmp 11 union all 12 select dateadd(day,1,start_date), end_date 13   from x 14  where dateadd(day,1,start_date) < end_date 15 ) 16 select datename(dw,start_date),count(*) 17   from x 18  group by datename(dw,start_date) 19 OPTION (MAXRECURSION 366) 

Discussion

DB2

Inline view TMP, in the recursive WITH view X, returns the first day of the current year and is shown below:

  select (current_date         dayofyear(current_date) day)         +1 day as start_date   from t1 START_DATE ------------- 01-JAN-2005 

The next step is to add one year to START_DATE, so that you have the beginning and end dates. You need to know both because you want to generate every day in a year. START_DATE and END_DATE are shown below:

  select start_date,        start_date + 1 year end_date   from ( select (current_date         dayofyear(current_date) day)         +1 day as start_date   from t1        ) tmp START_DATE  END_DATE ----------- ------------ 01-JAN-2005 01-JAN-2006 

The next step is to recursively increment START_DATE by one day, stopping before it equals END_DATE. A portion of the rows returned by the recursive view X is shown below:

  with x (start_date,end_date) as ( select start_date,        start_date + 1 year end_date   from ( select (current_date -         dayofyear(current_date) day)         +1 day as start_date   from t1        ) tmp  union all select start_date + 1 day, end_date   from x  where start_date + 1 day < end_date ) select * from x START_DATE  END_DATE ----------- ----------- 01-JAN-2005 01-JAN-2006 02-JAN-2005 01-JAN-2006 03-JAN-2005 01-JAN-2006 … 29-JAN-2005 01-JAN-2006 30-JAN-2005 01-JAN-2006 31-JAN-2005 01-JAN-2006 … 01-DEC-2005 01-JAN-2006 02-DEC-2005 01-JAN-2006 03-DEC-2005 01-JAN-2006 … 29-DEC-2005 01-JAN-2006 30-DEC-2005 01-JAN-2006 31-DEC-2005 01-JAN-2006 

The final step is to use the function DAYNAME on the rows returned by the recursive view X, and count how many times each weekday occurs. The final result is shown below:

  with x (start_date,end_date) as ( select start_date,        start_date + 1 year end_date   from ( select (current_date -         dayofyear(current_date) day)         +1 day as start_date   from t1        ) tmp  union all select start_date + 1 day, end_date   from x  where start_date + 1 day < end_date ) select dayname(start_date),count(*)   from x  group by dayname(start_date) START_DATE   COUNT(*) ---------- ---------- FRIDAY             52 MONDAY             52 SATURDAY           53 SUNDAY             52 THURSDAY           52 TUESDAY            52 WEDNESDAY          52 

MySQL

This solution selects against table T500 to generate one row for every day in the year. The command on line 4 returns the first day of the current year. It does this by returning the year of the date returned by the function CURRENT_DATE, and then appending a month and day (following MySQL's default date format). The result is shown below:

  select concat(year(current_date),'-01-01')   from t1 START_DATE ----------- 01-JAN-2005 

Now that you have the first day in the current year, use the DATEADD function to add each value from T500.IDto generate each day in the year. Use the function DATE_FORMAT to return the weekday for each date. To generate the required number of rows from table T500, find the difference in days between the first day of the current year and the first day of the next year, and return that many rows (will be either 365 or 366). A portion of the results is shown below:

  select date_format(           date_add(               cast(             concat(year(current_date),'-01-01')                    as date),                    interval t500.id-1 day),                    '%W') day   from t500  where t500.id <= datediff(                          cast(                        concat(year(current_date)+1,'-01-01')                               as date),                          cast(                        concat(year(current_date),'-01-01')                              as date)) DAY ----------- 01-JAN-2005 02-JAN-2005 03-JAN-2005 … 29-JAN-2005 30-JAN-2005 31-JAN-2005 … 01-DEC-2005 02-DEC-2005 03-DEC-2005 … 29-DEC-2005 30-DEC-2005 31-DEC-2005 

Now that you can return every day in the current year, count the occurrences of each weekday returned by the function DAYNAME. The final results are shown below:

  select date_format(           date_add(               cast(             concat(year(current_date),'-01-01')                    as date),                    interval t500.id-1 day),                    '%W') day,        count(*)   from t500  where t500.id <= datediff(                          cast(                        concat(year(current_date)+1,'-01-01')                               as date),                          cast(                        concat(year(current_date),'-01-01')                               as date))  group by date_format(              date_add(                  cast(                concat(year(current_date),'-01-01')                       as date),                      interval t500.id-1 day),                      '%W') DAY       COUNT(*) --------- ---------- FRIDAY            52 MONDAY            52 SATURDAY          53 SUNDAY            52 THURSDAY          52 TUESDAY           52 WEDNESDAY         52 

Oracle

The solutions provided either select against table T500 (a pivot table), or use the recursive CONNECT BY and WITH, to generate a row for every day in the current year. The call to the function TRUNC truncates the current date to the first day of the current year.

If you are using the CONNECT BY/WITH solution, you can use the pseudo-column LEVEL to generate sequential numbers beginning at 1. To generate the required number of rows needed for this solution, filter ROWNUM or LEVEL on the difference in days between the first day of the current year and the first day of the next year (will be 365 or 366 days). The next step is to increment each day by adding ROWNUM or LEVEL to the first day of the current year. Partial results are shown below:

  /* Oracle 9i and later */ with x as ( select level lvl   from dual  connect by level <= (    add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')  ) ) select trunc(sysdate,'y')+lvl-1   from x 

If you are using the pivot-table solution, you can use any table or view with at least 366 rows in it. And since Oracle has ROWNUM, there's no need for a table with incrementing values starting from 1. Consider the following example, which uses pivot table T500 to return every day in the current year:

  /* Oracle 8i and earlier */ select trunc(sysdate,'y')+rownum-1 start_date   from t500  where rownum <= (add_months(trunc(sysdate,'y'),12)                      - trunc(sysdate,'y')) START_DATE ----------- 01-JAN-2005 02-JAN-2005 03-JAN-2005 … 29-JAN-2005 30-JAN-2005 31-JAN-2005 … 01-DEC-2005 02-DEC-2005 03-DEC-2005 … 29-DEC-2005 30-DEC-2005 31-DEC-2005 

Regardless of which approach you take, you eventually must use the function TO_ CHAR to return the weekday name for each date, and then count the occurrence of each name. The final results are shown below:

  /* Oracle 9i and later */ with x as ( select level lvl   from dual  connect by level <= (    add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')  ) ) select to_char(trunc(sysdate,'y')+lvl-1,'DAY'), count(*)   from x  group by to_char(trunc(sysdate,'y')+lvl-1,'DAY') /* Oracle 8i and earlier */ select to_char(trunc(sysdate,'y')+rownum-1,'DAY') start_date,        count(*)   from t500  where rownum <= (add_months(trunc(sysdate,'y'),12)                      - trunc(sysdate,'y'))  group by to_char(trunc(sysdate,'y')+rownum-1,'DAY') START_DATE   COUNT(*) ---------- ---------- FRIDAY             52 MONDAY             52 SATURDAY           53 SUNDAY             52 THURSDAY           52 TUESDAY            52 WEDNESDAY          52 

PostgreSQL

The first step is to use the DATE_TRUNC function to return the year of the current date (shown below, selecting against T1 so only one row is returned):

  select cast(          date_trunc('year',current_date)          as date) as start_date   from t1  START_DATE  ----------  01-JAN-2005 

The next step is to select against a row source (any table expression, really) with at least 366 rows. The solution uses the function GENERATE_SERIES as the row source. You can, of course, use table T500 instead. Then add one day to the first day of the current year until you return every day in the year (shown below):

  select cast( date_trunc('year',current_date)                as date) + gs.id-1 as start_date   from generate_series (1,366) gs(id)  where gs.id <= (cast                      ( date_trunc('year',current_date) +                           interval '12 month' as date) -      cast(date_trunc('year',current_date)                       as date)) START_DATE ----------- 01-JAN-2005 02-JAN-2005 03-JAN-2005 … 29-JAN-2005 30-JAN-2005 31-JAN-2005 … 01-DEC-2005 02-DEC-2005 03-DEC-2005 … 29-DEC-2005 30-DEC-2005 31-DEC-2005 

The final step is to use the function TO_CHAR to return the weekday name for each date, and then count the occurrence of each name. The final results are shown below:

  select to_char(           cast(     date_trunc('year',current_date)                as date) + gs.id-1,'DAY') as start_dates,        count(*)   from generate_series(1,366) gs(id)  where gs.id <= (cast                      ( date_trunc('year',current_date) +                          interval '12 month' as date) -      cast(date_trunc('year',current_date)                       as date))  group by to_char(              cast(        date_trunc('year',current_date)           as date) + gs.id-1,'DAY') START_DATE   COUNT(*) ---------- ---------- FRIDAY             52 MONDAY             52 SATURDAY           53 SUNDAY             52 THURSDAY           52 TUESDAY            52 WEDNESDAY          52 

SQL Server

Inline view TMP, in the recursive WITH view X, returns the first day of the current year and is shown below:

  select cast(        cast(year(getdate()) as varchar) + '-01-01'             as datetime) start_date   from t1 START_DATE ----------- 01-JAN-2005 

Once you return the first day of the current year, add one year to START_DATE so that you have the beginning and end dates. You need to know both because you want to generate every day in a year. START_DATE and END_DATE are shown below:

  select start_date,         dateadd(year,1,start_date) end_date   from ( select cast(        cast(year(getdate( )) as varchar) + '-01-01'             as datetime) start_date   from t1        ) tmp START_DATE  END_DATE ----------- ----------- 01-JAN-2005 01-JAN-2006 

Next, recursively increment START_DATE by one day and stop before it equals END_DATE. A portion of the rows returned by the recursive view X is shown below:

  with x (start_date,end_date)  as (  select start_date,         dateadd(year,1,start_date) end_date    from (  select cast(         cast(year(getdate( )) as varchar) + '-01-01'              as datetime) start_date    from t1         ) tmp  union all  select dateadd(day,1,start_date), end_date    from x   where dateadd(day,1,start_date) < end_date  )  select * from x  OPTION (MAXRECURSION 366) START_DATE  END_DATE ----------- ----------- 01-JAN-2005 01-JAN-2006 02-JAN-2005 01-JAN-2006 03-JAN-2005 01-JAN-2006 … 29-JAN-2005 01-JAN-2006 30-JAN-2005 01-JAN-2006 31-JAN-2005 01-JAN-2006 … 01-DEC-2005 01-JAN-2006 02-DEC-2005 01-JAN-2006 03-DEC-2005 01-JAN-2006 … 29-DEC-2005 01-JAN-2006 30-DEC-2005 01-JAN-2006 31-DEC-2005 01-JAN-2006 

The final step is to use the function DATENAME on the rows returned by the recursive view X and count how many times each weekday occurs. The final result is shown below:

  with x(start_date,end_date)  as (  select start_date,         dateadd(year,1,start_date) end_date    from (  select cast(         cast(year(getdate( )) as varchar) + '-01-01'              as datetime) start_date    from t1         ) tmp  union all  select dateadd(day,1,start_date), end_date    from x   where dateadd(day,1,start_date) < end_date  )  select datename(dw,start_date), count(*)    from x   group by datename(dw,start_date)  OPTION (MAXRECURSION 366) START_DATE   COUNT(*) ---------  ---------- FRIDAY             52 MONDAY             52 SATURDAY           53 SUNDAY             52 THURSDAY           52 TUESDAY            52 WEDNESDAY          52