Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

tqeonline

MIS
Oct 5, 2009
304
US
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
Code:
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
 
When you use a subquery in the SELECT clause of the SELECT statement, it must return only one (or 0) values.

May be you want to find 0.9 * Max([Diff]) as [90%]
and 0.1 * Max([Diff]) as [10%]

PluralSight Learning Library
 
well I need it to return 1 value. I need it to return the Min of the Top 10% and the Max of the Bottom 10%.

So i'd be like

Code:
Min(Top 10%)
Max(Bottom 10%)

So this needs to return only one value.

- 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
 
Try
Code:
;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' 
    ),
top10 as (Select Top 10 Percent [DIFF] From TMP_TABLE ORDER BY [DIFF] ASC),
bottom10 as (Select Top 10 Percent [DIFF] From TMP_TABLE ORDER BY [DIFF] DESC),
MinTop10 as (select Min(DIFF) as [90%] from top10),
MaxBottom10 as (select Max(Diff) as [10%] from Bottom10)

SELECT [Close]
    , MIN([Diff]) as 'Min'
    , MAX([Diff]) as 'Max'
    , MT.[90%],
    , MB.[10%]
FROM TMP_TABLE, MinTop10 MT, MaxBottom10 MB
GROUP BY [Close]
ORDER BY [Close]

PluralSight Learning Library
 
it gives me this error:

Msg 8120, Level 16, State 1, Line 1
Column 'MinTop10.90%' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

- 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
 
[90%] and [10%] are all returning '6' and '99' every single one.

- 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
 
this is what i have that's not working with the 90% and 10%

Code:
WITH TMP_TABLE AS (SELECT DateAdd(Month, DateDiff(Month, 0, BG_CLOSING_DATE), 0) as 'Date'          
, DATEDIFF(day,[BG_DETECTION_DATE],[BG_CLOSING_DATE]) as 'Duration'    
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')

,top10 as (Select Top 10 Percent [Duration] From TMP_TABLE ORDER BY [Duration] DESC)
,MinTop10 as (select Min([Duration]) as [90%] from top10)

,bottom10 as (Select Top 10 Percent [Duration] From TMP_TABLE ORDER BY [Duration] ASC)
,MaxBottom10 as (select Max([Duration]) as [10%] from Bottom10)

SELECT [Date]    
, MIN([Duration]) as 'Min'    
, MAX([Duration]) as 'Max'    
, MIN(MT.[90%]) as '90%'
, MAX(MB.[10%]) as '10%'
FROM TMP_TABLE, MinTop10 MT, MaxBottom10 MB
GROUP BY [Date]
ORDER BY [Date]

- 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
 
after looking at the data. It seems it's not grabbing the top 10% for a given month. [Date]. But it's doing a general top 10% across everything.

- 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
 
Yes, it's doing it for the whole table.

Try
Code:
;WITH TMP_TABLE AS (SELECT DateAdd(Month, DateDiff(Month, 0, BG_CLOSING_DATE), 0) as 'Date'          
, DATEDIFF(day,[BG_DETECTION_DATE],[BG_CLOSING_DATE]) as 'Duration'    
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 T1.[Close]
    , MIN(T1.[Diff]) as 'Min'
    , MAX(T1.[Diff]) as 'Max'
    , MIN(T2.[Diff]) as '90%'
    , MAX(T3.[Diff]) as '10%'
FROM TMP_TABLE INNER JOIN
(Select Top 10 Percent [DIFF],[Date] 
From TMP_TABLE ORDER BY [DIFF] ASC) T2 ON T1.[date] = T2.[Date]
INNER JOIN
(Select Top 10 Percent [DIFF],[Date] From TMP_TABLE ORDER BY [DIFF] DESC) T3 ON T1.[Date] = T3.[Date]
GROUP BY [Close]
ORDER BY [Close]

PluralSight Learning Library
 
Does T1 = TMP_TABLE?

If so I got it to work like this:

Code:
WITH T1 AS 
(
	SELECT DateAdd(Month, DateDiff(Month, 0, BG_CLOSING_DATE), 0) as 'Date'          
	, DATEDIFF(day,[BG_DETECTION_DATE],[BG_CLOSING_DATE]) as 'Duration'    
	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 T1.[Date]    
	, MIN(T1.[Duration]) as 'Min'    
	, MAX(T1.[Duration]) as 'Max'    
	, MIN(T2.[Duration]) as '90%'    
	, MAX(T3.[Duration]) as '10%'
FROM T1 

INNER JOIN
(
	Select Top 10 Percent [Duration],[Date] 
	From T1 
	ORDER BY [Duration] ASC
) T2 
ON T1.[Date] = T2.[Date]

INNER JOIN
(
	Select Top 10 Percent [Duration],[Date] 
	From T1 
	ORDER BY [Duration] DESC
) T3 
ON T1.[Date] = T3.[Date]

GROUP BY T1.[Date]
ORDER BY T1.[Date]

It's a 6 second query and only returns 3 months... It should be returning 4 so it's leaving out november for some reason?

- 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
 
after again checking the data I have verified it should return 5 months. 8, 9, 10, 11, and 12

Here's the results right now

Date Min Max 90% 10%
2010-08-01 00:00:00.000 0 25 0 25
2010-09-01 00:00:00.000 -6 33 -6 33
2010-10-01 00:00:00.000 0 60 0 60

- 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
 
Yes, in my query I meant to give TEMP_TABLE alias T1 and forgot. I suggest to debug it. Comment out the current query and just do

select * from T1 to see which months it brings. I don't see a reason for this query to bring less months than it should as INNER JOINS with top 10/top 90 should not change the number of months.

PluralSight Learning Library
 
ok it is returning 5 months now... still a slow query and for two of the 10% it is returning NULL. Any reason it's returning null?

- 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
 
It's returning null when all the results of the table are the same value.

Ex. All the values return 0 so it's trying to get the MIN() of all 0's and can't.

- 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
 
instead of
Code:
MAX(T3.[Duration])

Could it be a Select Top 1 [Duration] FROM T3? Because that would always give me the Max or Min (with regards to T2).

- 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
 
Scratch the derived tables idea. It still will give you result for the whole table, not for each individual month.

This is really tricky problem. Post DDL of the table and some insert statements so I can try to solve it in real (not from the top of my head).

PluralSight Learning Library
 
here is the Create Statement
Code:
USE [default_bsd_qa_db]
GO

/****** Object:  Table [dbo].[BUG]    Script Date: 12/02/2010 09:08:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BUG](
	[BG_BUG_ID] [int] NOT NULL,
	[BG_STATUS] [varchar](70) NULL,
	[BG_RESPONSIBLE] [varchar](60) NULL,
	[BG_PROJECT] [varchar](70) NULL,
	[BG_SUBJECT] [int] NULL,
	[BG_SUMMARY] [varchar](255) NULL,
	[BG_DESCRIPTION] [text] NULL,
	[BG_DEV_COMMENTS] [text] NULL,
	[BG_REPRODUCIBLE] [varchar](1) NULL,
	[BG_SEVERITY] [varchar](70) NULL,
	[BG_PRIORITY] [varchar](70) NULL,
	[BG_DETECTED_BY] [varchar](60) NULL,
	[BG_DETECTION_DATE] [datetime] NULL,
	[BG_DETECTION_VERSION] [varchar](70) NULL,
	[BG_PLANNED_CLOSING_VER] [varchar](70) NULL,
	[BG_ESTIMATED_FIX_TIME] [int] NULL,
	[BG_ACTUAL_FIX_TIME] [int] NULL,
	[BG_CLOSING_DATE] [datetime] NULL,
	[BG_CLOSING_VERSION] [varchar](70) NULL,
	[BG_TO_MAIL] [varchar](1) NULL,
	[BG_ATTACHMENT] [varchar](1) NULL,
	[BG_USER_01] [varchar](40) NULL,
	[BG_USER_02] [varchar](40) NULL,
	[BG_USER_03] [varchar](40) NULL,
	[BG_USER_04] [varchar](40) NULL,
	[BG_USER_05] [varchar](40) NULL,
	[BG_USER_06] [varchar](40) NULL,
	[BG_USER_07] [varchar](40) NULL,
	[BG_USER_08] [varchar](40) NULL,
	[BG_USER_09] [varchar](255) NULL,
	[BG_USER_10] [varchar](40) NULL,
	[BG_USER_11] [varchar](70) NULL,
	[BG_USER_12] [varchar](40) NULL,
	[BG_USER_13] [varchar](40) NULL,
	[BG_USER_14] [varchar](40) NULL,
	[BG_USER_15] [varchar](40) NULL,
	[BG_USER_16] [varchar](40) NULL,
	[BG_USER_17] [varchar](40) NULL,
	[BG_USER_18] [varchar](40) NULL,
	[BG_USER_19] [varchar](40) NULL,
	[BG_USER_20] [varchar](40) NULL,
	[BG_USER_21] [varchar](40) NULL,
	[BG_USER_22] [varchar](40) NULL,
	[BG_USER_23] [varchar](40) NULL,
	[BG_USER_24] [varchar](40) NULL,
	[BG_USER_HR_01] [int] NULL,
	[BG_USER_HR_02] [int] NULL,
	[BG_USER_HR_03] [int] NULL,
	[BG_USER_HR_04] [int] NULL,
	[BG_USER_HR_05] [int] NULL,
	[BG_USER_HR_06] [int] NULL,
	[BG_BUG_VER_STAMP] [int] NULL,
	[BG_HAS_CHANGE] [varchar](50) NULL,
	[BG_VTS] [varchar](20) NULL,
	[BG_EXTENDED_REFERENCE] [varchar](50) NULL,
	[BG_REQUEST_ID] [int] NULL,
	[BG_TEXT_SYNC] [varchar](1) NULL,
	[BG_REQUEST_SERVER] [varchar](120) NULL,
	[BG_REQUEST_TYPE] [varchar](120) NULL,
	[BG_REQUEST_NOTE] [text] NULL,
	[BG_CYCLE_ID] [int] NULL,
	[BG_TEST_REFERENCE] [int] NULL,
	[BG_CYCLE_REFERENCE] [varchar](255) NULL,
	[BG_RUN_REFERENCE] [int] NULL,
	[BG_STEP_REFERENCE] [int] NULL,
	[BG_DETECTED_IN_REL] [int] NULL,
	[BG_DETECTED_IN_RCYC] [int] NULL,
	[BG_TARGET_REL] [int] NULL,
	[BG_TARGET_RCYC] [int] NULL,
 CONSTRAINT [BG_PRIMARYKEY] PRIMARY KEY CLUSTERED 
(
	[BG_BUG_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

The only columns we are using in this query is:
[BG_STATUS]
[BG_CLOSING_DATE]
[BG_DETECTION_DATE]

Here is an insert statement:
Code:
INSERT INTO [default_bsd_qa_db].[dbo].[BUG]
           ([BG_BUG_ID]
           ,[BG_STATUS]
           ,[BG_RESPONSIBLE]
           ,[BG_PROJECT]
           ,[BG_SUBJECT]
           ,[BG_SUMMARY]
           ,[BG_DESCRIPTION]
           ,[BG_DEV_COMMENTS]
           ,[BG_REPRODUCIBLE]
           ,[BG_SEVERITY]
           ,[BG_PRIORITY]
           ,[BG_DETECTED_BY]
           ,[BG_DETECTION_DATE]
           ,[BG_DETECTION_VERSION]
           ,[BG_PLANNED_CLOSING_VER]
           ,[BG_ESTIMATED_FIX_TIME]
           ,[BG_ACTUAL_FIX_TIME]
           ,[BG_CLOSING_DATE]
           ,[BG_CLOSING_VERSION]
           ,[BG_TO_MAIL]
           ,[BG_ATTACHMENT]
           ,[BG_USER_01]
           ,[BG_USER_02]
           ,[BG_USER_03]
           ,[BG_USER_04]
           ,[BG_USER_05]
           ,[BG_USER_06]
           ,[BG_USER_07]
           ,[BG_USER_08]
           ,[BG_USER_09]
           ,[BG_USER_10]
           ,[BG_USER_11]
           ,[BG_USER_12]
           ,[BG_USER_13]
           ,[BG_USER_14]
           ,[BG_USER_15]
           ,[BG_USER_16]
           ,[BG_USER_17]
           ,[BG_USER_18]
           ,[BG_USER_19]
           ,[BG_USER_20]
           ,[BG_USER_21]
           ,[BG_USER_22]
           ,[BG_USER_23]
           ,[BG_USER_24]
           ,[BG_USER_HR_01]
           ,[BG_USER_HR_02]
           ,[BG_USER_HR_03]
           ,[BG_USER_HR_04]
           ,[BG_USER_HR_05]
           ,[BG_USER_HR_06]
           ,[BG_BUG_VER_STAMP]
           ,[BG_HAS_CHANGE]
           ,[BG_VTS]
           ,[BG_EXTENDED_REFERENCE]
           ,[BG_REQUEST_ID]
           ,[BG_TEXT_SYNC]
           ,[BG_REQUEST_SERVER]
           ,[BG_REQUEST_TYPE]
           ,[BG_REQUEST_NOTE]
           ,[BG_CYCLE_ID]
           ,[BG_TEST_REFERENCE]
           ,[BG_CYCLE_REFERENCE]
           ,[BG_RUN_REFERENCE]
           ,[BG_STEP_REFERENCE]
           ,[BG_DETECTED_IN_REL]
           ,[BG_DETECTED_IN_RCYC]
           ,[BG_TARGET_REL]
           ,[BG_TARGET_RCYC])
     VALUES
           (<BG_BUG_ID, int,>
           ,<BG_STATUS, varchar(70),>
           ,<BG_RESPONSIBLE, varchar(60),>
           ,<BG_PROJECT, varchar(70),>
           ,<BG_SUBJECT, int,>
           ,<BG_SUMMARY, varchar(255),>
           ,<BG_DESCRIPTION, text,>
           ,<BG_DEV_COMMENTS, text,>
           ,<BG_REPRODUCIBLE, varchar(1),>
           ,<BG_SEVERITY, varchar(70),>
           ,<BG_PRIORITY, varchar(70),>
           ,<BG_DETECTED_BY, varchar(60),>
           ,<BG_DETECTION_DATE, datetime,>
           ,<BG_DETECTION_VERSION, varchar(70),>
           ,<BG_PLANNED_CLOSING_VER, varchar(70),>
           ,<BG_ESTIMATED_FIX_TIME, int,>
           ,<BG_ACTUAL_FIX_TIME, int,>
           ,<BG_CLOSING_DATE, datetime,>
           ,<BG_CLOSING_VERSION, varchar(70),>
           ,<BG_TO_MAIL, varchar(1),>
           ,<BG_ATTACHMENT, varchar(1),>
           ,<BG_USER_01, varchar(40),>
           ,<BG_USER_02, varchar(40),>
           ,<BG_USER_03, varchar(40),>
           ,<BG_USER_04, varchar(40),>
           ,<BG_USER_05, varchar(40),>
           ,<BG_USER_06, varchar(40),>
           ,<BG_USER_07, varchar(40),>
           ,<BG_USER_08, varchar(40),>
           ,<BG_USER_09, varchar(255),>
           ,<BG_USER_10, varchar(40),>
           ,<BG_USER_11, varchar(70),>
           ,<BG_USER_12, varchar(40),>
           ,<BG_USER_13, varchar(40),>
           ,<BG_USER_14, varchar(40),>
           ,<BG_USER_15, varchar(40),>
           ,<BG_USER_16, varchar(40),>
           ,<BG_USER_17, varchar(40),>
           ,<BG_USER_18, varchar(40),>
           ,<BG_USER_19, varchar(40),>
           ,<BG_USER_20, varchar(40),>
           ,<BG_USER_21, varchar(40),>
           ,<BG_USER_22, varchar(40),>
           ,<BG_USER_23, varchar(40),>
           ,<BG_USER_24, varchar(40),>
           ,<BG_USER_HR_01, int,>
           ,<BG_USER_HR_02, int,>
           ,<BG_USER_HR_03, int,>
           ,<BG_USER_HR_04, int,>
           ,<BG_USER_HR_05, int,>
           ,<BG_USER_HR_06, int,>
           ,<BG_BUG_VER_STAMP, int,>
           ,<BG_HAS_CHANGE, varchar(50),>
           ,<BG_VTS, varchar(20),>
           ,<BG_EXTENDED_REFERENCE, varchar(50),>
           ,<BG_REQUEST_ID, int,>
           ,<BG_TEXT_SYNC, varchar(1),>
           ,<BG_REQUEST_SERVER, varchar(120),>
           ,<BG_REQUEST_TYPE, varchar(120),>
           ,<BG_REQUEST_NOTE, text,>
           ,<BG_CYCLE_ID, int,>
           ,<BG_TEST_REFERENCE, int,>
           ,<BG_CYCLE_REFERENCE, varchar(255),>
           ,<BG_RUN_REFERENCE, int,>
           ,<BG_STEP_REFERENCE, int,>
           ,<BG_DETECTED_IN_REL, int,>
           ,<BG_DETECTED_IN_RCYC, int,>
           ,<BG_TARGET_REL, int,>
           ,<BG_TARGET_RCYC, int,>)
GO

The only columns that are important are:
[BG_STATUS]
[BG_CLOSING_DATE]
[BG_DETECTION_DATE]

[BG_STATUS] has to be 'Closed' for this query. Other than that I want a rolling 12 month query.

Do you need anything else?


- 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
 
No, I wanted something simple. Say, simple table with only relevant columns and several insert statements to re-present the data (you can mock the data, I just need them to have a test case for me).

In other words, think how would you explain your problem with a test case scenario - so someone who doesn't have your data can easily create a test environment to test query.

PluralSight Learning Library
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top