Link to home
Start Free TrialLog in
Avatar of Star79
Star79Flag for United States of America

asked on

Oracle Date

Hello All,
I have the below query which pulls  data from current date to 12 months back..
with tx_amd_fsi as (
  select dim_product_id
         ,sum(quantity) as quantity
  from fact_salesitem
  where dim_org_id in (select dim_org_id from dim_org where org_name like 'TSI%' and org_name <> 'TSI FMS')
  and dim_whse_bin_id = (select dim_whse_bin_id from dim_whse_bin where WHSE_CODE = 'EXE795')
  and ordered_date_id in (select dim_date_id from dim_date where thisdate >= add_months(TRUNC(sysdate,'MM'), -12) and thisdate <  add_months(TRUNC(sysdate,'MM'), 0))
    and fact_salesitem.DLA_STATUS not in('Cancelled','AL')
    and purch_req  NOT LIKE 'XFR%' AND purch_req  NOT LIKE 'AXR%'
  group by dim_product_id
)
select * from  tx_amd_fsi;

Open in new window

I want to change the above code date range to run from the last day of the previous month and back 12 months.
Please help.
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

Use the last_day function.  It gives you the last day of the current month.

So for last month:

select add_months(last_day(sysdate), -1) from dual;

(Sorry don't have the time at the moment to put that into your query but that should give you something to work with)
Avatar of Sean Stuber
Sean Stuber

you almost have it already.

ADD_MONTHS(TRUNC(sysdate,'MM',0)   is the same as TRUNC(sysdate,'MM')  which is the 1st of the current month.
So, to get the last day of the previous month, simply subtract 1 day


thisdate <= TRUNC(sysdate,'MM') - 1

of course, that assumes all of your data is in truncated days.

If it's not, if you might have 2017-03-31 23:59:59  then  that is part of the last day of the previous month (for today anyway)

So,  what your have is correct except the add_months(...,0) is superfluous

thisdate < TRUNC(sysdate,'mm')  will give you everything prior to the current month (including the last day of the previous month)
Actually, it is quite easy to get to 23:59:59 on the last day of the previous month.

TRUNC(SYSDATE, 'mm') - (1/86400)

Also, keep in mind that ADD_MONTHS and LAST_DAY functions do not touch the time portion of the date, so the time portion of what is returned would be the current time, with the new date.  I would also be careful about using LAST_DAY before ADD_MONTHS.  There used to be issues with that.  Like if it was February and you do LAST_DAY, that is 2/28.  Then you do ADD_MONTHS you may get 1/28 not 1/31.  You should do:

LAST_DAY(ADD_MONTHS(sysdate, -1))
Avatar of Star79

ASKER

Using ststuber comment above, can I use :
with tx_amd_fsi as (
  select dim_product_id
         ,sum(quantity) as quantity
  from fact_salesitem
  where dim_org_id in (select dim_org_id from dim_org where org_name like 'TSI%' and org_name <> 'TSI FMS')
  and dim_whse_bin_id = (select dim_whse_bin_id from dim_whse_bin where WHSE_CODE = 'EXE795')
  and ordered_date_id in (select dim_date_id from dim_date where thisdate >= add_months(TRUNC(sysdate,'MM'),-12) and thisdate <=  add_months(last_day(sysdate), -1))
    and fact_salesitem.DLA_STATUS not in('Cancelled','AL')
    and purch_req  NOT LIKE 'XFR%' AND purch_req  NOT LIKE 'AXR%'
  group by dim_product_id
),
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Can you use that?  Yes, but see previous comment about using LAST_DAY before ADD_MONTHS.  Will it do what you want?  We don't know.  Why not check the 2 dates that you would be using with the simple query:

select add_months(TRUNC(sysdate,'MM'),-12), add_months(last_day(sysdate), -1) from dual;

The guess would be that you would either want to use

thisdate >= add_months(TRUNC(sysdate,'MM'),-12) and thisdate <=  trunc(sysdate,'mm')-(1/86400)

or

thisdate >= add_months(TRUNC(sysdate,'MM'),-12) and thisdate < trunc(sysdate, 'mm')