Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Top 20 count Expand / Collapse
Author
Message
Posted Monday, November 5, 2012 9:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
Hi all, I was wondering if this is possible, I use the query below which is working great if be it a little slow as table is huge. I only want to return the top 20 worst offending Operator_Names from the query


How do I do this, do I use count ?

SELECT Operator_Name, Docket_Id, Docket_Machine, Docket_Number, Docket_Status, Docket_EngineerName, Docket_Category, Docket_SubCategory, Duration,
Module, Section, Waittime, Monitor_Time, spare8, Docket_EngStart, Docket_EngFinish, Docket_DateRaised, Docket_Date, Contract
FROM DocketTB
WHERE (Docket_Status = 'CL') AND (Contract = '1') AND (Operator_Name IS NOT NULL) AND (Operator_Name NOT LIKE 'None')
Post #1381156
Posted Monday, November 5, 2012 9:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 13,315, Visits: 12,182
jerome.morris (11/5/2012)
Hi all, I was wondering if this is possible, I use the query below which is working great if be it a little slow as table is huge. I only want to return the top 20 worst offending Operator_Names from the query


How do I do this, do I use count ?

SELECT Operator_Name, Docket_Id, Docket_Machine, Docket_Number, Docket_Status, Docket_EngineerName, Docket_Category, Docket_SubCategory, Duration,
Module, Section, Waittime, Monitor_Time, spare8, Docket_EngStart, Docket_EngFinish, Docket_DateRaised, Docket_Date, Contract
FROM DocketTB
WHERE (Docket_Status = 'CL') AND (Contract = '1') AND (Operator_Name IS NOT NULL) AND (Operator_Name NOT LIKE 'None')


The easiest way is to use ROW_NUMBER. You need to figure out what makes the top 20. I don't see an order by in your query but that is what will define the "top 20".


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1381161
Posted Monday, November 5, 2012 9:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
The amount off calls raiesed by an operator, so I thought counting the ID would give me this which it does, but I want the operator with the most calls at the top going down to the 20th worst offender is that makes sense

J
Post #1381166
Posted Monday, November 5, 2012 9:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 13,315, Visits: 12,182
jerome.morris (11/5/2012)
The amount off calls raiesed by an operator, so I thought counting the ID would give me this which it does, but I want the operator with the most calls at the top going down to the 20th worst offender is that makes sense

J


With no real details the best I can do is throw together some generic sql.

;with cte as
(
select Cols, COUNT(*) as CallCount
From SomeTable
Group By Cols
)

select top 20 * from cte order by CallCount



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1381176
Posted Thursday, November 8, 2012 9:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
When everything else fails I will ask you guys. This has been annoying me for weeks now and although I can get my c# code to complete the task at hand, the other system I need to tackle are not running Win 64bit so I am always faced with the dreaded out of memory problem.

I have a large Fixed width text file (226mb min) and around 97 columns of data. I need to get all this data into my SQL DB table.

Here is my create

USE [SLADB]
GO

/****** Object: Table [dbo].[ProdDataTB] Script Date: 11/08/2012 16:25:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ProdDataTB](
[MachineName] [nvarchar](10) NULL,
[ModeName] [nvarchar](10) NULL,
[FileName] [nvarchar](10) NULL,
[JobName] [nvarchar](10) NULL,
[UserName] [nvarchar](10) NULL,
[TransactionKey] [nvarchar](10) NULL,
[StartDate_Time] [nvarchar](25) NULL,
[StartDate] [nvarchar](10) NULL,
[StartTime] [time](7) NULL,
[StartYYYY] [nvarchar](4) NULL,
[StartMM] [nvarchar](2) NULL,
[StartDD] [nvarchar](2) NULL,
[StartHH] [nvarchar](2) NULL,
[StartNN] [nvarchar](2) NULL,
[StartSS] [nvarchar](2) NULL,
[Spare1] [nvarchar](6) NULL,
[EndDate_Time] [nvarchar](30) NULL,
[EndDate] [nvarchar](10) NULL,
[EndTime] [time](7) NULL,
[EndYYYY] [nvarchar](4) NULL,
[EndMM] [nvarchar](2) NULL,
[EndDD] [nvarchar](2) NULL,
[EndHH] [nvarchar](2) NULL,
[EndNN] [nvarchar](2) NULL,
[EndSS] [nvarchar](2) NULL,
[Spare2] [nvarchar](6) NULL,
[RunTime] [float] NULL,
[DelayTime] [float] NULL,
[MachineStopTime] [float] NULL,
[OperatorStopTime] [float] NULL,
[MachineFaultTime] [float] NULL,
[OperatorFaultTime] [float] NULL,
[OldPiecesFed] [float] NULL,
[OldPiecesCompleted] [float] NULL,
[NumMachineStops] [float] NULL,
[NumOperatorStops] [float] NULL,
[NumDelays] [float] NULL,
[Feeder_00_Count] [float] NULL,
[Feeder_01_Count] [float] NULL,
[Feeder_02_Count] [float] NULL,
[Feeder_03_Count] [float] NULL,
[Feeder_04_Count] [float] NULL,
[Feeder_05_Count] [float] NULL,
[Feeder_06_Count] [float] NULL,
[Feeder_07_Count] [float] NULL,
[Feeder_08_Count] [float] NULL,
[Feeder_09_Count] [float] NULL,
[Feeder_10_Count] [float] NULL,
[Feeder_11_Count] [float] NULL,
[Feeder_12_Count] [float] NULL,
[Feeder_13_Count] [float] NULL,
[Feeder_14_Count] [float] NULL,
[Feeder_15_Count] [float] NULL,
[Feeder_16_Count] [float] NULL,
[Feeder_17_Count] [float] NULL,
[Input_Feeder_Count] [float] NULL,
[Input_SubFdr_1_Count] [float] NULL,
[Input_SubFdr_2_Count] [float] NULL,
[Input_SubFdr_3_Count] [float] NULL,
[Input_SubFdr_4_Count] [float] NULL,
[Input_SubFdr_5_Count] [float] NULL,
[Avg_Chassis_Speed] [float] NULL,
[Shift] [nvarchar](5) NULL,
[Total_Pcs_Outsorted] [float] NULL,
[Total_Pcs_Outsorted_Good] [float] NULL,
[Total_Pcs_Outsorted_Maybe] [float] NULL,
[Total_Pcs_Outsorted_Bad] [float] NULL,
[Total_Pcs_Outsorted_Unk] [float] NULL,
[Bin_01] [float] NULL,
[Bin_02] [float] NULL,
[Bin_03] [float] NULL,
[Bin_04] [float] NULL,
[Bin_05] [float] NULL,
[Bin_06] [float] NULL,
[Bin_07] [float] NULL,
[Bin_08] [float] NULL,
[IST_Bin1] [float] NULL,
[IST_Bin2] [float] NULL,
[IST_Bin3] [float] NULL,
[IST_Bin4] [float] NULL,
[IST_RunOut] [float] NULL,
[Mtr1_NoPrint] [float] NULL,
[Mtr2_NoPrint] [float] NULL,
[Mtr3_NoPrint] [float] NULL,
[Mtr4_NoPrint] [float] NULL,
[Edge_Mark1] [float] NULL,
[Edge_Mark2] [float] NULL,
[Edge_Mark3] [float] NULL,
[No_Seal] [float] NULL,
[Empty_Cycles] [float] NULL,
[Filled_Cycles] [float] NULL,
[MidRunTime] [nvarchar](8) NULL,
[SubShiftExt] [nvarchar](2) NULL,
[ShiftDateAdjust] [nvarchar](10) NULL,
[PiecesFed] [float] NOT NULL,
[PiecesCompleted] [float] NULL,
[Spare3] [nvarchar](68) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_ProdDataTB] PRIMARY KEY CLUSTERED
(
[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]

GO

Insert into

INSERT INTO [SLADB].[dbo].[ProdDataTB]
([MachineName]
,[ModeName]
,[FileName]
,[JobName]
,[UserName]
,[TransactionKey]
,[StartDate_Time]
,[StartDate]
,[StartTime]
,[StartYYYY]
,[StartMM]
,[StartDD]
,[StartHH]
,[StartNN]
,[StartSS]
,[Spare1]
,[EndDate_Time]
,[EndDate]
,[EndTime]
,[EndYYYY]
,[EndMM]
,[EndDD]
,[EndHH]
,[EndNN]
,[EndSS]
,[Spare2]
,[RunTime]
,[DelayTime]
,[MachineStopTime]
,[OperatorStopTime]
,[MachineFaultTime]
,[OperatorFaultTime]
,[OldPiecesFed]
,[OldPiecesCompleted]
,[NumMachineStops]
,[NumOperatorStops]
,[NumDelays]
,[Feeder_00_Count]
,[Feeder_01_Count]
,[Feeder_02_Count]
,[Feeder_03_Count]
,[Feeder_04_Count]
,[Feeder_05_Count]
,[Feeder_06_Count]
,[Feeder_07_Count]
,[Feeder_08_Count]
,[Feeder_09_Count]
,[Feeder_10_Count]
,[Feeder_11_Count]
,[Feeder_12_Count]
,[Feeder_13_Count]
,[Feeder_14_Count]
,[Feeder_15_Count]
,[Feeder_16_Count]
,[Feeder_17_Count]
,[Input_Feeder_Count]
,[Input_SubFdr_1_Count]
,[Input_SubFdr_2_Count]
,[Input_SubFdr_3_Count]
,[Input_SubFdr_4_Count]
,[Input_SubFdr_5_Count]
,[Avg_Chassis_Speed]
,[Shift]
,[Total_Pcs_Outsorted]
,[Total_Pcs_Outsorted_Good]
,[Total_Pcs_Outsorted_Maybe]
,[Total_Pcs_Outsorted_Bad]
,[Total_Pcs_Outsorted_Unk]
,[Bin_01]
,[Bin_02]
,[Bin_03]
,[Bin_04]
,[Bin_05]
,[Bin_06]
,[Bin_07]
,[Bin_08]
,[IST_Bin1]
,[IST_Bin2]
,[IST_Bin3]
,[IST_Bin4]
,[IST_RunOut]
,[Mtr1_NoPrint]
,[Mtr2_NoPrint]
,[Mtr3_NoPrint]
,[Mtr4_NoPrint]
,[Edge_Mark1]
,[Edge_Mark2]
,[Edge_Mark3]
,[No_Seal]
,[Empty_Cycles]
,[Filled_Cycles]
,[MidRunTime]
,[SubShiftExt]
,[ShiftDateAdjust]
,[PiecesFed]
,[PiecesCompleted]
,[Spare3])
VALUES
(<MachineName, nvarchar(10),>
,<ModeName, nvarchar(10),>
,<FileName, nvarchar(10),>
,<JobName, nvarchar(10),>
,<UserName, nvarchar(10),>
,<TransactionKey, nvarchar(10),>
,<StartDate_Time, nvarchar(25),>
,<StartDate, nvarchar(10),>
,<StartTime, time(7),>
,<StartYYYY, nvarchar(4),>
,<StartMM, nvarchar(2),>
,<StartDD, nvarchar(2),>
,<StartHH, nvarchar(2),>
,<StartNN, nvarchar(2),>
,<StartSS, nvarchar(2),>
,<Spare1, nvarchar(6),>
,<EndDate_Time, nvarchar(30),>
,<EndDate, nvarchar(10),>
,<EndTime, time(7),>
,<EndYYYY, nvarchar(4),>
,<EndMM, nvarchar(2),>
,<EndDD, nvarchar(2),>
,<EndHH, nvarchar(2),>
,<EndNN, nvarchar(2),>
,<EndSS, nvarchar(2),>
,<Spare2, nvarchar(6),>
,<RunTime, float,>
,<DelayTime, float,>
,<MachineStopTime, float,>
,<OperatorStopTime, float,>
,<MachineFaultTime, float,>
,<OperatorFaultTime, float,>
,<OldPiecesFed, float,>
,<OldPiecesCompleted, float,>
,<NumMachineStops, float,>
,<NumOperatorStops, float,>
,<NumDelays, float,>
,<Feeder_00_Count, float,>
,<Feeder_01_Count, float,>
,<Feeder_02_Count, float,>
,<Feeder_03_Count, float,>
,<Feeder_04_Count, float,>
,<Feeder_05_Count, float,>
,<Feeder_06_Count, float,>
,<Feeder_07_Count, float,>
,<Feeder_08_Count, float,>
,<Feeder_09_Count, float,>
,<Feeder_10_Count, float,>
,<Feeder_11_Count, float,>
,<Feeder_12_Count, float,>
,<Feeder_13_Count, float,>
,<Feeder_14_Count, float,>
,<Feeder_15_Count, float,>
,<Feeder_16_Count, float,>
,<Feeder_17_Count, float,>
,<Input_Feeder_Count, float,>
,<Input_SubFdr_1_Count, float,>
,<Input_SubFdr_2_Count, float,>
,<Input_SubFdr_3_Count, float,>
,<Input_SubFdr_4_Count, float,>
,<Input_SubFdr_5_Count, float,>
,<Avg_Chassis_Speed, float,>
,<Shift, nvarchar(5),>
,<Total_Pcs_Outsorted, float,>
,<Total_Pcs_Outsorted_Good, float,>
,<Total_Pcs_Outsorted_Maybe, float,>
,<Total_Pcs_Outsorted_Bad, float,>
,<Total_Pcs_Outsorted_Unk, float,>
,<Bin_01, float,>
,<Bin_02, float,>
,<Bin_03, float,>
,<Bin_04, float,>
,<Bin_05, float,>
,<Bin_06, float,>
,<Bin_07, float,>
,<Bin_08, float,>
,<IST_Bin1, float,>
,<IST_Bin2, float,>
,<IST_Bin3, float,>
,<IST_Bin4, float,>
,<IST_RunOut, float,>
,<Mtr1_NoPrint, float,>
,<Mtr2_NoPrint, float,>
,<Mtr3_NoPrint, float,>
,<Mtr4_NoPrint, float,>
,<Edge_Mark1, float,>
,<Edge_Mark2, float,>
,<Edge_Mark3, float,>
,<No_Seal, float,>
,<Empty_Cycles, float,>
,<Filled_Cycles, float,>
,<MidRunTime, nvarchar(8),>
,<SubShiftExt, nvarchar(2),>
,<ShiftDateAdjust, nvarchar(10),>
,<PiecesFed, float,>
,<PiecesCompleted, float,>
,<Spare3, nvarchar(68),>)
GO

Sample data

APS08 C5_LAKE_WE08075_002_0807500236ADAMSAG 26013Sun Jan 01 07:21:59 2012 01/01/201207:21:59 20120101072159 Sun Jan 01 07:30:42 2012 01/01/201207:30:42 20120101073042 196 27 88 212 88 212 498 483 3 0 800000487000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001147000011470000000000000000000000000000000013463 2 0 0 2 0 0 2 0 0 0 488 245 498 483
APS05 C5_LAKE_WE08066_005_0806600536HUGHEPE 28708Sun Jan 01 07:08:38 2012 01/01/201207:08:38 20120101070838 Sun Jan 01 07:47:25 2012 01/01/201207:47:25 20120101074725 1467 45 295 520 295 520 5476 5457 12 2 1500005467000000000000000000000000000000000000000000000109000042480000000000000000000000000000000000000000000000000000000000000000000000000000000000009894000000000000989400000000000000000000000014090 18 0 0 14 4 11 2 0 0 5 5467 275 5476 5457



Can anybody please recomend a fast and effective way of getting the above into my table ?

Jay
Post #1382575
Posted Thursday, November 8, 2012 10:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 13,315, Visits: 12,182
Wow that table needs some help. If at all possible you should consider changing that table. You should be storing datetime data in datetime instead of breaking apart all the pieces across various nvarchar columns. Then there is the normalization. Of course from some of the description this may be a holding table for importing from an external file prior to being imported and massaged into usable data. If that is the case then disregard my rant.

I appreciate the table ddl. But the insert is useless. It looks like it from right clicking the table in SSMS and creating the insert script. I have never really understood the point of that "feature" because it creates completely useless information.

The best way to post this is to just create an insert statement.

insert table 
select col1, col2, col3 union all
select col1, col2, col3...

Also if you use the IFCode shortcuts (on the left side when posting) you can wrap your code in the code tags and it will make it a lot cleaner. (like my code above)


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1382618
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse