CODEWITHSUNDEEP
postgresql
Rn3l
Rn3l

Reputation: 53

How to retrieve the last day of the month data

I have this SQL statement to retrieve ONLY the last day of stock quotes. But I am missing the data if the data falls on a non-end of month data.

This is my SQL.

select symbol, trade_date, close_price from soa_pse_stock_quotes_history where symbol = '2GO'
group by symbol, trade_date, close_price
having max(trade_date::date) = (date_trunc('month', trade_date::date) + interval '1 month - 1 day')::date
order by trade_date desc limit 100   ;

I am missing quotes for July and Apr because Jul data is July 29, the last business day of the month. The same for April.

What is the correct SQL to fetch the last quote for each end of the month?

Result:

2GO 2022-08-31 00:00:00 7.28
2GO 2022-06-30 00:00:00 6.82
2GO 2022-05-31 00:00:00 7.1
2GO 2022-03-31 00:00:00 7.31
2GO 2022-02-28 00:00:00 7.5
2GO 2021-12-31 00:00:00 7.61
2GO 2021-09-30 00:00:00 8.14
2GO 2021-08-31 00:00:00 8.06
2GO 2021-06-30 00:00:00 8.48
2GO 2021-05-31 00:00:00 8.34
2GO 2021-04-30 00:00:00 8.4
2GO 2021-03-31 00:00:00 8.5
2GO 2020-09-30 00:00:00 8.42
2GO 2020-06-30 00:00:00 9.63

Upvotes: 0

Views: 187

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19665

Create a subquery that finds the max date for a symbol by month using GROUP BY symbol, date_trunc('month', trade_date)). Then join this to the table to get the closing price.

CREATE TABLE stock_table (
    symbol varchar,
    close_price numeric,
    trade_date date
);

INSERT INTO stock_table
    VALUES ('2GO', 7.0, '02/26/2022'),
    ('2GO', 7.1, '02/28/2022'),
    ('2GO', 7.4, '03/31/2022'),
    ('2GO', 7.2, '04/28/2022'),
    ('2GO', 7.3, '04/29/2022');

SELECT
    st.symbol,
    st.trade_date,
    st.close_price
FROM
    stock_table AS st
    JOIN (
        SELECT
            symbol,
            max(trade_date) AS max_date
        FROM
            stock_table
        WHERE 
            symbol = '2GO'
        GROUP BY
            symbol,
            date_trunc('month', trade_date)) AS m_date 
    ON st.symbol = m_date.symbol
    AND st.trade_date = m_date.max_date;

 symbol | trade_date | close_price 
--------+------------+-------------
 2GO    | 02/28/2022 |         7.1
 2GO    | 03/31/2022 |         7.4
 2GO    | 04/29/2022 |         7.3

Upvotes: 1

Related Questions