CODEWITHSUNDEEP
mysqlgoogle-cloud-platformgoogle-bigqueryhql
Sara
Sara

Reputation: 322

Extract last working day of every month in bigquery

Currently I'm using the below code:

SELECT DATE_SUB(example, INTERVAL 1 DAY) , 
       FORMAT_DATE("%A", DATE_SUB(example, INTERVAL 1 DAY))
FROM UNNEST(GENERATE_DATE_ARRAY('2013-01-1', '2030-04-01', INTERVAL 3 MONTH)) AS example

The output which I'm getting is:

2012-12-31    Monday
2013-03-31    Sunday
2013-06-30    Sunday
2013-09-30    Monday
2013-12-31    Tuesday
2014-03-31    Monday
2014-06-30    Monday
2014-09-30    Tuesday

The expected output is:

2012-12-31    Monday
2013-03-29    Friday
2013-06-28    Friday
2013-09-30    Monday
2013-12-31    Tuesday
2014-03-31    Monday
2014-06-30    Monday
2014-09-30    Tuesday

I'm trying to extract the last working day for each quarter(3 months of timegap). But i couldn't exclude saturdays and sundays.

It would be great if someone help me to sort out this issue.

Upvotes: 0

Views: 709

Answers (4)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You also can calculate the diffenece to the last workday with ELT().

This function returns the difference, in days, to the last workday

ELT(dayofweek(example), '2','3','1','1','1','1','1')

Sample for MariaDB

SELECT 
    example as org
    , example - INTERVAL ELT(dayofweek(example), '2','3','1','1','1','1','1') DAY AS LAST_WORKDAY
    , DATE_FORMAT(example - INTERVAL ELT(dayofweek(example), '2','3','1','1','1','1','1') DAY,'%W') AS WEEKDAY
FROM (
SELECT date('2013-01-01') + interval seq MONTH as example from seq_0_to_4) as d;

Sample for MySQL

SELECT 
    example as org
    , example - INTERVAL ELT(dayofweek(example), '2','3','1','1','1','1','1') DAY AS LAST_WORKDAY
    , DATE_FORMAT(example - INTERVAL ELT(dayofweek(example), '2','3','1','1','1','1','1') DAY,'%W') AS WEEKDAY
FROM UNNEST(GENERATE_DATE_ARRAY('2013-01-1', '2030-04-01', INTERVAL 3 MONTH)) AS example;

Result

MariaDB [bernd]> SELECT 
    -> example as org
    -> , example - INTERVAL ELT(dayofweek(example), '2','3','1','1','1','1','1') DAY AS LAST_WORKDAY
    -> , DATE_FORMAT(example - INTERVAL ELT(dayofweek(example), '2','3','1','1','1','1','1') DAY,'%W') AS WEEKDAY
    -> FROM (
    -> SELECT date('2013-01-01') + interval seq MONTH as example from seq_0_to_4) as d ;
+------------+--------------+----------+
| org        | LAST_WORKDAY | WEEKDAY  |
+------------+--------------+----------+
| 2013-01-01 | 2012-12-31   | Monday   |
| 2013-02-01 | 2013-01-31   | Thursday |
| 2013-03-01 | 2013-02-28   | Thursday |
| 2013-04-01 | 2013-03-29   | Friday   |
| 2013-05-01 | 2013-04-30   | Tuesday  |
+------------+--------------+----------+
5 rows in set (0.001 sec)

MariaDB [bernd]>

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Consider below approach

create temp function getLastBusinessDayOfMonth(next_month_first_day date) as ((
  select as struct month_last_business_day,
    format_date("%A", month_last_business_day) day_of_week
  from unnest([struct(next_month_first_day - 1 as month_last_day, extract(dayofweek from next_month_first_day) as adjust)]),
  unnest([struct(month_last_day - if(adjust > 2, 0, adjust) as month_last_business_day)])
));
select getLastBusinessDayOfMonth(sample).*
from unnest(generate_date_array('2013-01-1', '2014-11-01', interval 3 month)) as sample

with output

enter image description here

Upvotes: 0

Mohammad
Mohammad

Reputation: 678

try this.

SELECT case when FORMAT_DATE("%A", DATE_SUB(example, INTERVAL 1 DAY)) = 'Sunday' 
                then date_sub(DATE_SUB(example, INTERVAL 1 DAY), interval 2 day)
            when FORMAT_DATE("%A", DATE_SUB(example, INTERVAL 1 DAY)) = 'Saturday' 
                then date_sub(DATE_SUB(example, INTERVAL 1 DAY), interval 1 day)
                 else DATE_SUB(example, INTERVAL 1 DAY) end as d1,
       case when FORMAT_DATE("%A", DATE_SUB(example, INTERVAL 1 DAY)) in ('Saturday','Sunday') 
                then 'Friday' 
                 else FORMAT_DATE("%A", DATE_SUB(example, INTERVAL 1 DAY)) end
FROM UNNEST(GENERATE_DATE_ARRAY('2013-01-1', '2030-04-01', INTERVAL 3 MONTH)) AS example
order by d1

Upvotes: 1

ChandraShekhara
ChandraShekhara

Reputation: 177

Try using this in your query ORDER BY your_date ASC

Upvotes: 1

Related Questions