tag:blogger.com,1999:blog-3682177839638728927.post1485223003702507006..comments2024-03-09T14:05:29.528+05:30Comments on SQL: Calculate PercentileAnonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.comBlogger15125tag:blogger.com,1999:blog-3682177839638728927.post-80660194619753149862016-01-04T10:35:07.417+05:302016-01-04T10:35:07.417+05:30Attacj the resultset into Report Grid.Attacj the resultset into Report Grid.Anonymoushttps://www.blogger.com/profile/09453699184523060639noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-82046046275633964562015-12-10T11:50:13.906+05:302015-12-10T11:50:13.906+05:30Fantastic, this gets the results i am after.. How ...Fantastic, this gets the results i am after.. How can this be setup in a view to report from ?Anonymoushttps://www.blogger.com/profile/12088175301239587132noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-24074046423000457472014-09-15T10:19:55.540+05:302014-09-15T10:19:55.540+05:30Yes it is possible with two Group. You had to Grou...Yes it is possible with two Group. You had to Group by two columns. Check below Example:<br /><br />Added one more column in table.<br /><br />DECLARE @Percentile FLOAT<br />SELECT @Percentile = .95;<br /><br />WITH Group_Row_Count(GroupName, GroupName1, Row_Count) AS<br />(<br /> SELECT GroupName, GroupName1, COUNT(*)<br /> FROM Employee<br /> GROUP BY GroupName, GroupName1<br /> HAVING COUNT(*) > 1<br />),<br />Emp_Sal(GroupName, GroupName1, Salary, Prev_Rank, Curr_Rank, Next_Rank) AS<br />(<br /> SELECT E.GroupName, E.GroupName1, E.Salary, <br /> (ROW_NUMBER() OVER ( PARTITION BY E.GroupName, E.GroupName1 ORDER BY E.Salary) - 2.0) / (C.Row_Count - 1), <br /> (ROW_NUMBER() OVER ( PARTITION BY E.GroupName, E.GroupName1 ORDER BY E.Salary) - 1.0) / (C.Row_Count - 1), <br /> (ROW_NUMBER() OVER ( PARTITION BY E.GroupName, E.GroupName1 ORDER BY E.Salary) + 0.0) / (C.Row_Count - 1)<br /> FROM Employee E <br /> JOIN Group_Row_Count C ON C.GroupName = E.GroupName AND C.GroupName1 = E.GroupName1<br />)<br />SELECT T1.GroupName, T1.GroupName1, <br /> CASE <br /> WHEN T1.Salary = T2.Salary THEN T1.Salary<br /> ELSE T1.Salary + (T2.Salary - T1.Salary) * ((@Percentile - T1.Curr_Rank) / (T2.Curr_Rank - T1.Curr_Rank)) <br /> END<br />FROM Emp_Sal T1 <br />JOIN Emp_Sal T2 ON T1.GroupName = T2.GroupName AND T1.GroupName1 = T2.GroupName1<br />WHERE (T1.Curr_Rank = @Percentile OR (T1.Curr_Rank < @Percentile AND T1.Next_Rank > @Percentile))<br /> AND (T2.Curr_Rank = @Percentile OR (T2.Curr_Rank > @Percentile AND T2.Prev_Rank < @Percentile))<br />GoAnonymoushttps://www.blogger.com/profile/09453699184523060639noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-85217045665168213292014-09-06T01:23:09.528+05:302014-09-06T01:23:09.528+05:30Is this possible with TWO by groups? Would it requ...Is this possible with TWO by groups? Would it require another CTE?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-90803141441116301902013-04-04T12:19:36.066+05:302013-04-04T12:19:36.066+05:30Hi Sir,
Thanks for the excellent solutions.
Rega...Hi Sir, <br />Thanks for the excellent solutions.<br /><br />Regards,<br />Hiren Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-47266173713500127472013-02-15T14:20:24.968+05:302013-02-15T14:20:24.968+05:30Hi Arun,
i have tried with example which you poste...Hi Arun,<br />i have tried with example which you posted earlier,<br />but i am getting two percentile values for below table.<br /><br />Name CSO_Age<br />OT 73<br />OT 3<br /><br />could you suggest me how can calculate these two percenterle values?<br /><br />Thanks in advance!!<br /><br />Regards,<br />vinay<br />Vinaynoreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-85953299817528667242013-02-15T13:48:45.551+05:302013-02-15T13:48:45.551+05:30Hi Arun,
can we calculate the percentile values b...Hi Arun,<br /><br />can we calculate the percentile values by using the temp tables inseted of by using <br />CTE?<br />could you please suggest me with some example.<br /><br />thanks in advance..Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-75585495865452419762013-01-11T18:50:06.390+05:302013-01-11T18:50:06.390+05:30Thanks Arun,
This query has helpmed me a lot.
than...Thanks Arun,<br />This query has helpmed me a lot.<br />thanks a lot for your query.<br /><br />Regards,<br />vinayVinay kumarnoreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-91796683013329733872013-01-03T17:48:22.740+05:302013-01-03T17:48:22.740+05:30Hi Vinay,
Check below query for solution:
DECLAR...Hi Vinay,<br /><br />Check below query for solution:<br /><br />DECLARE @Percentile FLOAT<br />SELECT @Percentile = .95;<br /><br />WITH Group_Row_Count(GroupName, Row_Count) AS<br />(<br /> SELECT GroupName, COUNT(*)<br /> FROM Employees<br /> GROUP BY GroupName<br /> HAVING COUNT(*) >= 1<br />),<br />Emp_Sal(GroupName, Salary, Prev_Rank, Curr_Rank, Next_Rank) AS<br />(<br /> SELECT E.GroupName, E.Salary, <br /> (ROW_NUMBER() OVER ( PARTITION BY E.GroupName ORDER BY E.Salary) - 2.0) / (C.Row_Count - (CASE WHEN C.Row_Count > 1 THEN 1 ELSE 0 END)), <br /> (ROW_NUMBER() OVER ( PARTITION BY E.GroupName ORDER BY E.Salary) - 1.0) / (C.Row_Count - (CASE WHEN C.Row_Count > 1 THEN 1 ELSE 0 END)), <br /> (ROW_NUMBER() OVER ( PARTITION BY E.GroupName ORDER BY E.Salary) + 0.0) / (C.Row_Count - (CASE WHEN C.Row_Count > 1 THEN 1 ELSE 0 END))<br /> FROM Employees E <br /> JOIN Group_Row_Count C ON C.GroupName = E.GroupName<br />)<br />SELECT T1.GroupName, <br /> CASE <br /> WHEN T1.Salary = T2.Salary THEN T1.Salary<br /> ELSE T1.Salary + (T2.Salary - T1.Salary) * ((@Percentile - T1.Curr_Rank) / (T2.Curr_Rank - T1.Curr_Rank)) <br /> END<br />FROM Emp_Sal T1 <br />JOIN Emp_Sal T2 ON T1.GroupName = T2.GroupName<br />WHERE ((T1.Curr_Rank = @Percentile OR (T1.Curr_Rank < @Percentile AND T1.Next_Rank > @Percentile))<br /> AND (T2.Curr_Rank = @Percentile OR (T2.Curr_Rank > @Percentile AND T2.Prev_Rank < @Percentile)))<br /> OR (T1.Curr_Rank = 0 AND T1.Next_Rank = 1 AND T1.Prev_Rank = -1)<br />Go<br />Anonymoushttps://www.blogger.com/profile/09453699184523060639noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-16000078173673491822013-01-02T18:02:35.597+05:302013-01-02T18:02:35.597+05:30Hi Arun,
Thanks for your great article,it was rea...Hi Arun,<br /><br />Thanks for your great article,it was really helped me.<br />But i have one doubt.<br />In the below "Employee" Table <br /><br />EmpID GroupName Emp_Name Salary<br />1 PRODUCTION P1 12000<br />2 PRODUCTION P2 13500<br />3 PRODUCTION P3 13500<br />4 PRODUCTION P4 11500<br />5 SALES S1 12500<br />11 MARKETING M1 11500<br />12 MARKETING M2 11000<br />13 MARKETING M3 16500<br />14 MARKETING M4 15500<br />15 MARKETING M5 12250<br />16 MARKETING M6 11500<br />17 ACCOUNTING A1 10500<br />18 ACCOUNTING A2 13500<br />19 ACCOUNTING A3 15000<br /><br />i am unable to find out "Sales" percentile ?<br /><br />Could you please help me?<br /><br />Thanks in advance for your help.<br /><br />Regards,<br />vinay.Vinay Kumarnoreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-40403291688415503062013-01-02T17:46:57.473+05:302013-01-02T17:46:57.473+05:30HI Arun,
i have one doubt ,
could you please help ...HI Arun,<br />i have one doubt ,<br />could you please help me.<br />question : In my table Personame is Rajesh and his age 50.<br />could you please help me, how to calculate the single record percentile values?<br />thanks in advance.<br /><br />Regards,<br />vinayAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-11092533545840520882012-02-24T09:26:39.711+05:302012-02-24T09:26:39.711+05:30Hello Dayalan,
Check below link for the solution
h...Hello Dayalan,<br />Check below link for the solution<br />http://sqlanddotnetdevelopment.blogspot.in/2012/02/how-to-generate-quartile-ranking.html<br /><br />Thanks<br />Arun LadhaAnonymoushttps://www.blogger.com/profile/09453699184523060639noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-52616552270838658602012-02-22T19:34:09.404+05:302012-02-22T19:34:09.404+05:30Hi Arun,
thaak you for this solution.
Please may i...Hi Arun,<br />thaak you for this solution.<br />Please may i know how you would go about calculating the 3rd quartile, like in excel by month?<br /><br />Kind regards,<br />DayalanDayalanhttps://www.blogger.com/profile/12917062586074602685noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-32120316477772164802011-06-01T10:03:49.802+05:302011-06-01T10:03:49.802+05:30Hi Matrika,
Thanks a lotHi Matrika,<br />Thanks a lotAnonymoushttps://www.blogger.com/profile/09453699184523060639noreply@blogger.comtag:blogger.com,1999:blog-3682177839638728927.post-48925805069197925302011-05-31T18:21:17.241+05:302011-05-31T18:21:17.241+05:30Hi Arun,
Thanks a million, you are a star, code wo...Hi Arun,<br />Thanks a million, you are a star, code works beautifully, many thanks again,I think I found my SQL Guru!!!<br /><br />Regards<br />MatrikaMatrikanoreply@blogger.com