CODEWITHSUNDEEP
postgresql
Tamil Selvan
Tamil Selvan

Reputation: 71

How to select month end data in PostgreSQL?

I'm working on the Accrual Reversal query in PostgreSQL. The system running doesn't have the reversal flag. So I need to consider all the end of the day of previous month accrued invoices as the reversal amount. And need to union them all with the main query. I can do it for last month but invoice date are dynamic, user may give 2 years as invoice period. For those 2 years, all the previous month data should be considered as accrued reversal. Here is the query

select invoicename, * from accountpay where invoice_date between '2020-01-01' and '2021-12-31'
union all
select concat('Accured Reversal', invoicename) as reference, * from accountpay where accrual = true and invoice_date::date = (select concat(date_part('year',((('2021-12-30'::date) - interval '1 month'))), '-', date_part('month',((('2021-12-30'::date) - interval '1 month'))), '-01')::date + interval '1 month' -  interval '1 day')

Please help me to do this.

Thanks in Advance

Upvotes: 0

Views: 440

Answers (2)

jian
jian

Reputation: 4877

SELECT (
    Date_trunc('MONTH',a) + interval '1 month -1 day ') 
            as last_day_of_month
       FROM   generate_series(
           '2020-01-01 00:00'::timestamp 
           - interval '12 months',
           '2022-01-01 00:00', 
           '1 month') as dt(a);

get last_day_of_month from '2020-01-01 00:00' till '2022-01-01 00:00' Then your sql would be

invoice_date in 
 (SELECT (Date_trunc('MONTH',a) + interval '1 month -1 day ') 
            as last_day_of_month
       FROM   generate_series(
           '2020-01-01 00:00'::timestamp 
           - interval '12 months',
           '2021-01-01 00:00', 
           '1 month') as dt(a))

Upvotes: 1

rajorshi
rajorshi

Reputation: 717

This will get the last day of last 12 months so the number of months will be place holder (dynamic) and all the last day of the months will be in IN clause.

SQL re-written:

WITH date_cte AS
(
       SELECT Date_trunc('MONTH',dt)+ interval '1 month -1 day ' last_day_of_month
       FROM   generate_series('2021-11-30 00:00:00'::timestamp - interval '12 months','2021-11-30 00:00:00','1 month') t(dt))
select invoicename, * from accountpay where invoice_date between '2020-01-01' and '2021-12-31'
union all
select concat('Accured Reversal', invoicename) as reference, * from accountpay where accrual = true and invoice_date::date in (select * from date_cte);

Basically , the last dates are generated this way for 12 months:

WITH date_cte AS
(
       SELECT Date_trunc('MONTH',dt)+ interval '1 month -1 day ' last_day_of_month
       FROM   generate_series('2021-11-30 00:00:00'::timestamp - interval '12 months','2021-11-30 00:00:00','1 month') t(dt))
SELECT *
FROM   date_cte;

  last_day_of_month
---------------------
 2020-11-30 00:00:00
 2020-12-31 00:00:00
 2021-01-31 00:00:00
 2021-02-28 00:00:00
 2021-03-31 00:00:00
 2021-04-30 00:00:00
 2021-05-31 00:00:00
 2021-06-30 00:00:00
 2021-07-31 00:00:00
 2021-08-31 00:00:00
 2021-09-30 00:00:00
 2021-10-31 00:00:00
 2021-11-30 00:00:00

You can replace 12 months by any number of months or you can make it year too like:

...generate_series('2021-11-30 00:00:00'::timestamp - interval '1 year','2021-11-30 00:00:00','1 month')

Upvotes: 1

Related Questions