moe moe - 3 months ago 7
SQL Question

How to get last month's date from specific date in postgressql

I have a max date in my query and If the max date is current month then I want always get previous month’s date. How can I do that?

For example, today is

20160825
. If the max date is
20160801
then I want to get
20160701
. But, if the max date is
20160501
then I just want to get without changes -
20160501
.

SELECT
DEFN_DK,
MAX(SNAPSHOT_MTH)
FROM myTable
WHERE TOT_AMT >0
GROUP BY DEFN_DK

Answer

Since your SNAPSHOT_MTH column is an integer (why?) you can not use any of the otherwise very useful timestamp functions. So it's back to integer math, creating a "month" from your snapshot "date" through integer division by 100. This can be compared to CURRENT_DATE by converting that to a string and then casting it to an integer. Going back 1 month similarly requires some math. Not entirely efficient, but here goes:

SELECT DEFN_DK, 
       CASE max(SNAPSHOT_MTH) / 100
           WHEN tochar(CURRENT_DATE, 'YYYYMM')::int THEN 
               ((max(SNAPSHOT_MTH) / 100) - 1) * 100 + 1
               -- or max(SNAPSHOT_MTH) - 100, if you know it always ends in 01
           ELSE max(SNAPSHOT_MTH)
       END AS SNAPSHOT_MTH
FROM myTable 
WHERE TOT_AMT > 0
GROUP BY DEFN_DK;
Comments