Joining Date and time and then finding the difference in Days

[antexity]

Hi,

I am trying to calculate lead time in Days (eg. 5 days etc..)

Date is laid out in this fashion.

Order #	Status	Date	Lead Time
2	25	12/15/11	TBD
2	25	12/18/11	TBD
2	99	12/26/11	TBD
3	25	12/25/11	TBD
3	99	12/28/11	TBD

My requirements are to calculate leadtime which is to take latest status 99 of the same order #  [Date] subtract the latest status 25 of the same order #[Date] into days format eg. 5 days.  That part I understand but then to throw in the mix, if the latest status 25 is submitted on the weekend  (Saturday-Sunday) then the lead time calculation Status 25 must start calculating on Monday (see Order # 2). As well if the process starts during the business week and carried thru the weekend and Status 99 ends during the following business week.  The leadtime must not take account the two days on the weekend.  Eg.  Status 25 is on a wed and 99 is completed on a thurs the following week, technically that is 9 days, but since its during the weekend the leadtime should be 7 days.  This is where I am having troubles calculating the lead time formula.

Leadtime (Days) = Latest Status 99 Date - Latest Status 25 Date    (Not counting Sat or suns)

Can this be done?

Thanks for your time.

[blom0344]

A possible solution would be to:

Step1:

Define 2 seperate queries that fetch the latest date for status (1 for 25, 1 for 99) using a simple maximum aggregate on the date.

Step2:

After joining locally in the report define the calculation in the resulting queryset by using the _day_of_week function in an extended case statement.

The case should include a check where any of the 2 dates falls in the weekend, but also whether a weekend is passed between 2 dates. (provided you do not need to provide for an even wider range)

As usual I advocate using a calender table for temporal calculations. Especially for skipping weekends/holidays in the type of report you need to build it is very handy to have an additional table that stored calculated indexes for this purpose.

[antexity]

Thanks for the response,  I got step one completed, regarding step two,  are there some examples on COGNOiSe that you can forward me to?  or an example?

Thank you!

[antexity]

Finally got it all working except for one small item.   Somehow my maximum function is not taking the max date of status 25.

For a certain order number i have two Status 25 entries and one 99 entry.     Total days should be 2 days but it is displaying 32.   32 makes sense if its calculating the first status 25 entry  but it should be taking the 2nd 25 entry.

I think I am missing something simple. 

I tried attaching screenshots but not working, so here is how I have it setup.

q1  >  Joined q2 and q3

q1 contains total days with a formula that counts working days only

q2 and q3 are the same except for each status  25 and 99

Each one contains:
Order number
Date
Status Code

Date - detailed filter - [Date - Beginning Effective]=maximum([Date - Beginning Effective] FOR [Order Number], [Work Order Status Code])
Status Code - Detailed Filter - [Work Order Status Code] = '25'
and my prompt


and same for Status 99,

Any ideas?

Thanks again.
Jonathan

Thank you!
Jonathan

[venkys4u_]

I hope ur trying to calculate working days alone between two date range but with maximum date for the status..

if im correct,

have a seperate query to and use maximum of date group by status and order if u want..
join with date,status to the main query..

perform this in main query

Include a Query Calculation in your report with the following syntax:

((cast(_days_between(?EndDate?,1900-01-01)/7,integer)*5)+if(_day_of_week(?EndDate?,1)>5) then (4) else (mod(_days_between(?EndDate?,1900-01-01),7))+1)
-
((cast(_days_between(?StartDate?,1900-01-01)/7,integer)*5)+if(_day_of_week(?StartDate?,1)>5) then (4) else (mod(_days_between(?StartDate?,1900-01-01),7))+1)
+
(if(_day_of_week(?StartDate?,1)<6) then (1) else (0))

Note: The expression above uses Cognos functions. If you prefer, you can substitute for your database's equivalent functions, assuming they exist.

FYI:https://www-304.ibm.com/support/docview.wss?uid=swg21342343

and

Problem(Abstract)How do you calculate the number of working days in a date range?

Resolving the problemUse the following calculation.

Steps -

Working Days from 1900 to Start Date:

( integer-divide ( date-to-days-from-1900 ( Start Date ) , 7 ) * 5 ) + ( mod ( date-to-days-from-1900 ( Start Date ) , 7 ) )

Working Days from 1900 to End Date:

( integer-divide ( date-to-days-from-1900 ( End Date ) , 7 ) * 5 ) + ( mod ( date-to-days-from-1900 ( End Date ) , 7 ) )

Working Days from Start Date to End Date Inclusive:

if ( mod ( date-to-days-from-1900 ( End Date ) , 7 ) = 6) then (Working Days from 1900 to End Date - Working Days from 1900 to Start Date)
else ( Working Days from 1900 to End Date - Working Days from 1900 to Start Date + 1 )


gud luck
venky

[blom0344]

Interesting post, does this relate to Cognos8? 

[antexity]

Thanks for the info, most of it works for me in Cognos 8:

now I have another question:,  How would I calculate if I had three statuses, basically there are two scenarios

- Calculate date difference if status is 25 (Date - Status 99 - Date - Status 25)
- Calculate date difference if status is 35 (Date - Status 99 - Date - Status 35)

I made three queries calculating the max date for each status, 25,35,and 99

Now in the Main query is where I cant get it to work, basically what I need is If status is 35 then ((cast(_days_between(?EndDate?,1900-01-01)/7,integer)*5)+if(_day_of_week(?EndDate?,1)>5) then (4) else (mod(_days_between(?EndDate?,1900-01-01),7))+1)
-
((cast(_days_between(?StartDate?,1900-01-01)/7,integer)*5)+if(_day_of_week(?StartDate?,1)>5) then (4) else (mod(_days_between(?StartDate?,1900-01-01),7))+1)
+
(if(_day_of_week(?StartDate?,1)<6) then (1) else (0))

OR

If status is 25 then ((cast(_days_between(?EndDate?,1900-01-01)/7,integer)*5)+if(_day_of_week(?EndDate?,1)>5) then (4) else (mod(_days_between(?EndDate?,1900-01-01),7))+1)
-
((cast(_days_between(?StartDate?,1900-01-01)/7,integer)*5)+if(_day_of_week(?StartDate?,1)>5) then (4) else (mod(_days_between(?StartDate?,1900-01-01),7))+1)
+
(if(_day_of_week(?StartDate?,1)<6) then (1) else (0))


How do I change the code to perform the If 25 then 99-25  or If 35 then 99-35

Thanks
Jon