Question 114610 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL Query be changed to go back 14 months but include whole month based on now?

SELECT t.cora_acct_code, t.accountnumber, c.accounttype, t.accountingdate, t.postingamount, t.refer_30, t.control_30, t.control2_30, t.detaildescription 
FROM ed.gljedetail_v as t 
  LEFT JOIN ed.glcoa_v as c 
        on t.cora_acct_code = c.cora_acct_code 
       AND t.accountnumber = c.accountnumber 
WHERE t.cora_acct_code = 'BMW-A' 
  AND t.accountingdate <= now() 
  AND t.accountingdate >= now() - INTERVAL '14 MONTHS' 
  AND c.accounttype = 'E' 
ORDER BY t.accountingdate
  1. My current code up top. Basically it is taking today or now into account however, I only get partial month of July when I would like it to actually pull the whole 14 months back up till this point. Is there a way to make this work?
  2. I want the code to recognize the now, but automatically give the full month of the previous months and the month to date of the current month (September)
    Also I apologize if my code sucks I am pretty new to this SQL business!!
Tags:

3

Answers


  1. 0

    1. This is pretty hard to read. You should start putting your SQL on multiple lines.

    2. The part that reads now() - INTERVAL '14 MONTHS' can be wrapped inside a Date_Trunc function:

      date_trunc(”month", now()- INTERVAL 14 MONTHS)

    Login or Signup to reply.
  2. 0

    You could truncate the current month and then look back:

    AND t.accountingdate >= date_trunc('month', now()) - interval '14 months'
    Login or Signup to reply.
  3. 0

    Yes, do what the other answerers said. Date_trunc("month",x) function returns the first day in the month selected. So instead of t.accountingdate >= 7/14/2021. It is reading t.accountingdate >= 7/1/2021.

    SELECT t.cora_acct_code, 
     t.accountnumber, 
     c.accounttype, 
     t.accountingdate, 
     t.postingamount, 
     t.refer_30, 
     t.control_30, 
     t.control2_30, 
     t.detaildescription 
 FROM ed.gljedetail_v as t 
    LEFT JOIN ed.glcoa_v as c 
            on t.cora_acct_code = c.cora_acct_code 
           AND t.accountnumber = c.accountnumber 
    WHERE t.cora_acct_code = 'BMW-A' 
      AND t.accountingdate <= now() 
      AND t.accountingdate >= date_trunc('month', now() - INTERVAL '14 MONTHS') 
      AND c.accounttype = 'E' 
    ORDER BY t.accountingdate
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search