November 5, 2012 at 9:24 am
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')
November 5, 2012 at 9:31 am
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 queryHow 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 5, 2012 at 9:36 am
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
November 5, 2012 at 9:50 am
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 senseJ
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2012 at 9:27 am
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
November 8, 2012 at 10:31 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply