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