# How to find % between two dates for the delay

• 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

• 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

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

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

• shagil.a.gopinath (8/23/2016)

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

• Thank you much, I will check the result for accuracy

Viewing 5 posts - 1 through 4 (of 4 total)