SQL Min 1 of Top 10% and Max 1 of Bottom 10%??? 1

[tqeonline]

Hey guys I have a temp table that i'm creating. Then doing some queries off that table.

The goal is to get the Max and Min of the temp table. Then get the 90% item and the 10% item. I'm using the data for a candlestick chart.

This is what I currently have

WITH TMP_TABLE AS (SELECT DateAdd(Month, DateDiff(Month, 0, BG_CLOSING_DATE), 0) as 'Close'
		  , DATEDIFF(day,[BG_DETECTION_DATE],[BG_CLOSING_DATE]) as 'Diff'
	FROM [default_bsd_qa_db].[dbo].[BUG]
	WHERE [BG_CLOSING_DATE] IS NOT NULL
	AND [BG_STATUS] = 'Closed'
	AND [BG_CLOSING_DATE] > '8/1/2010' 
	AND [BG_CLOSING_DATE] < '12/31/2010'
	AND [BG_DETECTION_DATE] > '8/1/2010' 
	)
SELECT [Close]
	, MIN([Diff]) as 'Min'
	, MAX([Diff]) as 'Max'
	, MIN(Select Top 10 Percent [DIFF] From TMP_TABLE ORDER BY [DIFF] ASC) as '90%'
	, MAX(Select Top 10 Percent [DIFF] From TMP_TABLE ORDER BY [DIFF] DESC) as '10%'
FROM TMP_TABLE
GROUP BY [Close]
ORDER BY [Close]

But it is throwing errors on my Min and Max of the Top 10 Percent. It's an SQL2005 database so i'm pretty sure it should be able to work.

What am I missing?

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

ISNULL(MIN(CASE WHEN [PercentGrp]  = 9 THEN [Duration] END)
, ISNULL(MIN(CASE WHEN [PercentGrp]  = 8 THEN [Duration] END)
, ISNULL(MIN(CASE WHEN [PercentGrp]  = 7 THEN [Duration] END)
, ISNULL(MIN(CASE WHEN [PercentGrp]  = 6 THEN [Duration] END)
, ISNULL(MIN(CASE WHEN [PercentGrp]  = 5 THEN [Duration] END)
, ISNULL(MIN(CASE WHEN [PercentGrp]  = 4 THEN [Duration] END)
, ISNULL(MIN(CASE WHEN [PercentGrp]  = 3 THEN [Duration] END)
, ISNULL(MIN(CASE WHEN [PercentGrp]  = 2 THEN [Duration] END)
, ISNULL(MAX(CASE WHEN [PercentGrp]  = 1 THEN [Duration] END),0)))))))))) AS [90%]

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[markros]

No. We need to forget about using NTILE() function and use
cast(100.00*(Row_number() over (Partition by ClosedDate order by Duration)-1)/COUNT(*) over (Partition by ClosedDate) as decimal(20,4)) as Percentile

and then use case when Percentile between 0 and 10 then ..

etc.

I wanted to write the blog this weekend, but was pretty busy, so I'm going to do this next weekend.

PluralSight Learning Library