How do I create a t...
Clear all

How do I create a table for all reporting objects using system date?

2 Posts
1 Users
KV Kumar
Posts: 27
Topic starter
Eminent Member
Joined: 3 years ago


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.

1 Reply
KV Kumar
Posts: 27
Topic starter
Eminent Member
Joined: 3 years ago

I got this



  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



Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved