Hi,
How do I create one table for all reportig objects using system date.
I need this logic to be implemented in my database using SQL for reporting purpose.
Technologies I use like tableau, business objects etc.
I need, last 24 hours, today, yesterday, last 6 months, end of the day last month and first day previous month etc.
Thank You.
I got this
select
trunc(trunc(sysdate,'MM')-1,'MM') AS FIRSTDAYLASTMONTH,
trunc(sysdate,'MM')- 1/(24*60*60) AS ENDOFLASTDAYLASTMONTH,
trunc(sysdate,'MM') AS FIRSTDAYTHISMONTH,
trunc(trunc(add_months(sysdate,1),'MM'),'MM') - 1/(24*60*60) AS ENDOFLASTDAYTHISMONTH,
trunc(trunc(add_months(sysdate,1),'MM'),'MM') AS FIRSTDAYNEXTMONTH,
trunc(trunc(add_months(sysdate,2),'MM'),'MM') - 1/(24*60*60) AS ENDOFLASTDAYNEXTMONTH,
trunc(trunc(add_months(sysdate,-6),'MM'),'MM') AS FIRSTDAY6MONTHSAGO,
trunc(trunc(add_months(sysdate,-12),'MM'),'MM') AS FIRSTDAY12MONTHSAGO,
add_months(trunc(sysdate),+6)-(1/24/60/60) AS ENDOFDAY6MONTHSFROMNOW,
CASE WHEN rtrim(to_char(trunc( SYSDATE) ,'Day')) = 'Monday'
then NEXT_DAY(trunc(sysdate-4),'FRIDAY')+.8333333
WHEN rtrim(to_char(trunc( SYSDATE) ,'Day')) = 'Tuesday'
then NEXT_DAY(trunc(sysdate-5),'FRIDAY')+.8333333
WHEN rtrim(to_char(trunc( SYSDATE) ,'Day')) = 'Wednesday'
then NEXT_DAY(trunc(sysdate-6),'FRIDAY')+.8333333
WHEN rtrim(to_char(trunc( SYSDATE) ,'Day')) = 'Thursday'
then NEXT_DAY(trunc(sysdate-7),'FRIDAY')+.8333333
WHEN rtrim(to_char(trunc(SYSDATE),'Day')) = 'Friday'
then NEXT_DAY(trunc(sysdate-8),'FRIDAY')+.8333333
WHEN rtrim(to_char(trunc(SYSDATE) ,'Day')) = 'Saturday'
then NEXT_DAY(trunc(sysdate-9),'FRIDAY')+.8333333
WHEN rtrim(to_char(trunc(SYSDATE) ,'Day')) = 'Sunday'
then NEXT_DAY(trunc(sysdate-10),'FRIDAY')+.8333333
end AS ReleaseWeekendStart,
CASE WHEN rtrim(to_char(trunc( SYSDATE) ,'Day')) = 'Monday'
then trunc(sysdate ) +(.25)-(.000011574)
WHEN rtrim(to_char(trunc( SYSDATE) ,'Day')) = 'Tuesday'
then NEXT_DAY( trunc(sysdate-2) ,'MONDAY')+(.25)-(.000011574)
WHEN rtrim(to_char(trunc( SYSDATE) ,'Day')) = 'Wednesday'
then NEXT_DAY(trunc(sysdate-3) ,'MONDAY')+(.25)-(.000011574)
WHEN rtrim(to_char(trunc( SYSDATE) ,'Day')) = 'Thursday'
then NEXT_DAY( trunc(sysdate-4) ,'MONDAY')+(.25)-(.000011574)
WHEN rtrim(to_char(trunc( SYSDATE) ,'Day')) = 'Friday'
then NEXT_DAY( trunc(sysdate-5) ,'MONDAY')+(.25)-(.000011574)
WHEN rtrim(to_char(trunc( SYSDATE) ,'Day')) = 'Saturday'
then NEXT_DAY( trunc(sysdate-6),'MONDAY')+(.25)-(.000011574)
WHEN rtrim(to_char(trunc( SYSDATE) ,'Day')) = 'Sunday'
then NEXT_DAY(trunc(sysdate-7),'MONDAY')+(.25)-(.000011574)
end AS ReleaseWeekendEnd,
trunc(sysdate) AS STARTOFTODAY,
sysdate-1 as within_last_24_hrs,
sysdate-(1/24) as within_last_1_hr,
(trunc(sysdate)+15) - (.000011574) as endofday2weeksfromnow,
TRUNC (next_day (sysdate-(7 + to_number(to_char(trunc(sysdate),'d'))), 'SUN')) as prev_week_sun,
TRUNC (next_day (sysdate-(7 + to_number(to_char(trunc(sysdate),'d'))), 'MON')) as prev_week_mon,
TRUNC (next_day (sysdate-(7 + to_number(to_char(trunc(sysdate),'d'))), 'TUE')) as prev_week_tue,
TRUNC (next_day (sysdate-(7 + to_number(to_char(trunc(sysdate),'d'))), 'WED')) as prev_week_wed,
TRUNC (next_day (sysdate-(7 + to_number(to_char(trunc(sysdate),'d'))), 'THU')) as prev_week_thu,
TRUNC (next_day (sysdate-(7 + to_number(to_char(trunc(sysdate),'d'))), 'FRI')) as prev_week_fri,
TRUNC (next_day (sysdate-(7 + to_number(to_char(trunc(sysdate),'d'))), 'SAT')) as prev_week_sat,
TRUNC (next_day (sysdate-(to_number(to_char(trunc(sysdate),'d'))), 'SUN')) as curr_week_sun,
TRUNC (next_day (sysdate-(to_number(to_char(trunc(sysdate),'d'))), 'MON')) as curr_week_mon,
TRUNC (next_day (sysdate-(to_number(to_char(trunc(sysdate),'d'))), 'TUE')) as curr_week_tue,
TRUNC (next_day (sysdate-(to_number(to_char(trunc(sysdate),'d'))), 'WED')) as curr_week_wed,
TRUNC (next_day (sysdate-(to_number(to_char(trunc(sysdate),'d'))), 'THU')) as curr_week_thu,
TRUNC (next_day (sysdate-(to_number(to_char(trunc(sysdate),'d'))), 'FRI')) as curr_week_fri,
TRUNC (next_day (sysdate-(to_number(to_char(trunc(sysdate),'d'))), 'SAT')) as curr_week_sat
from dual