CODEWITHSUNDEEP
sqlpostgresqldml
user25101315
user25101315

Reputation: 13

How to write sql query that will output currency on each 1st date of every month in 2024?

I need to write a query so that there are 12 lines with the current exchange rate on the 1st day of each month 2024. I need to show all 12 months if some information is missing, I need to take previous lag currency value for a particular month.

SELECT '01.01.2024'::date AS data, 67 AS cur UNION ALL
SELECT '03.01.2024'::date AS data, 68 AS cur UNION ALL
SELECT '04.01.2024'::date AS data, 69 AS cur UNION ALL
SELECT '06.01.2024'::date AS data, 70 AS cur UNION ALL
SELECT '04.02.2024'::date AS data, 71 AS cur UNION ALL
SELECT '07.02.2024'::date AS data, 72 AS cur UNION ALL
SELECT '06.03.2024'::date AS data, 77 AS cur UNION ALL
SELECT '04.05.2024'::date AS data, 78 AS cur UNION ALL
SELECT '03.06.2024'::date AS data, 79 AS cur UNION ALL
SELECT '04.06.2024'::date AS data, 80 AS cur UNION ALL
SELECT '05.06.2024'::date AS data, 81 AS cur

SELECT MIN(data) AS first_date, cur AS currency
FROM (
    SELECT data, cur,
        ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM data)::int, ((EXTRACT(MONTH FROM data) - 1) / 12)::int ORDER BY data) AS rn
    FROM your_table_name
) subquery
WHERE rn = 1
GROUP BY cur
ORDER BY first_date

The answer must be like this:

    data    | cur 
------------+-----
 2024-01-01 |  67
 2024-02-01 |  70
 2024-03-01 |  72
 2024-04-01 |  77
 2024-05-01 |  77
 2024-06-01 |  78
 2024-07-01 |  81
 2024-08-01 |  81
 2024-09-01 |  81
 2024-10-01 |  81
 2024-11-01 |  81
 2024-12-01 |  81

Upvotes: 1

Views: 57

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

First, generate the dates you want to see in your output. One method:

WITH dates (dt) AS
(
  VALUES (DATE '2024-01-01'), (DATE '2024-02-01'), (DATE '2024-03-01'),
         (DATE '2024-04-01'), (DATE '2024-05-01'), (DATE '2024-06-01'), 
         (DATE '2024-07-01'), (DATE '2024-08-01'), (DATE '2024-09-01'),
         (DATE '2024-10-01'), (DATE '2024-11-01'), (DATE '2024-12-01')
)

another method:

WITH dates (dt) AS
(
  SELECT generate_series::DATE AS dt FROM generate_series(DATE '2024-01-01',
                                                          DATE '2024-12-01',
                                                          '1 month')
)

Then get the latest currency entry for each date. This can easily be done with a subquery in the select clause or a lateral join.

WITH dates (dt) AS (...)
SELECT d.dt, t.cur
FROM dates d
LEFT JOIN LATERAL
(
  SELECT *
  FROM mytable mt 
  WHERE mt.data <= d.dt
  ORDER BY mt.data DESC
  FETCH FIRST ROW ONLY
) t ON TRUE
ORDER BY d.dt;

Demo: https://dbfiddle.uk/A8N3TL3Y

Upvotes: 0

Related Questions