Question 89147 in PostgreSQL Question posted in
The official documentation can be found here.

Automate date interval every month in postgressql – Postgresql

I have a query which calculate latest price till last month. I need to schedule it every month so i can get latest price till last month. If current month is march, it will calculate latest price till Febuary. If current month is april it should calculate till march.
Here is my query:-

SELECT 
        item,
        cost_price,
        effective_date
    FROM 
        join_purchase_table
    WHERE effective_date BETWEEN '2023-02-01' AND '2023-02-28'
    UNION 
    (
        SELECT 
            item,
            cost_price,
            effective_date
        FROM (
            SELECT 
                item,
                cost_price,
                effective_date,
                row_number() over(partition by item order by effective_date desc) AS rn
            FROM 
                join_purchase_table
            WHERE 
                effective_date<'2023-03-01'

Here i have enter date statically, How can i change date dynamically which will calculate latest price till last month and it run every month?

Tags:

2

Answers


  1. 0

    To make it dynamic when you run in the month following the one you are interested in.

    SELECT 
        item,
        cost_price,
        effective_date
    FROM 
        join_purchase_table
    WHERE effective_date BETWEEN date_trunc('month', current_date) - '1 month'::interval AND date_trunc('month', current_date) - '1 day'::interval
    UNION 
    (
        SELECT 
            item,
            cost_price,
            effective_date
        FROM (
            SELECT 
                item,
                cost_price,
                effective_date,
                row_number() over(partition by item order by effective_date desc) AS rn
            FROM 
                join_purchase_table
            WHERE 
                effective_date < date_trunc('month', current_date)

    Though as noted in the comments this query is not complete. This answer just covers how to create the dynamic dates.

    Login or Signup to reply.
  2. 0

    The following query gets the most recent cost_price for each item for the previous month:

    SELECT DISTINCT ON (jpt.item)
    jpt.item,
    jpt.cost_price,
    jpt.effective_date
FROM
    join_purchase_table jpt
WHERE (jpt.effective_date, jpt.effective_date)
OVERLAPS(date_trunc('MONTH', CURRENT_DATE) - interval '1' month, interval '1' month)
ORDER BY
    jpt.item,
    jpt.effective_date DESC;

    PostgreSQL doesn’t have an internal mechanism for scheduling recurrent queries; however, this can be accomplished using external mechanisms such as cron or pg_cron.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search