Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
1) you don't need the calendar to be stored with the fact table, this was only for testing if working days match corresponding week days, so you can drop this table
2) right, I modified the script to fix this
Hope this helps!
Best
Stefan
Yes, the Logic is now working Perfectly for all scenarios. No issues left.
This is something i called as incredible stuff.......
Many-2 thanks to you man. You are the Master. Thanks for devoting your valuable time and mind into my issue. It was pleasure working with you. I have learnt many new things from you which I'll remember & keep with me for a Lifetime.
Regards
Av7eN
Hi,
thank you for your compliments. But please open a new topic for this issue, so others will help you
Best regards
Stefan
Hi Stefan Sir
I need your help on this post:
Exclude Holidays & their Time from Running Logic...............!!
If possible, kindly look into this.
Regards
Av7eN