PostgreSQL date query from hell

Question

This query needs to return all columns from the price table for rows that match a specific list of dates. The requirements for the list of dates are between () in the pseudo query below.

At issue here is how to build the list of dates.

The requirement is beyond my current understanding of SQL, but hopefully I can learn from your responses.

-- Given two tables, price_tbl (column price_date) and holiday_tbl (column holiday_date):

select * from price_tbl where price_date in 
  ( 1. Last business  day of every Month from current_date - 30 months to 24 months later  -- (total of 24 months)
    2. Last business  day of every Week  from last date of '1.' to 24 weeks later          -- (total of 24 weeks)
    3. Every business day from last date of '2.' to current_date                           -- (variable number of days - approx 3 to 33 days possible)
  )
 order by price_date;

NB:

For '1.' and '2.' any business day that is in the holiday_tbl must be replaced by the closest preceding business day that is not in the holiday_tbl.
For '3.' any dates in the holiday_tbl are excluded.
It can be assumed that there will be no contiguous holidays that fill an entire week.

I have an Excel table with the date patterns as described above. FWIW, the formulas are listed here in case they may help:

--
-- Excel formulas that match the above:
--  
-- First Month    (in cell B3)     :  =IF(WEEKDAY(EOMONTH(TODAY(),-30),2)>5,WORKDAY(EOMONTH(TODAY(),-24),-1,'PubHol'!$B$5:$B$54),EOMONTH(TODAY(),-30))
-- Next 23 months (in cell B4:B26) :  =IF(WEEKDAY(EOMONTH($B3,1),2)>5,WORKDAY(EOMONTH($B3,1),-1,'PubHol'!$B$5:$B$54),EOMONTH($B3,1))
-- First Week     (in cell B27)    :  =IF(WEEKDAY($B26,2)=5,$B26+7,WORKDAY($B26+(6-WEEKDAY($B26,2)),-1,'PubHol'!$B$5:$B$54))
-- Next 23 weeks  (in cell B28:B50):  =WORKDAY($B27+9,-1,'PubHol'!$B$5:$B$54)
-- All following days              :  =WORKDAY($B50,1,'PublHol'!$B$5:$B$54)
--

Please edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. (edit your question - do not post code or additional information in comments) — user330315
– user330315, Commented Mar 7, 2018 at 15:23

JR ibkr · Accepted Answer · 2018-03-07 18:23:38Z

0

Use SQL query suggested on Postgresql query between date ranges. Most of the languages do have a date API. Use it to generate precise dates based on the supplied input.

Input(Week/day/month) -> Convert it to exact dates -> Create a function which takes this dates and fire the query.

Also, you can use, Postgres database's date/time feature
https://www.postgresql.org/docs/current/static/functions-datetime.html.

edited Mar 7, 2018 at 18:23

answered Mar 7, 2018 at 16:07

JR ibkr

9171 gold badge8 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL date query from hell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related