top 5%

[dtoney]

I have a list of applications that have help desk tickets and need to
determine the top 5% of defects by the number of tickets called in per month.
What would be the best way to set this up? I considered using the results of
a cross tab query but dont really know how to get the top 5% by month...
please help!

 

[Stefan Hoffmann]

hi,

I have a list of applications that have help desk tickets and need to
determine the top 5% of defects by the number of tickets called in per month.
What would be the best way to set this up? I considered using the results of
a cross tab query but dont really know how to get the top 5% by month...
please help!

For the pivot query use the wizard and to gather your data use:

SELECT TOP 5 PERCENT *
FROM [yourTable]


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Not correct as not complete:

SELECT TOP 5 PERCENT *
FROM [yourTable]

ORDER BY [yourFieldList]

mfG
--> stefan <--

 

[KARL DEWEY]

These queries will get you part way there --
Call_Data_Application --
SELECT Format([CallData].[CallDate],"mmmm yyyy") AS Call_Month,
Format([CallData].[CallDate],"yyyymm") AS CallMon, CallData_1.Application
FROM CallData, CallData AS CallData_1
GROUP BY Format([CallData].[CallDate],"mmmm yyyy"),
Format([CallData].[CallDate],"yyyymm"), CallData_1.Application
ORDER BY Format([CallData].[CallDate],"yyyymm");

Call_Data_Application_1
SELECT Call_Data_Application.Call_Month, Call_Data_Application.CallMon,
Call_Data_Application.Application,
Sum(IIf(Format([CallData].[CallDate],"yyyymm")=[CallMon],1,0))/[CountOfApplication] AS Percent_Of_Month
FROM (Call_Data_Application LEFT JOIN CallData ON
Call_Data_Application.Application = CallData.Application) INNER JOIN
Call_Data_Application_2 ON Call_Data_Application.CallMon =
Call_Data_Application_2.Call_Mon
GROUP BY Call_Data_Application.Call_Month, Call_Data_Application.CallMon,
Call_Data_Application.Application, Call_Data_Application_2.CountOfApplication
ORDER BY Call_Data_Application.CallMon;

Call_Data_Application_2 --
SELECT Format([CallData].[CallDate],"yyyymm") AS Call_Mon,
Count(CallData.Application) AS CountOfApplication
FROM CallData
GROUP BY Format([CallData].[CallDate],"yyyymm");

 

[dtoney]

If I'm understanding correctly... all of that code appears to merely be
creating sum of incidents by month by application... which in affect is about
the same as a crosstab query... the only advantage I see is that you have the
year - which is albeit lost in the crosstab without additional coding.

please help me understand if I'm off base.

Thanks!

These queries will get you part way there --
Call_Data_Application --
SELECT Format([CallData].[CallDate],"mmmm yyyy") AS Call_Month,
Format([CallData].[CallDate],"yyyymm") AS CallMon, CallData_1.Application
FROM CallData, CallData AS CallData_1
GROUP BY Format([CallData].[CallDate],"mmmm yyyy"),
Format([CallData].[CallDate],"yyyymm"), CallData_1.Application
ORDER BY Format([CallData].[CallDate],"yyyymm");

Call_Data_Application_1
SELECT Call_Data_Application.Call_Month, Call_Data_Application.CallMon,
Call_Data_Application.Application,
Sum(IIf(Format([CallData].[CallDate],"yyyymm")=[CallMon],1,0))/[CountOfApplication] AS Percent_Of_Month
FROM (Call_Data_Application LEFT JOIN CallData ON
Call_Data_Application.Application = CallData.Application) INNER JOIN
Call_Data_Application_2 ON Call_Data_Application.CallMon =
Call_Data_Application_2.Call_Mon
GROUP BY Call_Data_Application.Call_Month, Call_Data_Application.CallMon,
Call_Data_Application.Application, Call_Data_Application_2.CountOfApplication
ORDER BY Call_Data_Application.CallMon;

Call_Data_Application_2 --
SELECT Format([CallData].[CallDate],"yyyymm") AS Call_Mon,
Count(CallData.Application) AS CountOfApplication
FROM CallData
GROUP BY Format([CallData].[CallDate],"yyyymm");

--
Build a little, test a little.

I have a list of applications that have help desk tickets and need to
determine the top 5% of defects by the number of tickets called in per month.
What would be the best way to set this up? I considered using the results of
a cross tab query but dont really know how to get the top 5% by month...
please help!