Thursday, March 7, 2013

[T-SQL] Counter inside SELECT

[T-SQL] Counter inside SELECT

Counter inside SELECT

Posted: 07 Mar 2013 12:10 AM PST

Hi,I have the following scenario :[code="sql"]DECLARE @TEST1 TABLE (C_NAME varchar(10), C_CODE VARCHAR(5))INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('John', 'CL1')INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('Jake', 'CL2')INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('Joe', 'CL3')INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('Jane', 'CL4')DECLARE @TEST2 TABLE (P_NAME varchar(10), P_CODE VARCHAR(5))INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('Ray', 'PL91')INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('James', 'PL92')INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('Fred', 'PL93')INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('Mac', 'PL94')DECLARE @x INTSELECT @x = MAX(CONVERT(INT,SUBSTRING(C_CODE,3,2))) FROM @TEST1INSERT INTO @TEST1(C_NAME, C_CODE) (SELECT P_NAME, C_CODE = 'CL' + CONVERT(VARCHAR(5),@x+1) FROM @TEST2) [/code]I need the new rows add to @Table1 but the C_CODE adds incrementally. What I have in @Table1 after running the above code is :[code="plain"]C_NAME C_CODE----------------------John CL1Jake CL2Joe CL3Jane CL4Ray CL5James CL5Fred CL5Mac CL5[/code] But I need it to be :[code="plain"]C_NAME C_CODE----------------------John CL1Jake CL2Joe CL3Jane CL4Ray CL5James CL6Fred CL7Mac CL8[/code]Thanks in advance for helps.

sql server stored procedure logic problem

Posted: 06 Mar 2013 06:02 PM PST

HI friends i have small doubt in sql server.plese tell me how to solve this issuse i have 2 tables based on that i want load first table records into second table useing stored procedure. structure is same in both tablecolumns like id ,name,sal in both table same columns.first table contains 1000 records .when ever we insert this records into second table that time 501 record is failed.when ever we load records what ever failed record that records must be showing errore message and remaing reords must be to load .that means when ever we applaying try catach method in stored procedure to handle errore .first in try methode to appply logicand catch metode is devlop errore message(501 records) once its catach errore.that errore is showing that records and remaing records(502 to 1000 records) to loaded in to second table plese tell me how to implement logic code in try catch block.

Cursor replacement help

Posted: 06 Mar 2013 08:01 PM PST

Morning, Thank you for your help in advance, I have built a cursor to run through list and with the use of table functions build up a set of data. Whilst I am unable to send everything through I can at least try to give you an idea of what this looks like.[code="sql"]CREATE TABLE [dbo].[#TSC_ExportedColumnIds]( [ID] [int] NOT NULL, [ColumnType] [int] NOT NULL) ON [PRIMARY]GOINSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (1, 1)GOINSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (2, 1)GOINSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (3, 1)GOINSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (4, 1)GOINSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (5, 1)GOINSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (6, 1)GOINSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (7, 3)GOINSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (8, 3)GOINSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (9, 3)GOINSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (10, 1)GOINSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (11, 1)DECLARE @TEMP_EXPORT TABLE ( [A] [varchar](500) NULL, [B] [nvarchar](500) NULL, [C] [nvarchar](50) NULL, [D] [nvarchar](50) NULL, [E] [nvarchar](1000) NULL, [F] [varchar](1000) NULL, [G] [varchar](500) NULL)DECLARE @COLUMN_ID INTDECLARE @getID CURSORSET @getID = CURSOR FORSELECT IDFROM dbo.#TSC_ExportedColumnIdsOPEN @getIDFETCH NEXTFROM @getID INTO @COLUMN_IDWHILE @@FETCH_STATUS = 0BEGINPRINT @COLUMN_IDDECLARE @COLUMN_TYPE INTSELECT @COLUMN_TYPE = TSC_COLUMNS.ColumnTypeFROM TSC_COLUMNSWHERE TSC_COLUMNS.ID = @COLUMN_IDIF @COLUMN_TYPE = 1 BEGININSERT INTO @TEMP_EXPORT SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL--INSERT INTO @TEMP_EXPORT SELECT * FROM [dbo].[udf_TSC_qry_ExportBlockColumnParam1](@COLUMN_ID)--INSERT INTO @TEMP_EXPORT SELECT * FROM [dbo].[udf_TSC_qry_ExportBlockColumnParam2](@COLUMN_ID,4)ENDIF @COLUMN_TYPE = 2BEGININSERT INTO @TEMP_EXPORT SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL--INSERT INTO @TEMP_EXPORT SELECT * FROM [dbo].[udf_TSC_qry_ExportSequenceColumnParam1](@COLUMN_ID)--INSERT INTO @TEMP_EXPORT SELECT * FROM [dbo].[udf_TSC_qry_ExportSequenceColumnParam2](@COLUMN_ID,4)ENDIF @COLUMN_TYPE = 3BEGININSERT INTO @TEMP_EXPORT SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL--INSERT INTO @TEMP_EXPORT SELECT A,B,C,D,E,F,G FROM [dbo].[udf_TSC_qry_ExportEventColumnParam0](@COLUMN_ID,4)ENDFETCH NEXTFROM @getID INTO @COLUMN_IDENDCLOSE @getIDDEALLOCATE @getIDselect * from @TEMP_EXPORT[/code]I have commented out the functions but kept them in to give you an idea of what is being created.My question is, would it be possible to turn this into a set operation? At the moment on real data this query runs through a list of about 2500 rows and creates a table with around 100,000 rows in about a minute.Many thanks for your thoughts,Oliver

Performing strange conditional COUNT

Posted: 06 Mar 2013 10:12 AM PST

Hey guys, Can someone show me how to go about solving this problem? I thought it would be pretty straightforward but turns out it's not. I made some strides but have been stuck on the last portion. Here is what I have so far. I have a table with the following data inside. Create table #m_TestTable (Name varchar(20),DateRecorded datetime)----Insert sample dataInsert into #m_TestTable Values ('John', dateadd(day,1,GetDate()))Insert into #m_TestTable Values ('John', dateadd(day,2,GetDate()))Insert into #m_TestTable Values ('John', dateadd(day,3,GetDate()))Insert into #m_TestTable Values ('John', dateadd(day,6,GetDate()))Insert into #m_TestTable Values ('John', dateadd(day,7,GetDate()))Insert into #m_TestTable Values ('John', dateadd(day,8,GetDate()))Insert into #m_TestTable Values ('John', dateadd(day,12,GetDate()))Insert into #m_TestTable Values ('John', dateadd(day,13,GetDate()))The idea is once the difference between the previous day and the current day is greater than or equal to 1, it should be counted as 1. I use the following query to get the difference in date between the current row and the previous row;With tblDifference as(Select Row_Number() OVER (Order by DateRecorded) as RowNumber,Name, DateRecorded,PointValue from #m_TestTable)Select Cur.Name,Cur.DateRecorded as CurrentDay,Prv.DateRecorded as PreviousDay,Datediff(Day, Prv.DateRecorded, Cur.DateRecorded) as DateDifferenceInto #temp1 FromtblDifference Cur Left Outer Join tblDifference PrvOn Cur.RowNumber = Prv.RowNumber + 1Order by Cur.DateRecorded--select * from #temp1Here is where I get stuck. Based on the datedifference column, I need to perform a count. The datedifference column data appears in following order. NULL, 1, 1, 3, 1, 1, 4, 1So the count for John should be 3 broken down like this. NULL, 1, 1 should count as ONE; 3, 1, 1 should count as ONE and 4, 1 should count as ONE. --But if the data was like this Create table #m_TestTable1 (Name varchar(20),DateRecorded datetime)----Insert sample dataInsert into #m_TestTable1 Values ('John', dateadd(day,1,GetDate()))Insert into #m_TestTable1 Values ('John', dateadd(day,2,GetDate()))Insert into #m_TestTable1 Values ('John', dateadd(day,3,GetDate()))Insert into #m_TestTable1 Values ('John', dateadd(day,6,GetDate()))Insert into #m_TestTable1 Values ('John', dateadd(day,7,GetDate()))Insert into #m_TestTable1 Values ('John', dateadd(day,8,GetDate()))Insert into #m_TestTable1 Values ('John', dateadd(day,9,GetDate()))Insert into #m_TestTable1 Values ('John', dateadd(day,10,GetDate()));With tblDifference as(Select Row_Number() OVER (Order by DateRecorded) as RowNumber,Name, DateRecorded from #m_TestTable1)Select Cur.Name,Cur.DateRecorded as CurrentDay,Prv.DateRecorded as PreviousDay,Datediff(Day, Prv.DateRecorded, Cur.DateRecorded) as DateDifferenceInto #temp2 FromtblDifference Cur Left Outer Join tblDifference PrvOn Cur.RowNumber = Prv.RowNumber + 1Order by Cur.DateRecorded--select * from #temp2The datedifference column data appears in following order. NULL, 1, 1, 3, 1, 1, 1, 1Then in this case the count for John would be only 2. NULL, 1, 1 would count as ONE, 3, 1, 1, 1, 1 would count as ONE. In summary, if there are '1's after a number, they count as ONE. If the number is anything other than 1 and is followed by '1's, it will count as ONE. If the number is anything other than one and is followed by non 1 numbers, they count as one each. To illustrate this last point, if the data was like thisCreate table #m_TestTable2(Name varchar(20),DateRecorded datetime)----Insert sample dataInsert into #m_TestTable2 Values ('John', dateadd(day,1,GetDate()))Insert into #m_TestTable2 Values ('John', dateadd(day,4,GetDate()))Insert into #m_TestTable2 Values ('John', dateadd(day,7,GetDate()))Insert into #m_TestTable2 Values ('John', dateadd(day,8,GetDate()))Insert into #m_TestTable2 Values ('John', dateadd(day,9,GetDate()))Insert into #m_TestTable2 Values ('John', dateadd(day,10,GetDate()))Insert into #m_TestTable2 Values ('John', dateadd(day,16,GetDate()))Insert into #m_TestTable2 Values ('John', dateadd(day,17,GetDate()));With tblDifference as(Select Row_Number() OVER (Order by DateRecorded) as RowNumber,Name, DateRecorded from #m_TestTable2)Select Cur.Name,Cur.DateRecorded as CurrentDay,Prv.DateRecorded as PreviousDay,Datediff(Day, Prv.DateRecorded, Cur.DateRecorded) as DateDifferenceInto #temp3 FromtblDifference Cur Left Outer Join tblDifference PrvOn Cur.RowNumber = Prv.RowNumber + 1Order by Cur.DateRecorded--select * from #temp3The datedifference column data appears in following order. NULL, 3, 3, 1, 1, 1, 6, 1Then the count for John would be 3. NULL, 3, will count as ONE, 3, 1, 1, 1 will count as ONE and 6, 1 will count as 1. I hope this makes sense. Thanks for your time. I know this is a strange one.

ORDER BY = Bubble Sort ? Quick Sort ? Insertion Sort ?

Posted: 06 Mar 2013 06:37 PM PST

Hi All,When We sort the data by using ORDER BY , which sorting algorithm method will be used by SQL optimizer?1) Bubble Sort2) Quick Sort3) Merge Sort4) Heap Sort5) Insertion SortInputs are welcome!

How to Convert Semi colon Separated Values into Column

Posted: 06 Mar 2013 02:29 PM PST

I am working on a integration project where I am receiving three string parameters ItemCode ItemName Amountand values will be in this format[b]ItemCod Parameter values [/b]T1;T2;T3;[b]ItemName Parameter values [/b]Pencil Box;Eraser;Mouse Pad;[b]Amount Paramter values [/b]1900;2000;8900;Now I would like to have a procedure in which i would receive these parameters and the query will convert this as column and records. I am not a SQLServer guy so somebody proposed a solution but it is limited to two records only where my requirement is as many records depends on the number of semi colon separated strings. The defined structure is that all parameters will have equal number of values.here is the solution somebody gave it to me.[code="sql"]INSERT INTO t(cod, name)VALUES('T1;T2;T3;T4;T5;', 'Pencil Box;Eraser;Board Marker;Trimmer;Ball Point;');SELECT CAST('<r>'+REPLACE(cod,';','</r><r>')+'</r>' AS XML).query('/r[1]').value('.','varchar(256)') cod, CAST('<r>'+REPLACE(name,';','</r><r>')+'</r>' AS XML).query('/r[1]').value('.','varchar(256)') nameINTO #tmpTable FROM tinsert INTO #tmpTableSELECT CAST('<r>'+REPLACE(cod,';','</r><r>')+'</r>' AS XML).query('/r[2]').value('.','varchar(256)') cod, CAST('<r>'+REPLACE(name,';','</r><r>')+'</r>' AS XML).query('/r[2]').value('.','varchar(256)') nameFROM t;select * from #tmpTable[/code][b]Here is the helper script to create required table so it wont waste your valuable time[/b][code="sql"]CREATE TABLE [dbo].[t]( [cod] [varchar](350) NULL, [name] [varchar](300) NULL) ON [PRIMARY]GO[/code]

remove duplicates IF matches

Posted: 06 Mar 2013 01:46 PM PST

So I have the following statement:SELECT ca.Caseid,[weeknum],[monthName], Response1_Num , Response2_Num, Response3_Num, Response4_Num, comments,[userlogin], [supervisor], [customer id], comm_ID FROM HMD hm join Correspondences_All ca on hm.[caseid]=ca.caseidwhere ca.supervisor ='Iamsomeone' and [comments] not in ('null','')It returns a bunch of duplicates in the comments, this is expected, but what I want to filter out is just this I need the query to return only one unique set of the comments UNLESS the userlogin is different. Here is a small subset of the select to give you an idea of what I mean.CaseID: Comments: Userlogin1243546457 Great help UserA3123234353 AWESOME UserB2131314242 Support was terrible UserC2131314242 Support was terrible UserC2131314242 Support was terrible UserC[b]2131314242 Support was terrible UserA[/b]3453453453 Could have been better UserBnote the item in bold, this is the variable I need to consider.Anyone able to help me with this one?

Delete large data from live table

Posted: 06 Mar 2013 06:48 AM PST

I have to delete data from live table( Size of DB:terabytes) (system is becoming slow). without affecting the end users ? And tables have indexes and identity columns.( when I delete , I don't want to affect the identity columns, how can we do it?)It is taking lot of time to delete the data.How can i do that?Any steps to follow.ThanksKomal

Is there a name for this concept?

Posted: 06 Mar 2013 06:05 AM PST

Hi, I am wondering if there is a standard industry name or term for this concept aside from "horrible", "bad idea", or "if you do this I (dba) will eat your first born."I remember learning this lesson on my own several years ago, but was recently reminded of this lazy way of getting things done in a stored procedure because a consultant that was hired to help our reporting team submitted a stored procedure to me that contains a where clause like this.A simple mockup of the scenario would look like this:[code="sql"]CREATE PROCEDURE GetSomeData(@Parameter1 int)ASBEGIN SELECT Field1 , Field2 , Field3 FROM SomeTable WHERE (Field4 = @Parameter OR @Parameter = -1)END[/code]The part I am asking about is the where clause, where a dummy value is passed in to the stored procedure that essentially is used to mean "return everything."Of course, this makes the query impossible to tune, as it will always do an index scan. I've already advised him that I will not be committing his stored procedure to the database and suggested several other ways they can achieve the desired result.Anyhow, is there a term used to describe this kind of where clause? Inquiring minds want to know!Thanks!
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog