Solved

Last 12 months of data

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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select in Access 2003 3 48
Pivot tables in SQL 1 43
SQL Rewrite without the NULLIF 4 31
SQL Query Returns Records in SSMS but not Classic ASP 5 41
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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.

738 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