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!!

CodePudding user response：

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)

CodePudding user response：

You could truncate the current month and then look back:

AND t.accountingdate >= date_trunc('month', now()) - interval '14 months'

CodePudding user response：

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