?
Solved

Last 12 months of data

Posted on 2016-09-16
2
Medium Priority
?
64 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 49

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 49

Accepted Solution

by:
PortletPaul earned 2000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

801 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