90th Percentile

Last Post 25 Feb 2008 07:06 PM by SQLUSA. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
beccong
New Member
New Member

--
21 Feb 2008 01:16 PM
Hi there

I'm on sql 2000 and writing a SQL for SSRS. i need to graph the 90th percentile wait time for each of the last 31 days.

cheers

Gene
beccong
New Member
New Member

--
21 Feb 2008 01:48 PM
this may give you an idea of what I'm trying to do. (In my own bizarre way)

SELECT dim_donation_Date_key,
(SELECT min(difftime)
FROM
(
SELECT TOP 10 Percent dim_donation_Date_key,
cast(datediff(mi,PrintDateTime ,StartDateTime)as decimal(13,7)) as difftime
FROM
(select DonorID, dim_donation_Date_key,
DATEADD(mi,CONVERT(int, SUBSTRING(starttime, 3, 2)),DATEADD(hh,CONVERT(int, SUBSTRING(starttime, 1, 2)),donationdate)) as StartDateTime
from fact_donations A
inner join dim_businessviewsite C
on A.siteID = C.SiteID
where dim_donation_Date_key BETWEEN convert(char(32),getdate()-31,112) AND convert(char(32),getdate()-1,112)
AND A.siteID = 1000
and phlebotomyID in ('W','F','P')
)A

LEFT OUTER JOIN
(
select DonorID, PrintDate, min(PrintDateTime) as PrintDateTime,
(SUBSTRING(printdate,1,4) + SUBSTRING(printdate,6,2) + SUBSTRING(printdate,9,2)) AS dim_PrintDate_key
from fact_donor_wait_times
where printdate BETWEEN convert(char(32),getdate()-31,111) AND convert(char(32),getdate()-1,111)
AND fact_donor_wait_times.SiteID = 1000
group by donorID, PrintDate

)B
ON A.DonorID = B.DonorID
AND A.dim_donation_Date_key = B.dim_PrintDate_key
ORDER BY cast(datediff(mi,PrintDateTime ,StartDateTime)as decimal(13,7)) DESC
)XX
WHERE xx.dim_donation_Date_key = ddd.dim_donation_date_key
GROUP BY XX.dim_donation_Date_key
)
FROM dim_donation_date ddd
WHERE ddd.dim_donation_date_key BETWEEN convert(char(32),getdate()-31,112) AND convert(char(32),getdate()-1,112)
SQLUSA
New Member
New Member

--
21 Feb 2008 05:22 PM
So what is your question?

SQL?

Reporting Services?

Graphing?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005repo...gservices/ The Best SQL Server 2005 Training in the World!
beccong
New Member
New Member

--
21 Feb 2008 05:48 PM
SQL - the query as it is above does not work. it gets the top 10 percent for the month not for the day.
SQLUSA
New Member
New Member

--
22 Feb 2008 12:16 AM
You need a separate SELECT - GROUP BY ( derived table, or CTE) which calculates for EACH day the 90% percentile wait threshold.

You have to JOIN to this derived table or CTE on date key for final results.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005grandslam/ The Best SQL Server 2005 Training in the World!
beccong
New Member
New Member

--
25 Feb 2008 05:30 PM
Hi There

Couple of things - what is a CTE and would it be possible for you to provide a simple example.

Cheers
SQLUSA
New Member
New Member

--
25 Feb 2008 07:06 PM
CTE - Common Table Expression in SQL Server 2005 and SQL Server 2008

Before that you used derived tables (not exactly the same).

Here is an example: http://www.sqlusa.com/bestpractices...xpression/

CTE-s help with structured programming, simplifying complex SQL queries.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/sql-server-2008-training/ The Best SQL Server 2008 Training in the World!


Acceptable Use Policy
---