Zack - 2 months ago 5x
SQL Question

Calculating 90th Percentile between two dates

Hi I have a table with 4 columns out of which 2 are date columns I need find the difference between those and then find the 90th percentile

Table A

``````ID,Name,Start_Date,End_Date
1,abc,04/15/2014,04/16/2014
2,xyz,05/13/2014,05/13/2014
3,afd,05/13/2014,05/14/2014
4,rfd,05/15/2014,05/16/2014
5,grr,06/15/2014,06/16/2014
``````

Any help or pointers are highly appreciated

``````Declare @Table table (ID int, Name varchar(50),Start_Date Date,End_Date Date)
Insert into @Table values
(1,'abc','04/15/2014','04/16/2014'),
(2,'xyz','05/13/2014','05/13/2014'),
(3,'afd','05/13/2014','05/14/2014'),
(4,'rfd','05/15/2014','05/16/2014'),
(5,'grr','06/15/2014','06/16/2014')

From  @Table
``````

Returns

``````DateR1      DateR2
2014-04-15  2014-06-09
``````

So to return the records

``````Select A.*
From  @Table A
Join (Select DateR1=min(Start_Date),DateR2=DateAdd(DD,DateDiff(DD,min(Start_Date),max(End_Date))*.9,min(Start_Date)) From  @Table) B
on (A.Start_Date Between B.DateR1 and B.DateR2)
``````

Returns 4 of the 5 original records

``````ID  Name    Start_Date  End_Date
1   abc     2014-04-15  2014-04-16
2   xyz     2014-05-13  2014-05-13
3   afd     2014-05-13  2014-05-14
4   rfd     2014-05-15  2014-05-16
``````