3

Find the last weekday for a given month in PostgreSQL

Usage: If month end falls on a Saturday or a Sunday, return the previous Friday, else use month end

Examples:

  • 3/31/2013 falls on a Sunday, so return 3/29/2013
  • 11/30/2013 falls on a Saturday, so return 11/29/2013

How to write this in PostgreSQL SQL?

What I have so far is this (returns only Month Ends, but month ends don't exist when they fall on a Saturday or Sunday):

SELECT as_of_dt, sum(bank_shr_bal) as bank_shr_bal 
  FROM hm_101.vw_gl_bal 
 WHERE as_of_dt = (date_trunc('MONTH', as_of_dt) + INTERVAL '1 MONTH - 1 day')::date 
GROUP BY 1

Thanks

Jon Jaussi
  • 1,298
  • 3
  • 18
  • 36
  • What about different types of holidays? Do you need special handling for them too? In that case, maybe populate a small table with the last day of each month within a suitable period of time. – plundra Apr 11 '13 at 14:59
  • Is this a complicated way of saying, "Return the last weekday for a given month?" – Mike Sherrill 'Cat Recall' Apr 11 '13 at 15:12
  • Thanks plundra. Now you have me thinking I need to rephrase my question. I can see how Holidays would be a consideration. In my data world the only event that results in a missing month end date is when month end falls on a Saturday or Sunday. – Jon Jaussi Apr 11 '13 at 15:24
  • Yes I think Mike Sherrill 'Catcall' is correct and I need to rephrase the question. – Jon Jaussi Apr 11 '13 at 15:33

6 Answers6

1
with s as (
    select *, (date_trunc('MONTH', as_of_dt) + INTERVAL '1 MONTH - 1 day')::date last_day
    from
    hm_101.vw_gl_bal
)
SELECT
    as_of_dt,
    gl_acct_nbr,
    cc_nbr,
    sum(bank_shr_bal) as bank_shr_bal
FROM s
WHERE as_of_dt = (
    last_day
    -
    (extract(dow from last_day) = 5)::int
    -
    2 * (extract(dow from last_day) = 6)::int
    )
GROUP BY 1,2,3
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
1

A calendar table really simplifies the SQL for queries like these. (The table "weekdays" is actually a view based on the calendar table. The structure of it should be obvious.)

select max(cal_date)
from weekdays
where cal_date < '2013-05-01'

or

select max(cal_date)
from weekdays
where cal_date between '2013-04-01' and '2013-04-30'
Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
0

One solution is to use a CTE, find the last day by month in the data and the actual last day for each month

WITH s1
as
(
SELECT 
 date_part('YEAR', as_of_dt) AOD_Year
,date_part('MONTH', as_of_dt) AOD_Month
,(date_trunc('MONTH', as_of_dt) + INTERVAL '1 MONTH - 1 day')::date AOD_MonthEnd
,max(as_of_dt) AOD_LastFound
  FROM hm_101.vw_gl_bal
where (date_trunc('MONTH', as_of_dt) + INTERVAL '1 MONTH - 1 day')::date = '2013-03-31'
 group by 1, 2, 3
)
SELECT 
 s1.AOD_MonthEnd
,s1.AOD_LastFound
,sum(v.bank_shr_bal) as bank_shr_bal 
  FROM hm_101.vw_gl_bal v
 INNER JOIN s1
    on v.as_of_dt = s1.AOD_LastFound
 WHERE v.as_of_dt = '2013-03-29'  
 GROUP BY 1, 2
Jon Jaussi
  • 1,298
  • 3
  • 18
  • 36
0

What you want to do is remove between 0 and 2 days from the last day of the month (which you have).

By extracting the Day Of Week (DOW) and check if it's 0 (Sunday) or 6 (Saturday), we know how many days to remove.

You can do it like this:

... - INTERVAL '1 day' * CASE date_part('DOW', last_day_of_month)
                             WHEN 0 THEN 2 -- Sunday, remove 2 days.
                             WHEN 6 THEN 1 -- Saturday, remove 1 day.
                             ELSE 0 -- Don't remove any days.
                         END

For the sake of readability I didn't include the complete last_day_of_month-calculation in there.

plundra
  • 18,542
  • 3
  • 33
  • 27
0

You can actually do this without CTEs or stored procedures. 

select 
case 
  when extract(dow from last_day_of_month) = 0 
    then last_day_of_month - 2
  when extract(dow from last_day_of_month) = 6 
    then last_day_of_month - 1
  else 
    last_day_of_month 
end as last_weekday_of_month
from(
  SELECT (date_trunc('MONTH', as_of_dt) 
    + INTERVAL '1 MONTH - 1 day')::date as last_day_of_month
  from hm_101.vw_gl_bal 
)subquery;
0
select 
case
  when extract(dow from first_day_of_month) = 0 then first_day_of_month 
  when extract(dow from first_day_of_month) = 1 then first_day_of_month - 1
  when extract(dow from first_day_of_month) = 2 then first_day_of_month - 2
  when extract(dow from first_day_of_month) = 3 then first_day_of_month - 3
  when extract(dow from first_day_of_month) = 4 then first_day_of_month - 4
  when extract(dow from first_day_of_month) = 5 then first_day_of_month - 5
  when extract(dow from first_day_of_month) = 6 then first_day_of_month - 6
  end as first_weekday_of_month,
case 
  when extract(dow from last_day_of_month)  = 6 then last_day_of_month 
  when extract(dow from last_day_of_month)  = 5 then last_day_of_month - 6 
  when extract(dow from last_day_of_month)  = 4 then last_day_of_month - 5 
  when extract(dow from last_day_of_month)  = 3 then last_day_of_month - 4 
  when extract(dow from last_day_of_month)  = 2 then last_day_of_month - 3 
  when extract(dow from last_day_of_month)  = 1 then last_day_of_month - 2 
  when extract(dow from last_day_of_month)  = 0 then last_day_of_month - 1
  end as last_weekday_of_month
from(
  SELECT   
    (date_trunc('month', current_date) -'7day'::interval)::date first_day_of_month,
    (date_trunc('month', current_date) -'1day'::interval)::date as last_day_of_month
)subquery;
Jiya
  • 1