Extract Data From Table Using Timestamp Range
Apr 21, 2014
There is a simple SQL table ("mytable"). Say, it has 100 rows and 5 columns. One of the columns (say "time") contains timestamps across the whole day and the data in this column has the following format: hh:mm:ssAM/PM. So, the table looks like this:
time var1 var2 var3 var4
12:00:01PM value ...
12:00:05PM value
12:00:08PM value
12:00:20PM value
12:10:12PM value
...100 rows
How to create simple SQL request for extracting data between any 2 timestamps? For example, I need sub-table of the initial table containing all data values between 12:00:05PM and 12:00:20PM:
time var1 var2 var3 var4
12:00:05PM value
12:00:08PM value
12:00:20PM value
I consider something like this:
SELECT * FROM mytable WHERE time BETWEEN ...?
View 2 Replies
ADVERTISEMENT
Oct 26, 2007
Hi,
I have created one Linked server to fetch the data from Oracle server.
I have two tables at Oracle server
1. EMP_Tbl1 (Emp_Cd VARCHAR2(10))
2. EMP_Tbl2 (EMP_ID NUMBER, Emp_Cd VARCHAR2(10))
I can connect to EMP_Tbl1 table through my linked server at SQL Server 2005.
WHILE I cann't connect to EMP_tbl2.
ERROR:
The OLE DB provider "OraOLEDB.Oracle" for linked server "Linked_Facets" supplied inconsistent metadata for a column. The column "EMPID" (compile-time ordinal 1) of object ""SYSTEM"."EMP_ASH"" was reported to have a "DBTYPE" of 5 at compile time and 130 at run time.
OR "The OLE DB provider "OraOLEDB.Oracle" for linked server "Linked_Facets" supplied invalid metadata for column "JOINING_DATE". The data type is not supported."
Reason:
EXECsp_addlinkedserver
@server='Linked_Facets'
,@srvproduct='ORACLE'
,@provider='OraOLEDB.Oracle'--'OraOLEDB.Oracle'
,@datasrc='ameri.corp.ads.valuelabs.net'
I am using 'OraOLEDB.Oracle' as the provider. This has its own limitations. How to conquer this?
View 1 Replies
View Related
May 8, 2007
Anyone know how to convert a timestamp value to datetime that I can read?
When I run this:
Code:
select * from trans
where convert(datetime , time_stamp) >= getdate()-1
--'time_stamp' is the name of the column
I get:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
(0 row(s) affected)
View 1 Replies
View Related
Nov 23, 2007
I have a time dimension that consists out of timestamp fields. Now I like to extract a value for a given month e.g. 8 for August, 10 for October and such. How would I do that?
[Time].[STARTDATE].LastChild holds this value: 2007-10-01T00:00:00
Any help is highly appreciated.
Thanks in advance
Dirk
View 8 Replies
View Related
Nov 28, 2012
Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?
The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.
convert(char(15), process_date,112) as process_dt
View 3 Replies
View Related
May 15, 2015
In SQL server I have the column doTableDate set a Datetime.I need extract all rows in on date range and I think use to syntax `Between And`If try this version of query I have in output 889 rows all with date 2014-01-03... but I have other records with date 2014-01-04 in column doTableData...
Code:
SELECT
*
FROM
doTable
WHERE
doTableDate BETWEEN CONVERT (
[code]....
View 4 Replies
View Related
Apr 16, 2004
Hello Friends,
Is there any way in sql server to get records with in a particular range. Lets say that we got 100 records satisfying a query, I want only first 20 records or records in the range 40 - 60. Kindly suggest me how to accomplish this in sqlserver and if possible provide a code snippet regarding this. Thanks in advance.
Regards,
Ch.Praveen.
View 5 Replies
View Related
Sep 24, 2015
I have a transaction table having about 40 crore rows in source. It don't have timestamp and unique key columns. It have only Bill_month and Bill_Year columns. Actually for loading this table into staging I have added a new datetime column by adding default bill_date as 01. Then
* First we delete last 3 month data from staging tables.
* Get last 3 months data from source table.
* Load that 3 months data from source to staging table.
We do this because we only get update for last three months data. Now I have to include this transaction table as Fact table in DW. What will be the best practice for loading the fact table by picking data form staging table. Also we have to look up with dimensions for Foreign Keys.
* Should I implement the same method of deleting last 3 months records and loading them again.
View 3 Replies
View Related
Mar 15, 2007
I've two table.......
One table Name Form1
Ex
----------
Name|F1No
----|-----
23wa|1
xyzx|2
abcd|3
......
......
......
---------
F1No====>primary Key
Second table Name Form2
Ex
--------
F2No
----
3
1
2
2
2
1
......
......
......
--------
F2No===>Foreign Key
i want top 3 Name(field) in first table(Form1).
Result
------
xyzx
23wa
abcd
View 7 Replies
View Related
Jan 10, 2008
I have been provided with a table where one of the columns is of TimeStamp data type. My question is how to insert and update data in this column through my SQL Statement? When I run my SQL statement, it gives me an error with this column name in the error.
View 3 Replies
View Related
Nov 21, 2007
Hi,
There's a nullable Timestamp data type column in all tables in Sage SQL database.
When using Insert SQL query add a new record, with a NULL for the Timestamp column, into a table it seems alright. When opening the table it shows the inserted data in the Timestamp column is Binary. But when reading the record by Sage program there's an error message as the following:
'Fractional truncation: table scheme.opheadm unique_no 24969592 1'.
Any idea what the problem is?
Thanks,
Yabing
View 3 Replies
View Related
Apr 28, 2014
Let us suppose I've 3 tables in which 2 columns data are same in all 3 tables & rest fields are distinct
Table 1 :
ID1 ID2 Gender Code towncode
1 12 1 234 1234
2 13 2 543 5463
89 187 1 125 N21Q
Table 2 :
ID1 ID2 Relation Name DOB
1 12 13 XYZ 21/01/1967
1 12 1 QAS 01/10/1987
1 12 2 NHS 09/12/2001
2 13 1 NHG 10/01/1987
2 13 2 GHS 16/12/1999
89 187 2 KJA 31/03/2000
89 187 1 KLA 09/11/1986
89 187 5 KOP 19/12/2001
Table 3 :
ID1 ID2 Period Date
1 12 1 01/01/2011
2 13 1 01/01/2011
89 187 2 01/01/2011
I want result as :
ID1 ID2 Gender Code towncode Relation Name DOB Period Date
1 12 1 234 1234 13 XYZ 21/01/1967 1 01/01/2011
1 12 1 234 1234 1 QAS 01/10/1987 1 01/01/2011
1 12 1 234 1234 2 NHS 09/12/2001 1 01/01/2011
2 13 2 543 5463 1 NHG 10/01/1987 1 01/01/2011
2 13 2 543 5463 2 GHS 16/12/1999 1 01/01/2011
89 187 1 125 N21Q 2 KJA 31/03/2000 2 01/01/2011
89 187 1 125 N21Q 1 KLA 09/11/1986 2 01/01/2011
89 187 1 125 N21Q 5 KOP 19/12/2001 2 01/01/2011
May be some duplicate entry of same record is available in Table 1.
View 3 Replies
View Related
May 3, 2004
Hello all,
Please help....
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
Any help would be highly appreciated.
Thanks in advance.
View 3 Replies
View Related
Jun 19, 2015
I need to extract data from SSAS' cubes into a SQL Server table.
I already read examples using Linked server (with openquery), SSIS, etc. However, the result always return as many columns per dimension as levels. I need to extract all members of a dimension in a column. E.g., when excecuting the following MDX query in Adventure Works 2014:
select [Measures].[Sales Amount] on columns,
Non Empty [Date].[Calendar].members on rows
from [Adventure Works]
I would like to get this result (MDX query in SSMS), but with keys displayed intead of names:
But I get this (MDX thru openquery or SSIS):
View 4 Replies
View Related
Feb 4, 2004
Hi all, First post on THIS forum...so be kind (I have my nomex suite on anyway, and am flameproof, but STILL!)
I have a table that consists of a bunch-o-gibberish, but essentially looks like this (for the sake of discussion):
PK RecID int
PK StartDate smalldatetime
PK EndDate smalldatetime
Gibberish_1 varchar(zillions and zillions)
Gibberish_2 int
OK, my query (get it? *LOL* I kill me) is this:
My PK date range is essentially a range indicating a "validity date range" or in other words - "this record applys to dates from StartDate to EndDate".
If I insert a new row, that I want to be "valid" from today on into the forseeable future. I'm thinking I would enter it with StartDate = today, and would like to figure out a good "MAXDATE" type value to put in the EndDate column so that:
1) I can find a record using a single date (i.e., the current date) and find the validation record (as defined above) - - - I'm not concerned about this part of the question, as I can build the simple query to do so).
2) NOT have to keep adjusting the EndDate to keep it ahead of the current date so that the query into the range will work.
Essentially, my question is this: What is a good value to put in the EndDate to mean "infinity" - - that will still work when I query using a single date designed to find the validation record with a range that includes it?
My boss suggested something like "January 1, 2040", but this bothers me, since my kids may be working here then :lol: and I don't want to have to force them into a Y2K-like issue where they now have to go adjust all the "maxdate" values. Anythoughts or conventions that I am unaware of here?
Thanks!
Paul
View 10 Replies
View Related
Dec 27, 2007
I have a query/report that I need to create that needs to look at the size of a company and based on that size apply different rules. I am sure that this is not the only query/report I'll need to do using this and I'm also not so sure that the size ranges won't be changed in the future. Given this, I'd like to store the size ranges in a lookup(global) table. That way, if the ranges ever change I can just alter them in that table and not in all of the queries/reports that use them. What I need to figure out is how to join the live table with the look up table.
Specifically, here is what I have. The look up table would be:
Code Block
CREATE TABLE #gl_sizerange (
glsid int IDENTITY(1,1) NOT NULL,
lowsize int,
highsize int,
sizecat varchar(10),
milestone varchar(25),
days int
) ON [PRIMARY]
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Approach', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Interview', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Close', 7)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Approach', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Interview', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Close', 7)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Approach', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Interview', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Close', 7)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Approach', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Interview', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Demonstrate', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Negotiate', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Close', 35)
Then what I have is two more tables, one that indicates the size of the company and another that has the milestone contained within it and I will also calculate how long that milestone has been open and if it is longer than what is in the lookup table for that milestone and size range I need it returned in the report. Here are some quick sample table to represent that data (I've condensed the size and number of tables for the example):
Code Block
CREATE TABLE #en_entity (
enid int NOT NULL,
orgsize int,
) ON [PRIMARY]
INSERT into #en_entity VALUES(1, 5)
INSERT into #en_entity VALUES(2, 18)
INSERT into #en_entity VALUES(3, 24)
INSERT into #en_entity VALUES(4, 25)
INSERT into #en_entity VALUES(5, 47)
INSERT into #en_entity VALUES(6, 101)
INSERT into #en_entity VALUES(7, 499)
INSERT into #en_entity VALUES(8, 500)
INSERT into #en_entity VALUES(9, 10000)
INSERT into #en_entity VALUES(10, 567890)
CREATE TABLE #op_opportunity (
opid int NOT NULL,
enid int NOT NULL,
milestone varchar(25),
daysopen int
) ON [PRIMARY]
INSERT into #op_opportunity VALUES(1, 1, 'Approach', 5)
INSERT into #op_opportunity VALUES(2, 2, 'Interview', 18)
INSERT into #op_opportunity VALUES(3, 4, 'Negotiate', 24)
INSERT into #op_opportunity VALUES(4, 7, 'Demonstrate', 25)
INSERT into #op_opportunity VALUES(5, 7, 'Approach', 7)
INSERT into #op_opportunity VALUES(6, 9, 'Close', 35)
INSERT into #op_opportunity VALUES(7, 8, 'Close', 36)
So, given the sample data, I would expect the results to return me the following opids from the #op_opportunity table because they don't comply with what is in the look up table based on milestone, size and days open: 2,3,4,7
View 4 Replies
View Related
Jun 25, 2014
What I am trying to do, Extract the data from SQL table and Insert in Email Body and email to user. I got good article on Internet, I follow all steps as it is, but still I am getting error.
Here is the link : [URL] ....
But I am getting Error:
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
at System.String.Format(IFormatProvider provider, String format, Object[] args)
at ST_7f59d09774914001b60a99a90809d5c5.csproj.ScriptMain.Main()
[Code] ....
View 4 Replies
View Related
Jun 19, 2007
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
View 6 Replies
View Related
Jun 23, 2015
date time s-sitename TimeTaken(Seconds)
6/8/2015 10:56:26 TestSite 100
6/8/2015 10:56:26 TestSite 500
6/8/2015 10:56:26 TestSite 800
6/9/2015 11:56:26 TestSite 700
6/9/2015 11:56:26 TestSite 200
6/12/2015 12:56:26 TestSite 700
I have a table with above values, I am looking for a sql query to find AvgTimeTaken at different time stamps and total count of each time stamp
Output
date time s-sitename TimeTaken(Seconds) Count_of_Request
6/8/2015 10:56:26 TestSite 1400 3
6/9/2015 11:56:26 TestSite 900 2
6/12/2015 12:56:26 TestSite 700 1
View 5 Replies
View Related
Jun 13, 2006
Hi everyone,
I have to extract, dayly a list of contacts on a exchange server in a table on our EDW on sql server 2005. Is it possible to get the information directly from a dataflow or i will have to developpe a script task ?
Need help desperatly !!!
View 3 Replies
View Related
Mar 28, 2008
Hi All,
I want to export data from SQL Server2005 to an Excel spreadsheet thru "Data Flow Task". I am using OLE DB for SQL Server for the source connection and a Connection To Excel as my destination source. The Excel spreadsheet (2003) exists and has the first row with column names. I don't have any warnings before trying to execute.
The SQL datable fileds are
i) ID - Int
ii) RefID
iii) txtRemarks - nvarchar(MAX)
iv) ddlWaterLevel - nvarchar(50)
While executing the tasks, I got the error
Error: 0xC0202025 at Data Flow Task, Excel Destination [427]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Destination" (427) failed the pre-execute phase and returned error code 0xC0202025.
After analysing I found in the DataFlow --> Excel destination --> Advanced Editor for Excel Destination, the default data type for txtRemarks shows as "Unicode string [DT_WSTR]". But this is supposed to be "Unicode text stream [DT_NTEXT]". Even if I change the data type in the design time, It doesn't accept.
Please do help me out.
thanks
Sanra
View 4 Replies
View Related
Aug 21, 2015
I'm working on archiving data from some tables. I've duplicated the data structure, with the exception of not including the IDENTITY specifier on INT columns, so that the archive table will keep the value that was generated in the original table. This is all going well, until I tried to copy the data over where the column is specified as a timestamp data type. I've looked this up and found a couple of things. First, documentation for SQL 2000 says,
Timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. Timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
And then documentation for the soon to be released SQL 2016 on the rowversion data type says,
The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
and
Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time.
OK, I've read the descriptions, but I don't get it. Why have a timestamp/rowversion data type?
View 9 Replies
View Related
Oct 7, 2005
Is there any reason to have a row that is the PK/Identity and a rowthat is datatype Timestamp in the same table?Does this in any way help speeding up row updates?Thanks,lq
View 7 Replies
View Related
May 27, 2008
Hi,
I've heard that in Oracle, tables have an implicitly column that stores the last modified timestamps for each record.
Using this timestamp i can find out all the newly inserted/updated records in a table.
Do we have something similar in SQL server. Or is there a method to find out newly inserted/updated/deleted records ?
Thanks,
Deep GZ.
View 4 Replies
View Related
Mar 6, 2002
Hi!
I have timestamp column and as you know the data there is not in readable format. what should I do to get normal date to find out when row was updated by user.
Thank you in advance,
Nadia.
View 1 Replies
View Related
Sep 11, 2013
how you code to get a timestamps to a temp table.ex. select date from clock where name='name' and nbr='10';I want this data to be in my temp date table..
View 1 Replies
View Related
Sep 12, 2003
Hi,
--sql server 2000
I have a table that has say 100 rows and 4 columns, 4th
one being datatype timestamp.
when a row is inserted the timestamp column also has a
value inserted.My understanding is that whenever the row
is updated(say any of the other three column values are
updated)the timestamp value also changes.
My question is suppose the same row is updated thrice at 3
different times.Will the timestamp VALUE after the last
update guarenteedly be greater than the ones for the early
2 updates..Eg
End of Update1 :Value1(timestamp)
End of Update2 :Value2(timestamp)
End of Update3 :Value3(timestamp)
Does sql server guarantee that Value3>Value2>Value1 ???
If this is true can I use this in any business logic?
Any help highly appreciated !
TIA
Kinnu
View 2 Replies
View Related
Feb 15, 2007
In a database supplied by a vendor, I'm trying to export to our test database several tables to which we've added data but I keep running into an error 'trying to insert row version column'. The vendor has included a timestamp column in every table. What I need to do is exclude that column from the export-hopefully without writing explicit SQL for every table.
Suggestions?
View 2 Replies
View Related
Jul 20, 2005
HiI'm interested in using the timestamp data type & I have some questions.As far as I can understand the contant of a timestamp column is a binaryvalue.Is there any connection between that value and a valid date (as the wordtimestamp means) or is it a left over from the days when the timestampvalue really was a datetime type (so it says in Books Online) ?or is it just a unique identification of a row (a tuple id) ?I tried converting a timestamp value to datetime and I got a date in 1900.Thanks for any answerDavid Greenberg
View 1 Replies
View Related
Dec 2, 2015
I would like to update the flag of the promotion ID should the promotion ID date range overlap with Promotion ID(All) Date Range. The general logic is as below.
Update TableName
SET PromotionID Flag = 1 AND Reason = 'Overlap with row ID(Overlap row ID number)'
Where EACH ROW(Except with Promotion ID ALL) Date Range Overlap with ROW(with promotion ID ALL) Date range
Note: ROW is Partition By ColumnA,ColumnB
TableName: PromotionList
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 0 NULL
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 0 NULL
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
Expected outcome after performing update on the table
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 1 Overlap with row ID 4
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 Overlap with row ID 5
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
View 4 Replies
View Related
Jan 22, 2008
I have a package which establishes connection with DB2 server.I dont have any db2 application where i can format query for db2.This is my query in access/sql.
can someone help me converting it into a db2 query.i tryed to google and use the functions but i failed and even the error that ssis gives don't help asthey are same for any type of error.
Thanks in ADV
SELECT table1.YYYY & table1.MM as MO_YR,
table2.CNTRYCD AS CNTRY_CD,
Sum(table1.AMT) AS [VALUE]
FROM table1
INNER JOIN table2
ON (table1.MM = table2.MM)
AND (table1.YYYY = table2.YYYY)
WHERE (table1.YYYY BETWEEN YEAR(DATE()) AND YEAR(DATEADD("m",-3,DATE())))
AND (table1.MM BETWEEN MONTH(DATE()) AND MONTH(DATEADD("m",-3,DATE())))
GROUP BY table2.CNTRYCD, table1.YYYY, table1.MM
View 2 Replies
View Related
Jul 23, 2005
I need to get some data from an enormous, creaky old SQL 6.5 database.I know nothing about either the data schema (though I believe some sortof documentation exists), nor 6.5 for that matter, having come to SQLServer at 7.0.My clients need the data in comma delimited format.Please, can anyone suggest any possibilities? One thing that occurredto me might be to create an Access application, use an ODBC link to theSQL DB, and then leverage Access' not inconsiderable functionality toget the data out.Does anyone foresee any problems with this, or any better ways?Forever in your debt.Edward--The reading group's reading group:http://www.bookgroup.org.uk
View 2 Replies
View Related
Jan 10, 2008
I have the following data example,
Street
Forest Ridge Dr
Whites St
Alba Rd
I wish to achieve the following,
Street StreetSuffix
Forest Ridge Dr
Whites St
Alba Rd
I have the following code
INSERT Addr2 (AddressID, LotNo, FlatNumber, HouseNum, Street, StreetSuffix, Locality,
PostCode, [State])
SELECT LotNo, FlatNumber, HouseNum, SUBSTRING(Street, 1, (PATINDEX('% %', Street))),
LTRIM(SUBSTRING(Street, (PATINDEX('% %', Street)), 20)), Locality, PostCode, [State]
FROM Addr1
But i get the following result which is no good!
Street StreetSuffix
Forest Forest
Whites Whites
Alba Alba
What am I doing wrong in the Street & StreetSuffix Functions above?
I have this working with a cursor but I'm trying to work out a set based solution as the cursor
takes way to long to complete.
In the Cursor I'm performing the code below per row of AddressID
SET @SpaceLength = (SELECT PATINDEX('% %', @Street))
SET @Suffix = (SELECT SUBSTRING(@Street, @SpaceLength, 20))
SET @Street = (SELECT SUBSTRING(@Street, 1, @SpaceLength))
View 6 Replies
View Related