August 23, 2016 at 1:30 am
Hi Team,
I would like to find out the percentage for the delay only, I have the ScheduledDate and if do the activity on or before then i can say my
achievement is 100 % (Please refer row number 2,3) but if i delay it (Please refer row number 1,4,5) then i have to find out delay in %. On or before i could find it using CASE statement because NoOfDays is 0 or less i can say its 100%
ScheduledDate CompletedDate NoOfDaysMonthYearPercentage
2016-06-13 00:00:00.0002016-06-14 00:00:00.0001 JUNE2016
2016-06-13 00:00:00.0002016-06-13 00:00:00.0000 JUNE2016100%
2016-06-02 00:00:00.0002016-06-01 00:00:00.000-1 JUNE2016100%
2016-06-08 00:00:00.0002016-06-10 00:00:00.0002 JUNE2016
2016-06-17 00:00:00.0002016-06-20 00:00:00.0003 JUNE2016
Please help me
August 23, 2016 at 1:39 am
shagil.a.gopinath (8/23/2016)
Hi Team,I would like to find out the percentage for the delay only, I have the ScheduledDate and if do the activity on or before then i can say my
achievement is 100 % (Please refer row number 2,3) but if i delay it (Please refer row number 1,4,5) then i have to find out delay in %. On or before i could find it using CASE statement because NoOfDays is 0 or less i can say its 100%
ScheduledDate CompletedDate NoOfDaysMonthYearPercentage
2016-06-13 00:00:00.0002016-06-14 00:00:00.0001 JUNE2016
2016-06-13 00:00:00.0002016-06-13 00:00:00.0000 JUNE2016100%
2016-06-02 00:00:00.0002016-06-01 00:00:00.000-1 JUNE2016100%
2016-06-08 00:00:00.0002016-06-10 00:00:00.0002 JUNE2016
2016-06-17 00:00:00.0002016-06-20 00:00:00.0003 JUNE2016
Please help me
how do you want the percentage to be calculated if you have a delay... you havent provided your expected results.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 23, 2016 at 1:57 am
Thanks for the reply,
According to given scenario I have time till end of June, % should be calculated by considering the scheduled date, Completed Date. In the row number 1 delay happened for 1 day so % could be 97% achievement, row number 5 delay happened for 3 days so % could be 91% achievement. In fact i cant say what formula to be used for the same purpose, I have used CASE statement to find for on or before activity since its considered as 100%
(CASE WHEN DATEDIFF(DAY, ScheduledDate , CompletedDate) >0 THEN 'Need a solution' WHEN DATEDIFF(DAY, ScheduledDate, CompletedDate) <= 0 THEN 100 END) as Percentage
August 23, 2016 at 2:10 am
shagil.a.gopinath (8/23/2016)
Thanks for the reply,According to given scenario I have time till end of June, % should be calculated by considering the scheduled date, Completed Date. In the row number 1 delay happened for 1 day so % could be 97% achievement, row number 5 delay happened for 3 days so % could be 91% achievement. In fact i cant say what formula to be used for the same purpose, I have used CASE statement to find for on or before activity since its considered as 100%
(CASE WHEN DATEDIFF(DAY, ScheduledDate , CompletedDate) >0 THEN 'Need a solution' WHEN DATEDIFF(DAY, ScheduledDate, CompletedDate) <= 0 THEN 100 END) as Percentage
hmmmm ....its a little difficult to provide you with a solution if you dont know the expected results....but based on what you have said above try the following
(CASE WHEN DATEDIFF(DAY, ScheduledDate , CompletedDate) >0
THEN 100 - (DATEDIFF(DAY, ScheduledDate, CompletedDate)*3)
ELSE 100 END) as Percentage
edit...please understand that this will give negative results if datediff is greater than 33 days
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 23, 2016 at 2:24 am
Thank you much, I will check the result for accuracy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy