Wednesday, June 12, 2013

[T-SQL] Identify postcode from multiple fields

[T-SQL] Identify postcode from multiple fields

Identify postcode from multiple fields

Posted: 11 Jun 2013 07:53 PM PDT

Hello,I have a table with five fields used to hold address data. Due to poor practices the users have been able to enter any data in any field. They have now asked me to find the post/zip code from each record for extracting to another application. Any tips on how I can identify the post/zip code and extract to a separate table?At the moment I have a VB6 application that uses regular expressions to perform this role, but I'm interested to see if I can now do this in T-SQL as I'd like to drop the VB6 app, or at a minimum replace it with something .NET based.I'm running an instance of SQL Server 2008 R2 to host the db.

Performance when predicate is < Greater than ...

Posted: 12 Jun 2013 12:53 AM PDT

My table consists of 4 location IDs, each with a sensor reading value for each minute (approx) of the day, over a period of 1 month. (178,000 rows +/-)CREATE TABLE [CJ_LBSVTP].[IP21_Import_PowerTier]( [locationID] [char](3) NOT NULL, [stamp] [datetime] NOT NULL, [value] [float] NULL)... and my query has to create a start & end time from the above.So, if the table rows are thus ...Location Time Value1234 2013-03-01 00:01 981234 2013-03-01 00:02 97The results should be ...Location StartTime EndTime1234 2013-03-01 00:01 2013-03-01 00:02My query code to do this is below, but it is taking a horrendously long time to execute.Have been playing with indexes for some time now and simply cannot get a decent time out of this one.Are there any 'tips' when doing such a query utilizing a '<' in the predicate ?Many Thanks--------- Query Code ---SELECT AA.LocationID , AA.stamp AS startStamp , MIN(BB.stamp) AS endStampFROM myTable AS BB INNER JOIN myTable AS AA ON BB.LocationID = AA.LocationID AND BB.stamp > AA.stampGROUP BY AA.LocationID , AA.stamp

can any one give me the answer of my query why I am getting the error message

Posted: 11 Jun 2013 09:32 PM PDT

use [12]goCREATE TABLE Test1(TestID uniqueidentifier CONSTRAINT Test_TestID_Default DEFAULT newsequentialid(),Inserted datetime CONSTRAINT Test_Inserted_Default DEFAULT getdate())ERROR MESSAGE Msg 2714, Level 16, State 4, Line 1There is already an object named 'Test_TestID_Default' in the database.Msg 1750, Level 16, State 0, Line 1Could not create constraint. See previous errors.I am executing the upper query ,but the below error message shown .

Calculate Previous Business Day Exclude Saturday, Sunday and Holiday

Posted: 11 Jun 2013 09:41 PM PDT

Hi,My name is Kashif, I am very new in sql server, I want create a UDF function that will return a previous date (exclude saturday, sunday and holiday)Holiday's list in a table called 'tblHoliday'Please help me to get desired result.ThanksKashif

Can we call stored Procedure inside a function

Posted: 11 Jun 2013 08:57 PM PDT

Hi, Can we call stored Procedure inside a functionThanks

qry options

Posted: 11 Jun 2013 03:56 AM PDT

I want to do this.select count(*) from (select col1,col2,......col18 from REVemployee.tbempgroup by col1,col2,......col18 HAVING COUNT(*)>1)ais there a better way of doing the same for better proformance?

Simple MAX Query not working

Posted: 11 Jun 2013 07:05 PM PDT

Im confused on why this query is not working..Data -vend_i dept tot_wgt 1 1 752 2 403 4 503 5 80result I needed vend_i dept1 12 23 5Basically I need largest dept by each vendor which has served more...we can use tot_wgt column to find the large dept/can anyone please provide a query to get above result...plss.its very urgent

inline table function

Posted: 11 Jun 2013 05:09 AM PDT

I need to create function that returns a concatenated street.I first used a scalor funciton, but I read from internet inline table funciton is better.So how can I convert my scalor function to table function, how can I cal lthe functin by join other tables, also is it possible I can not only get one student street, also get a group of student's street by calling the function?I attached the script

Variable Kills Performance

Posted: 11 Jun 2013 01:33 AM PDT

Well I was stumped for a title to this, as I am stumped as to the cause !With the code below, the issue is in the where statement "LB.DB = @DB".As written, this variable is equal to the string 'NA', and the execution time is horrendous.However, if I hard code "LB.DB = 'NA'" , then the code executes in about 2 minutes, which is as expected.For the life of me, I cannot figure out WHY the execution is affected by the use of a variable to carry in the required 'where' value.If it helps, the field [DB] is a char(2), originating 3 levels of nesting down in a set of views.Any insights as to what is happening here would be appreciated !!Many ThanksSimon------------------------------------------------------------------- Declare @DB as char(2)select @DB = 'NA'SELECT LB.DB, LB.CaseKey AS [Case Key], LB.Month AS [Month] , LB.RollUpClass AS [Product], LB.SourceOfProduct as [Plant] , LB.DeliveredQty AS [Delivered Qty]into #LBFROM [CJ_LBSVTP].[3_LB_ProductSourcePlant] AS LB WHERE (LB.CaseKey = 8751) AND (LB.Month = CONVERT(datetime,'2012-10-01')) And (LB.DB = @DB)

How to generate Sequence numbers in a temporary table?

Posted: 11 Jun 2013 04:18 AM PDT

I am trying to extract data from multiple table joins into a temporary table, so that I will be able to count Occupancy and Vacancy parking transactions based on starttime. The problem I am having is the sequence of occurrences for each transaction in not accurate in the database. My solution is to create a temporary table and generate my own sequence numbers. I did generate my own sequence numbers, however the numbers are not in sequence and I am also getting duplicates.See sample code below as well as data output (Excel Spreadsheet attached) with inconsistent sequence numbers. If anyone knows how to fix this issue, please provide an example. As you can see, the SN numbers keep starting over at 1 as soon as it gets to a certain point.Any help would be greatly appreciated.SAMPLE CODE IF OBJECT_ID ('tempdb..#Tmp1') IS NOT NULL DROP TABLE #Tmp1 --all transactions + SNCREATE TABLE #Tmp1( [SN] [int] , [ParkingSpaceId] [int], [MeterId] [int], [BlockFaceID] [int], [occupancystatus] [int], [StartTime_PT] [datetime], [LastSensorEvent_PT] [datetime], [State] [nvarchar](50) ) insert #Tmp1SELECT ROW_NUMBER() OVER (PARTITION BY ST.ParkingSpaceId ORDER BY StartTime) AS SN, ST.ParkingSpaceId, MeterId, [BlockFaceID] , [occupancystatus], st.StartTime as StartTime_PT, PS.LastSensorEvent as [LastSensorEvent_PT], ST.[State]FROM SensorTransactions STjoin ParkingSpaces ps on ps.ParkingSpaceId=ST.ParkingSpaceIdWHERE BlockfaceId = 996 AND dateadd(HH,-7,Starttime) between '2013-05-28 08:00:00.000' and '2013-05-28 20:00:00.000' AND state in('Occupied','vacant')ORDER by starttimeselect * from #Tmp1 OUTPUT - Excel Spreadsheet is also attached.SN ParkingSpaceId MeterId BlockFaceID occupancystatus StartTime_PT LastSensorEvent_PT State1 6366 5775 996 1 5/28/13 10:21 6/10/13 19:57 OCCUPIED2 6366 5775 996 1 5/28/13 10:22 6/10/13 19:57 VACANT3 6366 5775 996 1 5/28/13 10:51 6/10/13 19:57 OCCUPIED1 6367 5776 996 1 5/28/13 8:44 6/10/13 16:44 OCCUPIED2 6367 5776 996 1 5/28/13 8:50 6/10/13 16:44 VACANT3 6367 5776 996 1 5/28/13 11:01 6/10/13 16:44 OCCUPIED1 6370 5779 996 1 5/28/13 8:12 6/10/13 19:37 OCCUPIED2 6370 5779 996 1 5/28/13 8:14 6/10/13 19:37 VACANT3 6370 5779 996 1 5/28/13 9:45 6/10/13 19:37 OCCUPIED4 6370 5779 996 1 5/28/13 9:45 6/10/13 19:37 VACANT5 6370 5779 996 1 5/28/13 9:56 6/10/13 19:37 OCCUPIED1 6371 5780 996 1 5/28/13 8:19 6/10/13 16:39 OCCUPIED2 6371 5780 996 1 5/28/13 17:59 6/10/13 16:39 VACANT3 6371 5780 996 1 5/28/13 18:02 6/10/13 16:39 OCCUPIED1 6372 5781 996 1 5/28/13 10:38 6/10/13 19:21 VACANT2 6372 5781 996 1 5/28/13 10:54 6/10/13 19:21 OCCUPIED3 6372 5781 996 1 5/28/13 12:53 6/10/13 19:21 UNKNOWN4 6372 5781 996 1 5/28/13 13:19 6/10/13 19:21 OCCUPIED1 6373 5782 996 1 5/28/13 8:27 6/10/13 19:31 OCCUPIED2 6373 5782 996 1 5/28/13 8:30 6/10/13 19:31 VACANT3 6373 5782 996 1 5/28/13 8:54 6/10/13 19:31 OCCUPIED

Contains fulltext search (contains) does not work within a stored procedure.

Posted: 11 Jun 2013 04:53 AM PDT

Greetings,The server info is shown below:Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)I have written a stored procedure to be used with a report that must search several columns for keywords. The keywords are stored in a table and I'm declaring a variable to load them and prep the search string as seen below:[code="sql"]declare @Keywords varchar(8000)=N''select @Keywords=(select '|' + char(34)+'*'+keyword+'*'+char(34) from LocalM5.dbo.TMKeyword where keyword<>'T&M' FOR XML PATH(''))SELECT @Keywords = STUFF(@Keywords, 1, 1, '')select * from TMQueue t1 WHERE CONTAINS ((t1.[cust_prob_descr],t1.[MyNote]),@Keywords)[/code]If this is executed in a procedure or stand-alone anywhere after other statements that execute such as populating the table "TMQueue" the query will return no results. If I populate that table and just execute the block from declare to the select statement I get data. So, the first thing I did was print the value for @keywords and build the statement so it executes with no variables like:[code="sql"]select * from TMQueue t1 WHERE CONTAINS ((t1.[cust_prob_descr],t1.[MyNote]),'"*Billable*"|"*Boom*"|"*Bucket*"|"*Cable*"|"*Coax*"|"*DOA*"|"*Fiber*"|"*Fluke*"|"*Ladder*"|"*Lift*"|"*Lightning*"|"*Materials*"|"*Not Onsite*"|"*Rental*"|"*Scissor*"|"*Scope*"|"*Water*"')[/code]Even this does not work within a procedure or if called after any statements that execute before it. I have never seen an issue like this and I don't understand why it executes fine if executed "alone" but not as part of a block.

Stored proc exit

Posted: 11 Jun 2013 02:18 AM PDT

Hello,I am new to tsql coding and I am in the process of adding a piece of code to an already existing stored proc. Here is my codeselect @Count1=count(*) from [test] where [testcol] = 10select @Count2=count(*) from [test1] where [testcol] = 10if @Count1>0 or @Count2>0then exit the storedproc and dont process any further code in the stored proc. If only the condition fails above the stored proc shd be process. How do I achieve this?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog