Topic
  • 5 replies
  • Latest Post - ‏2013-07-17T22:15:58Z by OpherB
Goud2013
Goud2013
13 Posts

Pinned topic Calculating Tenth business day in report

‏2013-07-17T00:12:58Z |

Hello,

I have a requirement  in one of the report , when  user runs the  report on june 5th. since it is not yet 11th busineess day in june , so report should show April data.if user runs the report  on june 15th  2013 ,The 10th business day was June 14th and June 15th is a Saturday. Even though June 15th is not a business day, the report should be updated  with May data. Any body has any idea would be apprciated.DB2

 

Thanks!!!

  • OpherB
    OpherB
    664 Posts

    Re: Calculating Tenth business day in report

    ‏2013-07-17T16:54:28Z  

    You need to build a Calendar table in your data warehouse which has dates and a flag indicating if it is a business day or not. Even better would be a business day counter.

    No matter what logic you build to try to eliminate weekends, what about other non-business days, holidays, etc? Doing this in the report logic is not possible.

    Good luck,
    Opher

     

  • Goud2013
    Goud2013
    13 Posts

    Re: Calculating Tenth business day in report

    ‏2013-07-17T19:44:05Z  
    • OpherB
    • ‏2013-07-17T16:54:28Z

    You need to build a Calendar table in your data warehouse which has dates and a flag indicating if it is a business day or not. Even better would be a business day counter.

    No matter what logic you build to try to eliminate weekends, what about other non-business days, holidays, etc? Doing this in the report logic is not possible.

    Good luck,
    Opher

     

    thanks for reply  Opher

    In our datawarehouse, people created tenth business day column for every month (Excluding holidays and weekends) in table. By using this filed how can i implement in the report.Any idea.....

     

    Thanks!!!!!

    Updated on 2013-07-17T19:47:15Z at 2013-07-17T19:47:15Z by Goud2013
  • OpherB
    OpherB
    664 Posts

    Re: Calculating Tenth business day in report

    ‏2013-07-17T21:24:52Z  
    • Goud2013
    • ‏2013-07-17T19:44:05Z

    thanks for reply  Opher

    In our datawarehouse, people created tenth business day column for every month (Excluding holidays and weekends) in table. By using this filed how can i implement in the report.Any idea.....

     

    Thanks!!!!!

    What are the other columns in the table? Is it a regular Calendar table with all dates populated?

  • Goud2013
    Goud2013
    13 Posts

    Re: Calculating Tenth business day in report

    ‏2013-07-17T21:41:01Z  
    • OpherB
    • ‏2013-07-17T21:24:52Z

    What are the other columns in the table? Is it a regular Calendar table with all dates populated?

    other columns calender year, calender month name, calender month number, state fiscal year, state fiscal month number with this i have fiscal month code , biennium month number.

    Thanks!!

  • OpherB
    OpherB
    664 Posts

    Re: Calculating Tenth business day in report

    ‏2013-07-17T22:15:58Z  
    • Goud2013
    • ‏2013-07-17T21:41:01Z

    other columns calender year, calender month name, calender month number, state fiscal year, state fiscal month number with this i have fiscal month code , biennium month number.

    Thanks!!

    Most Calendar tables have every day of the year (that's the key) and then there are what Fiscal Month that date is, what Fiscal Year, what Quarter, etc., etc. In a setting like that, there would be a column for "business day of month" which would count off which business day each date in the month was. The report could then look at that table for the row with the current date, and the business day of month column would indicate if it was before or after the 10th and you could then do what was needed.

    It sounds like you have a row for JULY 2013 and somewhere is a column that has a value for 7/15/2013 as being the date of the 10th business day. Is that correct?

    So you'll need to take the current date, go through a bunch of calculations to get "JULY 2013" as a value to look for in the table and then find the date, which you will then compare to the current date and continue as above.

    Does that work for you?

    Good luck,
    Opher