Solved

Last 12 months of data

Posted on 2016-09-16
2
51 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
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…

830 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