CODEWITHSUNDEEP
sqlpostgresql
nadegnaro
nadegnaro

Reputation: 45

query get every last date on a month

i have a table like this

date amount
2021-01-01 100
2021-01-15 200
2021-01-31 300
2021-02-01 400
2021-02-15 500
2021-02-28 600
2021-03-01 700
2021-03-02 800
2021-03-03 900

and i want create a report like this

date amount
2021-01-31 300
2021-02-28 600
2021-03-03 900

I confused how create a query to provide data like that, get amount every last date on a month.

Let's say I put an amount on 4 march 2021 the report will show data like this

date amount
2021-01-31 300
2021-02-28 600
2021-03-04 1000

I am using PostgreSQL

Upvotes: 1

Views: 225

Answers (3)

S-Man
S-Man

Reputation: 23676

demo:db<>fiddle

To find the most recent record of a group you can use DISTINCT ON:

SELECT DISTINCT ON (date_trunc('month', my_date))
    *
FROM mytable
ORDER BY date_trunc('month', my_date), my_date DESC
  1. date_trunc('month', ...) normalizes a date to the first of a month. So every date within February will be converted to '2021-02-01'. This can be used for a group of months
  2. Ordering these groups by their actual date DESC, which orders the most recent date to the top of the month group. DISTINCT ON returns exactly this records.

Upvotes: 2

Mushariar
Mushariar

Reputation: 132

select date_trunc('month', date + '01 Months'::interval) - '01 Days'::interval, sum(amount) 
from tableName
group by date_trunc('month', date + '01 Months'::interval) - '01 Days'::interval
order by date_trunc('month', date + '01 Months'::interval) - '01 Days'::interval desc

Upvotes: 0

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

You can do this with row_number() :

**Schema (PostgreSQL v13)**

CREATE TABLE tablename (
    date date,
    amount int
);

INSERT INTO tablename VALUES
('2021-01-01',  100),
('2021-01-15',  200),
('2021-01-31',  300),
('2021-02-01',  400),
('2021-02-15',  500),
('2021-02-28',  600),
('2021-03-01',  700),
('2021-03-02',  800),
('2021-03-03',  900),
('2021-03-04',  1000);

Query #1

select date,amount from 
(
  select date,amount ,
  row_number()over (partition by (extract (year from date)),(extract (month from date )) order by date desc)RN
       from tablename
)T
WHERE RN=1;
date amount
2021-01-31T00:00:00.000Z 300
2021-02-28T00:00:00.000Z 600
2021-03-04T00:00:00.000Z 1000

View on DB Fiddle

Upvotes: 0

Related Questions