Zack Zack - 4 months ago 12
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

Answer
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') 

Select DateR1=min(Start_Date),DateR2=DateAdd(DD,DateDiff(DD,min(Start_Date),max(End_Date))*.9,min(Start_Date))
 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