Solved

Last 12 months of data

Posted on 2016-09-16
2
46 Views
Last Modified: 2016-09-19
Hi All,

I am using the following code in my query:

       WHERE prt.tr_date BETWEEN TRUNC (ADD_MONTHS (SYSDATE, -13), 'MM')  
       AND TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1))+1)

It works. But when I run the report today (9/16) and look at the dates its pulling, its starting from 8/22/15 to 8/26/16.
Shouldn't the code be pulling from 9/5/15 (first September date) to today 9/16/16?
0
Comment
Question by:metalteck
  • 2
2 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41802673
You are deducting 13 months from sysdate, that why it goes back 1 month more than expected.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41802699
Perhaps this will help you choose what you need:
select a, b, c, gt_date, lt_date, lt_date - gt_date, gt_mnth, lt_mnth, lt_mnth - gt_mnth
from (
select
     sysdate a
   , trunc(sysdate) b
   , trunc(sysdate, 'mm') c
   , add_months(trunc(sysdate),-12) gt_date
   , add_months(trunc(sysdate),0)  lt_date
   , add_months(trunc(sysdate, 'mm'),-12) gt_mnth
   , add_months(trunc(sysdate, 'mm'),0)  lt_mnth
from dual
)
;

Open in new window

I would NOT USE BETWEEN, instead I would use >= with < like this:

       WHERE ( prt.tr_date >= add_months(trunc(sysdate, 'mm'),-12)
            AND   prt.tr_date < trunc(sysdate)
                     )

i.e. if sysdate is sometime in Sep 17 2016, then that would equate to

       WHERE prt.tr_date >= 2015-09-17
       AND prt.tr_date < 2016-09-17 -- LESS THAN today

(which is exactly 1 year, and happens to be 366 days)

For more on between in date ranges please see: "Beware of Between"
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question