NTILE GROUP By?? 1

[tqeonline]

Hello all - lets see if I can stump the audience with this one.

I have a query

WITH TMP_TABLE AS
( 
    SELECT [BG_USER_02], 
        DateAdd(Month, DateDiff(Month, 0, CLOSED.MAX_AU_TIME), 0) AS 'ClosedOn', 
        DATEDIFF(hh, SUB.MIN_AU_TIME, CLOSED.MAX_AU_TIME) AS 'Duration' 
    FROM RELEASE_CYCLES WITH (NOLOCK),  
(
    SELECT AU_ENTITY_ID, MIN(AU_TIME) AS MIN_AU_TIME 
    FROM AUDIT_LOG WITH (NOLOCK), AUDIT_PROPERTIES WITH (NOLOCK)
    WHERE AU_ENTITY_TYPE = 'BUG'
        AND AP_FIELD_NAME = 'BG_STATUS' 
        AND AU_ACTION_ID = AP_ACTION_ID 
        AND AP_NEW_VALUE = 'Submitted'
    GROUP BY AU_ENTITY_ID 
) AS SUB, 
BUG LEFT OUTER JOIN 
(
    SELECT AU_ENTITY_ID, MAX(AU_TIME) AS MAX_AU_TIME
    FROM AUDIT_LOG WITH (NOLOCK), AUDIT_PROPERTIES WITH (NOLOCK)
    WHERE AU_ENTITY_TYPE = 'BUG' 
        AND AP_FIELD_NAME = 'BG_STATUS' 
        AND AU_ACTION_ID = AP_ACTION_ID 
        AND AP_NEW_VALUE = 'Closed' 
    GROUP BY AU_ENTITY_ID 
) AS CLOSED 
ON CLOSED.AU_ENTITY_ID = BUG.BG_BUG_ID 
LEFT OUTER JOIN (SELECT REL_NAME, REL_ID FROM RELEASES) AS RELEASES
ON BUG.BG_DETECTED_IN_REL = RELEASES.REL_ID 
WHERE BUG.BG_STATUS = 'Closed' 
    AND SUB.AU_ENTITY_ID = BUG.BG_BUG_ID
    AND CLOSED.AU_ENTITY_ID = BUG.BG_BUG_ID
    AND BG_DETECTED_IN_RCYC = RCYC_ID 
)
,t1 AS
(
    SELECT *,
        NTILE(10) OVER (ORDER BY [Duration]) AS [PercentGrp],        
        NTILE(2) OVER (ORDER BY [Duration]) AS [Ascending],
        NTILE(2) OVER (ORDER BY [Duration] DESC) AS [Descending]
FROM TMP_TABLE WITH (NOLOCK)
) 
select * from t1
WHERE BG_USER_02 = 'iweb-eu'

The weird thing is I think this is doing the NTILE over the entire collection so when I do the

select * from t1
WHERE BG_USER_02 = 'iweb-eu'

It is skewed based off the entire data set. If I change the T1 to be like this:

,t1 AS
(
    SELECT *,
        NTILE(10) OVER (ORDER BY [Duration]) AS [PercentGrp],        
        NTILE(2) OVER (ORDER BY [Duration]) AS [Ascending],
        NTILE(2) OVER (ORDER BY [Duration] DESC) AS [Descending]
FROM TMP_TABLE WITH (NOLOCK)
GROUP BY [BG_USER_02] = 'iweb-eu'
)

And then just do a Select * FROM T1 the results are accurate.

The issue is that I need to be able to do a group by BG_USER_02 and do calculations off the returned values. There are multiple BG_USER_02's (application names) and i'm trying to do median and a few other things so the results look like this currently:

BG_USER_02 Duration PercGrp Asc Desc
iCare 4300 10 2 1
iCare 4246 10 2 1
eClaims 4186 10 2 1
iCare 4132 10 2 1
CRM-EU 4132 10 2 1
iCare 4062 10 2 1
iCare 4062 10 2 1
IS 3914 10 2 1
CRM-EU 3825 10 2 1
CRM-EU 3824 10 2 1

ignore the closed on values

- 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]

it seems that i need to maybe loop through the distinct BG_USER_02 and write that to a table... I just don't know how to do that.

- 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]

Your question is not clear (you may get better help if you show some data and desired result), but try adding WHERE clause into T1 cte definition to filter only particular user.

PluralSight Learning Library

 

[tqeonline]

sorry... let me try this again.

Picture you have 10 to 15 applications. Each one of those applications has 100s of defects. You need to create a chart that looks like a candle stick. So you need to know the min and max, this creates the stick. Then you need the 10% value and the 90% value, these create the "Candle". I have got this much.

Now I need to get the Median. So I thought I could do this:

NTILE(2) OVER (ORDER BY [Duration]) AS [Ascending],
NTILE(2) OVER (ORDER BY [Duration] DESC) AS [Descending]

Then grap the MAX of Ascending Group 1 and the MIN of Descending Group 1 add those together and divide by 2. And it works... IF you limit it to just one application.

example: WHERE APP = 'iWeb'

However, when I all the applications listed in a list view, the data gets skewed. What is happening is my NTILE functions are doing it over ALL the data and not grouping the data by application. So when I do

,t1 AS
(
    SELECT *,
        NTILE(10) OVER (ORDER BY [Duration]) AS [PercentGrp],        
        NTILE(2) OVER (ORDER BY [Duration]) AS [Ascending],
        NTILE(2) OVER (ORDER BY [Duration] DESC) AS [Descending]
FROM TMP_TABLE WITH (NOLOCK)
)

It is doing it over the entire dataset. To explain in an example... Say you have 10 applications each have 3 defects (so 30 total) and I tell you to get the Median of each application... you would go get the middle number of the defects pertaining to each application. Well what this query does is get the medians for the entire dataset. Which is inaccurate.

So how can I get the Average Duration, Min, Max, 10%, 90%, and Median durations that are group by application?

- 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]

Well, just add PARTITION clause to each of your NTILE() function to partition by Application.

PluralSight Learning Library

 

[tqeonline]

I believe in miracles

Markros you have done it again!

Here is the final code for those who visit in the future.

WITH TMP_TABLE AS
( 
    SELECT [BG_USER_02], 
        DateAdd(Month, DateDiff(Month, 0, CLOSED.MAX_AU_TIME), 0) AS 'ClosedOn', 
        DATEDIFF(hh, SUB.MIN_AU_TIME, CLOSED.MAX_AU_TIME) AS 'Duration' 
    FROM RELEASE_CYCLES WITH (NOLOCK),  
(
    SELECT AU_ENTITY_ID, MIN(AU_TIME) AS MIN_AU_TIME 
    FROM AUDIT_LOG WITH (NOLOCK), AUDIT_PROPERTIES WITH (NOLOCK)
    WHERE AU_ENTITY_TYPE = 'BUG'
        AND AP_FIELD_NAME = 'BG_STATUS' 
        AND AU_ACTION_ID = AP_ACTION_ID 
        AND AP_NEW_VALUE = 'Submitted'
    GROUP BY AU_ENTITY_ID 
) AS SUB, 
BUG LEFT OUTER JOIN 
(
    SELECT AU_ENTITY_ID, MAX(AU_TIME) AS MAX_AU_TIME
    FROM AUDIT_LOG WITH (NOLOCK), AUDIT_PROPERTIES WITH (NOLOCK)
    WHERE AU_ENTITY_TYPE = 'BUG' 
        AND AP_FIELD_NAME = 'BG_STATUS' 
        AND AU_ACTION_ID = AP_ACTION_ID 
        AND AP_NEW_VALUE = 'Closed' 
    GROUP BY AU_ENTITY_ID 
) AS CLOSED 
ON CLOSED.AU_ENTITY_ID = BUG.BG_BUG_ID 
LEFT OUTER JOIN (SELECT REL_NAME, REL_ID FROM RELEASES) AS RELEASES
ON BUG.BG_DETECTED_IN_REL = RELEASES.REL_ID 
WHERE BUG.BG_STATUS = 'Closed' 
    AND SUB.AU_ENTITY_ID = BUG.BG_BUG_ID
    AND CLOSED.AU_ENTITY_ID = BUG.BG_BUG_ID
    AND BG_DETECTED_IN_RCYC = RCYC_ID 
)
,t1 AS
(
    SELECT [BG_USER_02],[Duration],
        NTILE(10) OVER (PARTITION BY [BG_USER_02] ORDER BY [Duration]) AS [PercentGrp],        
        NTILE(2) OVER (PARTITION BY [BG_USER_02] ORDER BY [Duration]) AS [Ascending],
        NTILE(2) OVER (PARTITION BY [BG_USER_02] ORDER BY [Duration] DESC) AS [Descending]
FROM TMP_TABLE WITH (NOLOCK)
) 
select * from t1

- 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