Extract last working day of every month in bigquery-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

Try using this in your query ORDER BY your_date ASC