Logic Error

[aveeeeeee7en]

Hi All

I am doing Calculation of Numbers based on Logic.

The Correct Calculation are Green in Color and Wrong Calculation is Red in Color.

I am calculation Time Difference between CreationTime & OutTime based on:

The Creation Time & End Time should be considered between StartTime & EndTime.

I am explaining 2 scenarios how the Calculation is done:

Case No.1:

It's Creation Time is  25/3/2014 1:11:24 PM

&   OutTime             is   31/3/2014 7:40:00 PM

If you Reload Attached qvd DataMainData, than you'll see its Working weekdays are different for individual Numbers.

For some of the cases Working Weekdays are Mon - Fri

& For some of the cases Working Weekdays are Mon - Sat

It's Working Weekdays are Mon - Fri

Also, calculate difference b/w OutTime & CreationTime by considering Start Time & End Time i.e. different individual Numbers

25 = 4:18:36 hrs ( Hours b/w 9:00 to 17:30)   Calculation=(17:30 - 1:11:24 = 4:18:36)

26 = 8:30 hrs  ( Hours b/w 9:00 to 17:30)   Calculation=(17:30 - 9:00= 8:30:00)

27 = 8:30 hrs  ( Hours b/w 9:00 to 17:30)   Calculation=(17:30 - 9:00= 8:30:00)

28 = 8:30 hrs  ( Hours b/w 9:00 to 17:30)  Calculation=(17:30 - 9:00= 8:30:00)

29 = 0 hrs   ( Saturdayto be excluded since Working Weekdays are Mon - Fri)

30 = 0 hrs  ( Sunday to be excluded since Working Weekdays are Mon - Fri)

31 = 8:30 hrs  ( Hours b/w 9:00 to 17:30)  Calculation=(17:30 - 9:00= 8:30:00)

Sum of All = 4:18:36 hrs + 8:30 hrs + 8:30 hrs + 8:30 hrs + 8:30 hrs = 38:18:36 hrs

Case No.2:

It's Creation Time is  30/3/2014 11:46:39 AM

&   OutTime             is   5/4/2014 5:00:00 PM

If you Reload Attached qvd DataMainData, than you'll see its Working weekdays are different for individual Numbers.

For some of the cases Working Weekdays are Mon - Fri

& For some of the cases Working Weekdays are Mon - Sat

It's Working Weekdays are Mon - Sat

Also, calculate difference b/w OutTime & CreationTime by considering Start Time & End Time i.e. different individual Numbers

30 = 0 hrs  ( Sunday to be excluded since Working Weekdays are Mon - Sat)

31 = 8:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(18:00 - 9:30 = 8:30:00)

1  = 8:30 hrs ( Hours b/w 9:30 to 18:00)  Calculation=(18:00 - 9:30 = 8:30:00)

2  = 8:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(18:00 - 9:30 = 8:30:00)

3  = 8:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(18:00 - 9:30 = 8:30:00)

4 = 8:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(18:00 - 9:30 = 8:30:00)

5  = 7:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(5:00 - 9:30 = 7:30:00)

Sum of All = 8:30 hrs+ 8:30 hrs + 8:30 hrs + 8:30 hrs + 8:30 hrs + 7:30 hrs = 50:00:00 hrs

Kindly, someone help. It's a tricky logic.

Regards

Av7eN

[#2]

Do you have any other table have which week are Mon-Sat working days ?

[aveeeeeee7en]

Hi

Below is the Snapshot for following cases:

1) Monday - Friday

2) Monday - Saturday

Also, see the Attachment.

[Anonymous]

Hi,

I've put something together which solves your problem.

Hope this helps!

Kind regards

Stefan

[aveeeeeee7en]

Hi

Thanks Stefan.

In one case - 1399974 I am getting problem. Please help on this.

As per your Logic it's answer is coming as 10:23 but the correct answer should come as 1:00

Regards

Av7eN

[Anonymous]

Hi,

can't test this as your testdata doesnt have this Ticket, but try modifying the part of the script where all is summed  up like this:

NumSum(

         pick(WeekDay6Flag + 1,

      if((WeekDay <= 5 and WeekDay >= 1) and [Cal Date] < EndDay and [Cal Date]> StartDay, WorkingHours),

      if( (WeekDay <= 6 and WeekDay >= 1) and [Cal Date] < EndDay and [Cal Date]> StartDay, WorkingHours)

      ) ,

      pick(WeekDay6Flag + 1, if( (WeekDay <= 5 and WeekDay >= 1) and [Cal Date]= StartDay and [Cal Date] <> EndDay , HoursStartDay2),

                              if((WeekDay <= 6 and WeekDay >= 1) and [Cal Date]= StartDay and [Cal Date] <> EndDay, HoursStartDay2 )

                                      ) ,

      pick ( WeekDay6Flag +1,

          if((WeekDay <= 5 and WeekDay >= 1) and [Cal Date]= EndDay, HoursEndDay2),

        if((WeekDay <= 6 and WeekDay >= 1) and [Cal Date]= EndDay, HoursEndDay2)

                                              )

     ) as SumWorkingHours

Hope this helps!

Best

Stefan

[aveeeeeee7en]

Sir

Your Previous Logic was perfect

One Last Question hopefully.

The 2 cases '1189675' and '1174767'  I have tested which have Window from Mon-Sun 24*7 so in that case no need to exclude anything from such cases.

Some More cases which are causing problem are:

'1255249','1473485','1261332','1196672'

Also, when I am adding my All cases Qvd (Latest_Final_Main_Table.qvd ) into your Logic, than it got hanged. Is this happening due to generated calendar or due to large Records.

Kindly help.

PFA.

Regards

Av7eN

[aveeeeeee7en]

Causing Problem:

[Anonymous]

Hi,

didn't had the cases in my mind when Tickets are solved on one day, added some code to cover these. (See qvw for this).

However, the missing two minutes probably result from the enddate of 23:59. Actually there are 59 Seconds missing, so the result shoud be like 47:17:01 but this is not captured by your data.

Your "big" qvd fails because there are some fields missing in this table you have to remove them from the script.

Hope this helps!

Best regards

Stefan

[aveeeeeee7en]

Hi Stefan

Your Logic is working.

But I have 2 issues:

1) When I am testing with only October Data than the Logic is getting Hanged.

2) The Case - '1399974' is not coming as per the new Logic.

Thanks

Av7eN