Star79
asked on
Oracle Date
Hello All,
I have the below query which pulls data from current date to 12 months back..
Please help.
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;
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.
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)
ADD_MONTHS(TRUNC(sysdate,'
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(sysdat e, -1))
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(sysdat
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(sysdat e), -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
),
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,'
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(sysdat e), -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/864 00)
or
thisdate >= add_months(TRUNC(sysdate,' MM'),-12) and thisdate < trunc(sysdate, 'mm')
select add_months(TRUNC(sysdate,'
The guess would be that you would either want to use
thisdate >= add_months(TRUNC(sysdate,'
or
thisdate >= add_months(TRUNC(sysdate,'
So for last month:
select add_months(last_day(sysdat
(Sorry don't have the time at the moment to put that into your query but that should give you something to work with)