If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
top 5%
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! |
#2
|
|||
|
|||
top 5%
hi,
dtoney wrote: 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 -- |
#3
|
|||
|
|||
top 5%
hi,
Not correct as not complete: SELECT TOP 5 PERCENT * FROM [yourTable] ORDER BY [yourFieldList] mfG -- stefan -- |
#4
|
|||
|
|||
top 5%
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. "dtoney" wrote: 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! |
#5
|
|||
|
|||
top 5%
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! "KARL DEWEY" wrote: 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. "dtoney" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|