query assistance: contains

Showing posts with label contains. Show all posts

Friday, March 30, 2012

query help

I have a name column that contains both first and last
names:
Col1
John Doe
I'd like to split it into two columns, a first and
lastname:
firstname lastname
-- --
John Doe
Anyone have any easy way to do this?
Do you *always* have two words, separated by a space? I.e., what does your data look like?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:c07e01c47a31$4e2ec290$a601280a@.phx.gbl...
> I have a name column that contains both first and last
> names:
> Col1
> --
> John Doe
> I'd like to split it into two columns, a first and
> lastname:
> firstname lastname
> -- --
> John Doe
> Anyone have any easy way to do this?
|||For the most part. There are some names that contain a
middle inital..
the data looks like this:
John Doe
Jane Doe
George W Bush
Bill Clinton
John Kerry
Jim Bob Smith
etc....
I'm not overly concerned with getting everything perfect.
To be honest, I'd be cool with just the first names.
>--Original Message--
>Do you *always* have two words, separated by a space?
I.e., what does your data look like?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:c07e01c47a31$4e2ec290$a601280a@.phx.gbl...
>
>.
>
|||This should get you started:
CREATE TABLE Presidents (
FullName varchar(50)
)
GO
INSERT INTO Frog VALUES ('George W Bush')
INSERT INTO Frog VALUES ('Bill Clinton')
INSERT INTO Frog VALUES ('Ronald Reagan')
INSERT INTO Frog VALUES ('George H Bush')
INSERT INTO Frog VALUES ('Gerald Ford')
INSERT INTO Frog VALUES ('Richard Nixon')
GO
SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
CASE
WHEN PATINDEX('% _ %', FullName) > 0
THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1, 1)
ELSE ''
END AS 'MI',
RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last Name'
FROM Presidents
You can look up the various pieces used.
CHARINDEX
PATINDEX
SUBSTRING
REVERSE
CASE
Rick Sawtell
MCT, MCSD, MCDBA
|||Ummm. Change the INSERT INTO commands to reflect the Presidents table...
Sorry bout that.
Rick
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> This should get you started:
> CREATE TABLE Presidents (
> FullName varchar(50)
> )
> GO
> INSERT INTO Frog VALUES ('George W Bush')
> INSERT INTO Frog VALUES ('Bill Clinton')
> INSERT INTO Frog VALUES ('Ronald Reagan')
> INSERT INTO Frog VALUES ('George H Bush')
> INSERT INTO Frog VALUES ('Gerald Ford')
> INSERT INTO Frog VALUES ('Richard Nixon')
> GO
>
> SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
> CASE
> WHEN PATINDEX('% _ %', FullName) > 0
> THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1,
1)
> ELSE ''
> END AS 'MI',
> RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last
Name'
> FROM Presidents
>
> You can look up the various pieces used.
> CHARINDEX
> PATINDEX
> SUBSTRING
> REVERSE
> CASE
> Rick Sawtell
> MCT, MCSD, MCDBA
>
|||Cool, that did it. One other thing though... Could the
same be used for an address column? I used the same
syntax, but ran into an issue...
The column has a street address:
123 N. Main St.
I used the SQL and pulled the house number, directional,
and suffix, but lost the street name. Any help?
Thanks!

>--Original Message--
>Ummm. Change the INSERT INTO commands to reflect the
Presidents table...[vbcol=seagreen]
>Sorry bout that.
>
>Rick
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
AS 'First Name',[vbcol=seagreen]
(' ', FullName) +1,[vbcol=seagreen]
>1)
(FullName)) - 1) AS 'Last
>Name'
>
>.
>
|||Ummm..
Use the SUBSTRING function to get everything to the right of your
directional. Then apply the same CHARINDEX or PATINDEX functions to the
return value you are looking for from the return value of the SUBSTRING
function.
On a separate note... SQL really isn't the best choice to be doing
procedural language things like this.
If you dumped everything to a text file and used VBScript, you could
probably get this thing hashed out more quickly.
Rick
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:c45a01c47a48$9c9aef50$a301280a@.phx.gbl...[vbcol=seagreen]
> Cool, that did it. One other thing though... Could the
> same be used for an address column? I used the same
> syntax, but ran into an issue...
> The column has a street address:
> 123 N. Main St.
> I used the SQL and pulled the house number, directional,
> and suffix, but lost the street name. Any help?
> Thanks!
> Presidents table...
> AS 'First Name',
> (' ', FullName) +1,
> (FullName)) - 1) AS 'Last

Query Help

I have a query that returns a recordset that contains this kind of data:

Description Rate Amount

item1 2 .32

item2 3 1.34

item3 5 12.33

I want change the query to put all this data in ONE single field. What is the best why to do this?

Thanks!

What the result will look like in the ONE single field?

|||

item1 2 .32 item2 3 1.34 item3 5 12.33

Just all these dumped into one field like above. However id like to use the final query in and update so i cant simply concatenate each field then each row and save it in a variable.

I have the query below but i cant use it in an update for multiple records without using a fetch, or at least I cant figure out a better way.

DECLARE @.blob varchar(2000)

SELECT
@.blob = ISNULL(@.blob,'') + (def.[description] + ' ' +
CAST(sd.amount as varchar(10)) + ' ' +
CAST(sd.minutes as varchar(10)) + CHAR(13) + CHAR(10) )
FROM
section_detail sd,
def_section_detail_description def
WHERE
sd.def_section_detail_descr_id = def.def_section_detail_descr_id
and sd.section_id = 2274893453

Query Help

Dear All,
I have a single table name "Remark". It contains the item code, date,
status and remark. If I want to build a query that select items out which
the latest status is still in "pending" for example. How do I make it?
Thanks
Best Rdgs
EllisEllis
SELECT <columns list>
FROM Remarks WHERE[date]=(SELECT TOP 1 [date]
FROM Remarks R WHERE
R.Itemcode=Remarks.Itemcode
ORDER BY [date] DESC)
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:uSSJ$UXbFHA.464@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I have a single table name "Remark". It contains the item code,
date,
> status and remark. If I want to build a query that select items out which
> the latest status is still in "pending" for example. How do I make it?
> Thanks
> Best Rdgs
> Ellis
>sql

Wednesday, March 28, 2012

query help

Query help

Here is what I am trying to do... I have a phone list of all our stores. It
contains managers home and cell numbers for emergencies. So the list is
about 44 stores and about 7 to 8 managers to each store...so that's about
350 or so numbers on this list... Now our store each have there own unique
IP address, ex. Store 2 would be 192.168.2.' So within the phone list I
have, for each store I have stored the Ip like 192.168.2 or 192.168.6 and
so on.. Well what I want to do is try to build a query that will only return
records that are similar to the users IP... so I have a hidden request for
the IP... Request.ServerVariables("REMOTE_ADDR") which I want to use as a
variable in my query to compare their ip with what's in the table..
So the results would be like this...
My IP 192.168.6.15
I should see ONLY records with 192.168.6 as part of the IP.
Any suggestions? Ideas? not sure if this would be the place to post this,
but would appreciate any help.You might want to try the LIKE operator, read more in Books Online.
--
Regards,
Eric Garza
AMIGE
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uRdrbBByEHA.1404@.TK2MSFTNGP11.phx.gbl...
> Here is what I am trying to do... I have a phone list of all our stores.
It
> contains managers home and cell numbers for emergencies. So the list is
> about 44 stores and about 7 to 8 managers to each store...so that's about
> 350 or so numbers on this list... Now our store each have there own unique
> IP address, ex. Store 2 would be 192.168.2.' So within the phone list I
> have, for each store I have stored the Ip like 192.168.2 or 192.168.6 and
> so on.. Well what I want to do is try to build a query that will only
return
> records that are similar to the users IP... so I have a hidden request for
> the IP... Request.ServerVariables("REMOTE_ADDR") which I want to use as a
> variable in my query to compare their ip with what's in the table..
> So the results would be like this...
> My IP 192.168.6.15
> I should see ONLY records with 192.168.6 as part of the IP.
> Any suggestions? Ideas? not sure if this would be the place to post this,
> but would appreciate any help.
>|||Daniel_Cha wrote:
> Here is what I am trying to do... I have a phone list of all our
> stores. It contains managers home and cell numbers for emergencies.
> So the list is about 44 stores and about 7 to 8 managers to each
> store...so that's about 350 or so numbers on this list... Now our
> store each have there own unique IP address, ex. Store 2 would be
> 192.168.2.' So within the phone list I have, for each store I have
> stored the Ip like 192.168.2 or 192.168.6 and so on.. Well what I
> want to do is try to build a query that will only return records that
> are similar to the users IP... so I have a hidden request for the
> IP... Request.ServerVariables("REMOTE_ADDR") which I want to use as a
> variable in my query to compare their ip with what's in the table..
> So the results would be like this...
> My IP 192.168.6.15
> I should see ONLY records with 192.168.6 as part of the IP.
> Any suggestions? Ideas? not sure if this would be the place to post
> this, but would appreciate any help.
It sounds like your IP address is stored in character format, and that's
good. To select only those stores that match your specs, use:
Select store_information
From Stores
Where IP LIKE '192.168.6.%'
Or if you are using a parameter:
@.IP as varchar(12)
Set @.IP = @.IP + '%'
Select store_information
From Stores
Where IP LIKE @.IP
David Gugick
Imceda Software
www.imceda.com|||ok, here is my sql statement...
SELECT *
FROM tbEmerStore, tbIpRange
WHERE tbEmerStore.Store = tbIpRange.StoreNum AND tbIpRange.IpRange LIKE
'varIP'
ORDER BY Store
Variable is Name= varIP Run-Time Value =Request.ServerVariable("REMOTE_ADDR")
what am i doing wrong?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23fMvpGByEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Daniel_Cha wrote:
> > Here is what I am trying to do... I have a phone list of all our
> > stores. It contains managers home and cell numbers for emergencies.
> > So the list is about 44 stores and about 7 to 8 managers to each
> > store...so that's about 350 or so numbers on this list... Now our
> > store each have there own unique IP address, ex. Store 2 would be
> > 192.168.2.' So within the phone list I have, for each store I have
> > stored the Ip like 192.168.2 or 192.168.6 and so on.. Well what I
> > want to do is try to build a query that will only return records that
> > are similar to the users IP... so I have a hidden request for the
> > IP... Request.ServerVariables("REMOTE_ADDR") which I want to use as a
> > variable in my query to compare their ip with what's in the table..
> >
> > So the results would be like this...
> >
> > My IP 192.168.6.15
> > I should see ONLY records with 192.168.6 as part of the IP.
> >
> > Any suggestions? Ideas? not sure if this would be the place to post
> > this, but would appreciate any help.
> It sounds like your IP address is stored in character format, and that's
> good. To select only those stores that match your specs, use:
> Select store_information
> From Stores
> Where IP LIKE '192.168.6.%'
> Or if you are using a parameter:
> @.IP as varchar(12)
> Set @.IP = @.IP + '%'
> Select store_information
> From Stores
> Where IP LIKE @.IP
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Daniel_Cha wrote:
> ok, here is my sql statement...
> SELECT *
> FROM tbEmerStore, tbIpRange
> WHERE tbEmerStore.Store = tbIpRange.StoreNum AND tbIpRange.IpRange
> LIKE 'varIP'
> ORDER BY Store
> Variable is Name= varIP Run-Time Value => Request.ServerVariable("REMOTE_ADDR")
> what am i doing wrong?
>
You are not using a variable. You are using the literal 'varIP'. 'varIP'
is not a variable.
Also, you should never use SELECT * in a production environment. It's
considered bad practice because you almost always end up returning more
columns than you really need. Even if you need them all, specify each
one in the query.
Review my example again.
--
David Gugick
Imceda Software
www.imceda.com|||I'm assuming that the varIP is storing the full octets, what does the
tbIpRane.IpRange field data look like?
I'm also assuming you are sending ad hoc T-SQL to the server instead of
using a stored procedure. I'll also assume that you are only storing the
truncated octet in the database. If this is so, modify your query, thusly:
varSQL = "
SELECT *
FROM tbEmerStore AS es
INNER JOIN
tbIpRange AS ir
ON es.Store = ir.StoreNum
WHERE ir.IpRange = " & LEFT(varIP, 12) & "
ORDER BY es.Store
"
conn.Execute(varSQL)
This is probably not exact given the line breaks, but you should be able to
get the point.
To make life easier, I would strongly recommend that you use fixed-length,
zero-padded IP addresses and subnets; otherwise, you are going to have to do
some INSTR pattern matching to find the last "." octet seperator to determine
if the characters you need to truncate is 1, 2, or 3--that would suck.
Sincerely,
Anthony Thomas
"Daniel_Cha" wrote:
> ok, here is my sql statement...
> SELECT *
> FROM tbEmerStore, tbIpRange
> WHERE tbEmerStore.Store = tbIpRange.StoreNum AND tbIpRange.IpRange LIKE
> 'varIP'
> ORDER BY Store
> Variable is Name= varIP Run-Time Value => Request.ServerVariable("REMOTE_ADDR")
> what am i doing wrong?
>
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23fMvpGByEHA.2540@.TK2MSFTNGP09.phx.gbl...
> > Daniel_Cha wrote:
> > > Here is what I am trying to do... I have a phone list of all our
> > > stores. It contains managers home and cell numbers for emergencies.
> > > So the list is about 44 stores and about 7 to 8 managers to each
> > > store...so that's about 350 or so numbers on this list... Now our
> > > store each have there own unique IP address, ex. Store 2 would be
> > > 192.168.2.' So within the phone list I have, for each store I have
> > > stored the Ip like 192.168.2 or 192.168.6 and so on.. Well what I
> > > want to do is try to build a query that will only return records that
> > > are similar to the users IP... so I have a hidden request for the
> > > IP... Request.ServerVariables("REMOTE_ADDR") which I want to use as a
> > > variable in my query to compare their ip with what's in the table..
> > >
> > > So the results would be like this...
> > >
> > > My IP 192.168.6.15
> > > I should see ONLY records with 192.168.6 as part of the IP.
> > >
> > > Any suggestions? Ideas? not sure if this would be the place to post
> > > this, but would appreciate any help.
> >
> > It sounds like your IP address is stored in character format, and that's
> > good. To select only those stores that match your specs, use:
> >
> > Select store_information
> > From Stores
> > Where IP LIKE '192.168.6.%'
> >
> > Or if you are using a parameter:
> >
> > @.IP as varchar(12)
> >
> > Set @.IP = @.IP + '%'
> > Select store_information
> > From Stores
> > Where IP LIKE @.IP
> >
> >
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >
>
>|||Ok, I what is stored in the database is the following:
Table = tbIpRange
Column Name Value
StoreNum 02 San Antonio
IPRange 192.168.2
Table = tbEmerStore
Column Name Value
Store 02 San Antonio
So I have one table that contains this information for each store, since
this will not change unless we add a new location to the company.
In the table that contains all the information there is a column that
contains the same StoreNum value and its called Store
So my select statement looks like this..
SELECT Store, StoreNum
FROM tbEmerStore, tbIpRange
WHERE tbEmerStore.Store = tbIpRange.StoreNum AND 'varIP' LIKE
tbIpRange.IpRange
my variable varIP has a run-time value of
Request.ServerVariable("REMOTE_ADDR") so that I can capture the users IP
and compare it to the table.
Im not all that great with queries and variables... so im not sure what im
doing wrong...and our SQL guru is out on vacation since he just had a little
boy this week.. so I dont have anyone to help me with this. So I want to
compare the REMOTE_ADDR to the IPRange column.. and if they are alike then
ONLY return the stores that have that IP Range.
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:89C657FA-CA4F-4C8C-BF08-3C9447F6C1B5@.microsoft.com...
> I'm assuming that the varIP is storing the full octets, what does the
> tbIpRane.IpRange field data look like?
> I'm also assuming you are sending ad hoc T-SQL to the server instead of
> using a stored procedure. I'll also assume that you are only storing the
> truncated octet in the database. If this is so, modify your query,
thusly:
> varSQL = "
> SELECT *
> FROM tbEmerStore AS es
> INNER JOIN
> tbIpRange AS ir
> ON es.Store = ir.StoreNum
> WHERE ir.IpRange = " & LEFT(varIP, 12) & "
> ORDER BY es.Store
> "
> conn.Execute(varSQL)
> This is probably not exact given the line breaks, but you should be able
to
> get the point.
> To make life easier, I would strongly recommend that you use fixed-length,
> zero-padded IP addresses and subnets; otherwise, you are going to have to
do
> some INSTR pattern matching to find the last "." octet seperator to
determine
> if the characters you need to truncate is 1, 2, or 3--that would suck.
> Sincerely,
>
> Anthony Thomas
> "Daniel_Cha" wrote:
> > ok, here is my sql statement...
> > SELECT *
> > FROM tbEmerStore, tbIpRange
> > WHERE tbEmerStore.Store = tbIpRange.StoreNum AND tbIpRange.IpRange LIKE
> > 'varIP'
> > ORDER BY Store
> >
> > Variable is Name= varIP Run-Time Value => > Request.ServerVariable("REMOTE_ADDR")
> >
> > what am i doing wrong?
> >
> >
> >
> >
> >
> > "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> > news:%23fMvpGByEHA.2540@.TK2MSFTNGP09.phx.gbl...
> > > Daniel_Cha wrote:
> > > > Here is what I am trying to do... I have a phone list of all our
> > > > stores. It contains managers home and cell numbers for emergencies.
> > > > So the list is about 44 stores and about 7 to 8 managers to each
> > > > store...so that's about 350 or so numbers on this list... Now our
> > > > store each have there own unique IP address, ex. Store 2 would be
> > > > 192.168.2.' So within the phone list I have, for each store I have
> > > > stored the Ip like 192.168.2 or 192.168.6 and so on.. Well what I
> > > > want to do is try to build a query that will only return records
that
> > > > are similar to the users IP... so I have a hidden request for the
> > > > IP... Request.ServerVariables("REMOTE_ADDR") which I want to use as
a
> > > > variable in my query to compare their ip with what's in the table..
> > > >
> > > > So the results would be like this...
> > > >
> > > > My IP 192.168.6.15
> > > > I should see ONLY records with 192.168.6 as part of the IP.
> > > >
> > > > Any suggestions? Ideas? not sure if this would be the place to post
> > > > this, but would appreciate any help.
> > >
> > > It sounds like your IP address is stored in character format, and
that's
> > > good. To select only those stores that match your specs, use:
> > >
> > > Select store_information
> > > From Stores
> > > Where IP LIKE '192.168.6.%'
> > >
> > > Or if you are using a parameter:
> > >
> > > @.IP as varchar(12)
> > >
> > > Set @.IP = @.IP + '%'
> > > Select store_information
> > > From Stores
> > > Where IP LIKE @.IP
> > >
> > >
> > > --
> > > David Gugick
> > > Imceda Software
> > > www.imceda.com
> > >
> >
> >
> >|||I don't mean to give you too much trouble but what are you, dense?
Three of us have given you examples now. Your problem is two-fold.
1. You can't quote your variable: 'varIP'
2. Your source is longer than your search. You'll have to truncate part of
your source.
Look at the examples again.
Sincerely,
Anthony Thomas
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:%23zj3AaCyEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Ok, I what is stored in the database is the following:
> Table = tbIpRange
> Column Name Value
> StoreNum 02 San Antonio
> IPRange 192.168.2
> Table = tbEmerStore
> Column Name Value
> Store 02 San Antonio
>
> So I have one table that contains this information for each store, since
> this will not change unless we add a new location to the company.
> In the table that contains all the information there is a column that
> contains the same StoreNum value and its called Store
> So my select statement looks like this..
> SELECT Store, StoreNum
> FROM tbEmerStore, tbIpRange
> WHERE tbEmerStore.Store = tbIpRange.StoreNum AND 'varIP' LIKE
> tbIpRange.IpRange
> my variable varIP has a run-time value of
> Request.ServerVariable("REMOTE_ADDR") so that I can capture the users IP
> and compare it to the table.
> Im not all that great with queries and variables... so im not sure what im
> doing wrong...and our SQL guru is out on vacation since he just had a
little
> boy this week.. so I dont have anyone to help me with this. So I want to
> compare the REMOTE_ADDR to the IPRange column.. and if they are alike
then
> ONLY return the stores that have that IP Range.
>
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:89C657FA-CA4F-4C8C-BF08-3C9447F6C1B5@.microsoft.com...
> > I'm assuming that the varIP is storing the full octets, what does the
> > tbIpRane.IpRange field data look like?
> >
> > I'm also assuming you are sending ad hoc T-SQL to the server instead of
> > using a stored procedure. I'll also assume that you are only storing
the
> > truncated octet in the database. If this is so, modify your query,
> thusly:
> >
> > varSQL = "
> > SELECT *
> >
> > FROM tbEmerStore AS es
> > INNER JOIN
> > tbIpRange AS ir
> > ON es.Store = ir.StoreNum
> >
> > WHERE ir.IpRange = " & LEFT(varIP, 12) & "
> >
> > ORDER BY es.Store
> > "
> >
> > conn.Execute(varSQL)
> >
> > This is probably not exact given the line breaks, but you should be able
> to
> > get the point.
> >
> > To make life easier, I would strongly recommend that you use
fixed-length,
> > zero-padded IP addresses and subnets; otherwise, you are going to have
to
> do
> > some INSTR pattern matching to find the last "." octet seperator to
> determine
> > if the characters you need to truncate is 1, 2, or 3--that would suck.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> > "Daniel_Cha" wrote:
> >
> > > ok, here is my sql statement...
> > > SELECT *
> > > FROM tbEmerStore, tbIpRange
> > > WHERE tbEmerStore.Store = tbIpRange.StoreNum AND tbIpRange.IpRange
LIKE
> > > 'varIP'
> > > ORDER BY Store
> > >
> > > Variable is Name= varIP Run-Time Value => > > Request.ServerVariable("REMOTE_ADDR")
> > >
> > > what am i doing wrong?
> > >
> > >
> > >
> > >
> > >
> > > "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> > > news:%23fMvpGByEHA.2540@.TK2MSFTNGP09.phx.gbl...
> > > > Daniel_Cha wrote:
> > > > > Here is what I am trying to do... I have a phone list of all our
> > > > > stores. It contains managers home and cell numbers for
emergencies.
> > > > > So the list is about 44 stores and about 7 to 8 managers to each
> > > > > store...so that's about 350 or so numbers on this list... Now our
> > > > > store each have there own unique IP address, ex. Store 2 would be
> > > > > 192.168.2.' So within the phone list I have, for each store I
have
> > > > > stored the Ip like 192.168.2 or 192.168.6 and so on.. Well what I
> > > > > want to do is try to build a query that will only return records
> that
> > > > > are similar to the users IP... so I have a hidden request for the
> > > > > IP... Request.ServerVariables("REMOTE_ADDR") which I want to use
as
> a
> > > > > variable in my query to compare their ip with what's in the
table..
> > > > >
> > > > > So the results would be like this...
> > > > >
> > > > > My IP 192.168.6.15
> > > > > I should see ONLY records with 192.168.6 as part of the IP.
> > > > >
> > > > > Any suggestions? Ideas? not sure if this would be the place to
post
> > > > > this, but would appreciate any help.
> > > >
> > > > It sounds like your IP address is stored in character format, and
> that's
> > > > good. To select only those stores that match your specs, use:
> > > >
> > > > Select store_information
> > > > From Stores
> > > > Where IP LIKE '192.168.6.%'
> > > >
> > > > Or if you are using a parameter:
> > > >
> > > > @.IP as varchar(12)
> > > >
> > > > Set @.IP = @.IP + '%'
> > > > Select store_information
> > > > From Stores
> > > > Where IP LIKE @.IP
> > > >
> > > >
> > > > --
> > > > David Gugick
> > > > Imceda Software
> > > > www.imceda.com
> > > >
> > >
> > >
> > >
>

query help

I have a name column that contains both first and last
names:
Col1
--
John Doe
I'd like to split it into two columns, a first and
lastname:
firstname lastname
-- --
John Doe
Anyone have any easy way to do this?Do you *always* have two words, separated by a space? I.e., what does your data look like?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:c07e01c47a31$4e2ec290$a601280a@.phx.gbl...
> I have a name column that contains both first and last
> names:
> Col1
> --
> John Doe
> I'd like to split it into two columns, a first and
> lastname:
> firstname lastname
> -- --
> John Doe
> Anyone have any easy way to do this?|||For the most part. There are some names that contain a
middle inital..
the data looks like this:
John Doe
Jane Doe
George W Bush
Bill Clinton
John Kerry
Jim Bob Smith
etc....
I'm not overly concerned with getting everything perfect.
To be honest, I'd be cool with just the first names.
>--Original Message--
>Do you *always* have two words, separated by a space?
I.e., what does your data look like?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:c07e01c47a31$4e2ec290$a601280a@.phx.gbl...
>> I have a name column that contains both first and last
>> names:
>> Col1
>> --
>> John Doe
>> I'd like to split it into two columns, a first and
>> lastname:
>> firstname lastname
>> -- --
>> John Doe
>> Anyone have any easy way to do this?
>
>.
>|||This should get you started:
CREATE TABLE Presidents (
FullName varchar(50)
)
GO
INSERT INTO Frog VALUES ('George W Bush')
INSERT INTO Frog VALUES ('Bill Clinton')
INSERT INTO Frog VALUES ('Ronald Reagan')
INSERT INTO Frog VALUES ('George H Bush')
INSERT INTO Frog VALUES ('Gerald Ford')
INSERT INTO Frog VALUES ('Richard Nixon')
GO
SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
CASE
WHEN PATINDEX('% _ %', FullName) > 0
THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1, 1)
ELSE ''
END AS 'MI',
RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last Name'
FROM Presidents
You can look up the various pieces used.
CHARINDEX
PATINDEX
SUBSTRING
REVERSE
CASE
Rick Sawtell
MCT, MCSD, MCDBA|||Ummm. Change the INSERT INTO commands to reflect the Presidents table...
Sorry bout that.
Rick
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> This should get you started:
> CREATE TABLE Presidents (
> FullName varchar(50)
> )
> GO
> INSERT INTO Frog VALUES ('George W Bush')
> INSERT INTO Frog VALUES ('Bill Clinton')
> INSERT INTO Frog VALUES ('Ronald Reagan')
> INSERT INTO Frog VALUES ('George H Bush')
> INSERT INTO Frog VALUES ('Gerald Ford')
> INSERT INTO Frog VALUES ('Richard Nixon')
> GO
>
> SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
> CASE
> WHEN PATINDEX('% _ %', FullName) > 0
> THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1,
1)
> ELSE ''
> END AS 'MI',
> RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last
Name'
> FROM Presidents
>
> You can look up the various pieces used.
> CHARINDEX
> PATINDEX
> SUBSTRING
> REVERSE
> CASE
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Cool, that did it. One other thing though... Could the
same be used for an address column? I used the same
syntax, but ran into an issue...
The column has a street address:
123 N. Main St.
I used the SQL and pulled the house number, directional,
and suffix, but lost the street name. Any help?
Thanks!
>--Original Message--
>Ummm. Change the INSERT INTO commands to reflect the
Presidents table...
>Sorry bout that.
>
>Rick
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
>> This should get you started:
>> CREATE TABLE Presidents (
>> FullName varchar(50)
>> )
>> GO
>> INSERT INTO Frog VALUES ('George W Bush')
>> INSERT INTO Frog VALUES ('Bill Clinton')
>> INSERT INTO Frog VALUES ('Ronald Reagan')
>> INSERT INTO Frog VALUES ('George H Bush')
>> INSERT INTO Frog VALUES ('Gerald Ford')
>> INSERT INTO Frog VALUES ('Richard Nixon')
>> GO
>>
>> SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1)
AS 'First Name',
>> CASE
>> WHEN PATINDEX('% _ %', FullName) > 0
>> THEN SUBSTRING(FullName, CHARINDEX
(' ', FullName) +1,
>1)
>> ELSE ''
>> END AS 'MI',
>> RIGHT(FullName, CHARINDEX(' ', REVERSE
(FullName)) - 1) AS 'Last
>Name'
>> FROM Presidents
>>
>> You can look up the various pieces used.
>> CHARINDEX
>> PATINDEX
>> SUBSTRING
>> REVERSE
>> CASE
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>
>.
>|||Ummm..
Use the SUBSTRING function to get everything to the right of your
directional. Then apply the same CHARINDEX or PATINDEX functions to the
return value you are looking for from the return value of the SUBSTRING
function.
On a separate note... SQL really isn't the best choice to be doing
procedural language things like this.
If you dumped everything to a text file and used VBScript, you could
probably get this thing hashed out more quickly.
Rick
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:c45a01c47a48$9c9aef50$a301280a@.phx.gbl...
> Cool, that did it. One other thing though... Could the
> same be used for an address column? I used the same
> syntax, but ran into an issue...
> The column has a street address:
> 123 N. Main St.
> I used the SQL and pulled the house number, directional,
> and suffix, but lost the street name. Any help?
> Thanks!
> >--Original Message--
> >Ummm. Change the INSERT INTO commands to reflect the
> Presidents table...
> >
> >Sorry bout that.
> >
> >
> >Rick
> >
> >
> >
> >"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> >news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> >> This should get you started:
> >>
> >> CREATE TABLE Presidents (
> >> FullName varchar(50)
> >> )
> >>
> >> GO
> >>
> >> INSERT INTO Frog VALUES ('George W Bush')
> >> INSERT INTO Frog VALUES ('Bill Clinton')
> >> INSERT INTO Frog VALUES ('Ronald Reagan')
> >> INSERT INTO Frog VALUES ('George H Bush')
> >> INSERT INTO Frog VALUES ('Gerald Ford')
> >> INSERT INTO Frog VALUES ('Richard Nixon')
> >>
> >> GO
> >>
> >>
> >> SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1)
> AS 'First Name',
> >> CASE
> >> WHEN PATINDEX('% _ %', FullName) > 0
> >> THEN SUBSTRING(FullName, CHARINDEX
> (' ', FullName) +1,
> >1)
> >> ELSE ''
> >> END AS 'MI',
> >> RIGHT(FullName, CHARINDEX(' ', REVERSE
> (FullName)) - 1) AS 'Last
> >Name'
> >> FROM Presidents
> >>
> >>
> >>
> >> You can look up the various pieces used.
> >> CHARINDEX
> >> PATINDEX
> >> SUBSTRING
> >> REVERSE
> >> CASE
> >>
> >> Rick Sawtell
> >> MCT, MCSD, MCDBA
> >>
> >>
> >
> >
> >.
> >

Friday, March 23, 2012

Query from 2 XML data sources

Hi,
Suppose I have to XML documents. The first one contains a list of
authors and the second one contains a list of books. For example,
author.xml
--
<authors>
<author @.id="1">
<name>Tom</name>
<tel>001-909-1129</tel>
</author>
<author @.id="2">
<name>Chris</name>
<tel>001-871-1341</tel>
</author>
<author @.id="3">
<name>Jack</name>
<tel>001-452-8721</tel>
</author>
</authors>
book.xml
--
<catalog>
<book>
<title>SQL Server for Dummies</title>
<author @.id="1">Tom</author>
</book>
<book>
<title>XQuery for Dummies</title>
<author @.id="2">Chris</author>
</book>
<book>
<title>It's All About XML</title>
<author @.id="3">Jack</author>
</book>
</catalog>
Suppose I store "author.xml" as xml data type in the AUTHOR table and
"book.xml" as xml data type in the BOOK table. I would like to get the
information of authors who have written a book with title containing
"XQuery".
How can I write XQuery in SQL Server 2005 ?
ThanksFirst - see also the item (in this forum) titled "Cross Joining two
XML columns, or two (or more) XML variables" (it also got renamed a
few times in the duration).
--
Well, firstly I wouldn't store them as entire documents. This data is
essentially in tabular form, so I would have an AUTHORS table (id,
name, tel) etc.
If I had to use xml (to store semi-structured data), then I would
probably start with AUTHORS (xml) (one row per author - which you can
do by shredding the original xml quite easily [shown]) -then add some
computed, stored, indexed columns. This means that in the database you
have access to a readonly column that is the "@.id" - this allows you
to join very efficiently between the tables, by using the pre-computed
column on the destination, not an xml query - i.e.
DROP TABLE AUTHORS
DROP FUNCTION GetAuthorId
GO
CREATE TABLE AUTHORS ([Content] xml NOT NULL)
GO
CREATE FUNCTION [dbo].GetAuthorId(@.Content xml) RETURNS int
WITH SCHEMABINDING
BEGIN
DECLARE @.val varchar(50)
SET @.val = @.Content.value('(/author/@.id)[1]','varchar(50)')
RETURN CASE ISNUMERIC(@.val) WHEN 1 THEN @.val ELSE NULL END
END
GO
ALTER TABLE AUTHORS ADD [Id]
AS [dbo].GetAuthorId([Content]) PERSISTED
GO
CREATE NONCLUSTERED INDEX IDX_AuthorId
ON AUTHORS([Id])
GO
DECLARE @.xml xml
SET @.xml = '
<authors>
<author id="1">
<name>Tom</name>
<tel>001-909-1129</tel>
</author>
<author id="2">
<name>Chris</name>
<tel>001-871-1341</tel>
</author>
<author id="3">
<name>Jack</name>
<tel>001-452-8721</tel>
</author>
</authors>'
INSERT AUTHORS ([Content])
SELECT tmp.x.query('.')
FROM @.xml.nodes('/authors/author') tmp(x)
SELECT * FROM AUTHORS
--
Now you can join to the stored and indexed AUTHORS.Id column.
Marc|||I think the gist of the discussion is that you basically have to shred the 2
documents to relational format and join using SQL. If, however, all of the
information were stored in a single document you could simulate an inner
join using a FLWOR expression.
1. If you put two "$var in path_expression" expressions in the for clause
separated by a comma, XQuery generates the cross join of the two path
expressions.
2. If you add a where clause to limit the results to those where your id
attribute is equal in both path expressions you have an inner join.
3. If you "and" another predicate to the where clause limiting the results
to those that contain the word "XQuery", you get author #2, "Chris".
In the example below I've combined your documents, removed the invalid "@."
character from the attribute names, and entitised the apostrophe in your
data. The FLWOR expression that follows generates the results. Of course all
this is dependent on the two documents being combined into a single doc.
DECLARE @.xml XML;
SELECT @.xml = '<authors>
<author id="1">
<name>Tom</name>
<tel>001-909-1129</tel>
</author>
<author id="2">
<name>Chris</name>
<tel>001-871-1341</tel>
</author>
<author id="3">
<name>Jack</name>
<tel>001-452-8721</tel>
</author>
</authors>
<catalog>
<book>
<title>SQL Server for Dummies</title>
<author id="1">Tom</author>
</book>
<book>
<title>XQuery for Dummies</title>
<author id="2">Chris</author>
</book>
<book>
<title>It's All About XML</title>
<author id="3">Jack</author>
</book>
</catalog>';
SELECT @.xml.query('for $a in /authors/author, $b in /catalog/book
where $a/@.id = $b/author/@.id
and $b/title[contains(., "XQuery")]
return $a');
<Paolo.sembung@.gmail.com> wrote in message
news:060ec030-adba-4ed3-bb99-c24e75fdee34@.y5g2000hsf.googlegroups.com...
> Hi,
> Suppose I have to XML documents. The first one contains a list of
> authors and the second one contains a list of books. For example,
> author.xml
> --
> <authors>
> <author @.id="1">
> <name>Tom</name>
> <tel>001-909-1129</tel>
> </author>
> <author @.id="2">
> <name>Chris</name>
> <tel>001-871-1341</tel>
> </author>
> <author @.id="3">
> <name>Jack</name>
> <tel>001-452-8721</tel>
> </author>
> </authors>
> book.xml
> --
> <catalog>
> <book>
> <title>SQL Server for Dummies</title>
> <author @.id="1">Tom</author>
> </book>
> <book>
> <title>XQuery for Dummies</title>
> <author @.id="2">Chris</author>
> </book>
> <book>
> <title>It's All About XML</title>
> <author @.id="3">Jack</author>
> </book>
> </catalog>
> Suppose I store "author.xml" as xml data type in the AUTHOR table and
> "book.xml" as xml data type in the BOOK table. I would like to get the
> information of authors who have written a book with title containing
> "XQuery".
> How can I write XQuery in SQL Server 2005 ?
> Thanks

Query from 2 XML data sources

Hi,
Suppose I have to XML documents. The first one contains a list of
authors and the second one contains a list of books. For example,
author.xml
<authors>
<author @.id="1">
<name>Tom</name>
<tel>001-909-1129</tel>
</author>
<author @.id="2">
<name>Chris</name>
<tel>001-871-1341</tel>
</author>
<author @.id="3">
<name>Jack</name>
<tel>001-452-8721</tel>
</author>
</authors>
book.xml
<catalog>
<book>
<title>SQL Server for Dummies</title>
<author @.id="1">Tom</author>
</book>
<book>
<title>XQuery for Dummies</title>
<author @.id="2">Chris</author>
</book>
<book>
<title>It's All About XML</title>
<author @.id="3">Jack</author>
</book>
</catalog>
Suppose I store "author.xml" as xml data type in the AUTHOR table and
"book.xml" as xml data type in the BOOK table. I would like to get the
information of authors who have written a book with title containing
"XQuery".
How can I write XQuery in SQL Server 2005 ?
Thanks
First - see also the item (in this forum) titled "Cross Joining two
XML columns, or two (or more) XML variables" (it also got renamed a
few times in the duration).
Well, firstly I wouldn't store them as entire documents. This data is
essentially in tabular form, so I would have an AUTHORS table (id,
name, tel) etc.
If I had to use xml (to store semi-structured data), then I would
probably start with AUTHORS (xml) (one row per author - which you can
do by shredding the original xml quite easily [shown]) -then add some
computed, stored, indexed columns. This means that in the database you
have access to a readonly column that is the "@.id" - this allows you
to join very efficiently between the tables, by using the pre-computed
column on the destination, not an xml query - i.e.
DROP TABLE AUTHORS
DROP FUNCTION GetAuthorId
GO
CREATE TABLE AUTHORS ([Content] xml NOT NULL)
GO
CREATE FUNCTION [dbo].GetAuthorId(@.Content xml) RETURNS int
WITH SCHEMABINDING
BEGIN
DECLARE @.val varchar(50)
SET @.val = @.Content.value('(/author/@.id)[1]','varchar(50)')
RETURN CASE ISNUMERIC(@.val) WHEN 1 THEN @.val ELSE NULL END
END
GO
ALTER TABLE AUTHORS ADD [Id]
AS [dbo].GetAuthorId([Content]) PERSISTED
GO
CREATE NONCLUSTERED INDEX IDX_AuthorId
ON AUTHORS([Id])
GO
DECLARE @.xml xml
SET @.xml = '
<authors>
<author id="1">
<name>Tom</name>
<tel>001-909-1129</tel>
</author>
<author id="2">
<name>Chris</name>
<tel>001-871-1341</tel>
</author>
<author id="3">
<name>Jack</name>
<tel>001-452-8721</tel>
</author>
</authors>'
INSERT AUTHORS ([Content])
SELECT tmp.x.query('.')
FROM @.xml.nodes('/authors/author') tmp(x)
SELECT * FROM AUTHORS
Now you can join to the stored and indexed AUTHORS.Id column.
Marc
|||I think the gist of the discussion is that you basically have to shred the 2
documents to relational format and join using SQL. If, however, all of the
information were stored in a single document you could simulate an inner
join using a FLWOR expression.
1. If you put two "$var in path_expression" expressions in the for clause
separated by a comma, XQuery generates the cross join of the two path
expressions.
2. If you add a where clause to limit the results to those where your id
attribute is equal in both path expressions you have an inner join.
3. If you "and" another predicate to the where clause limiting the results
to those that contain the word "XQuery", you get author #2, "Chris".
In the example below I've combined your documents, removed the invalid "@."
character from the attribute names, and entitised the apostrophe in your
data. The FLWOR expression that follows generates the results. Of course all
this is dependent on the two documents being combined into a single doc.
DECLARE @.xml XML;
SELECT @.xml = '<authors>
<author id="1">
<name>Tom</name>
<tel>001-909-1129</tel>
</author>
<author id="2">
<name>Chris</name>
<tel>001-871-1341</tel>
</author>
<author id="3">
<name>Jack</name>
<tel>001-452-8721</tel>
</author>
</authors>
<catalog>
<book>
<title>SQL Server for Dummies</title>
<author id="1">Tom</author>
</book>
<book>
<title>XQuery for Dummies</title>
<author id="2">Chris</author>
</book>
<book>
<title>It's All About XML</title>
<author id="3">Jack</author>
</book>
</catalog>';
SELECT @.xml.query('for $a in /authors/author, $b in /catalog/book
where $a/@.id = $b/author/@.id
and $b/title[contains(., "XQuery")]
return $a');
<Paolo.sembung@.gmail.com> wrote in message
news:060ec030-adba-4ed3-bb99-c24e75fdee34@.y5g2000hsf.googlegroups.com...
> Hi,
> Suppose I have to XML documents. The first one contains a list of
> authors and the second one contains a list of books. For example,
> author.xml
> --
> <authors>
> <author @.id="1">
> <name>Tom</name>
> <tel>001-909-1129</tel>
> </author>
> <author @.id="2">
> <name>Chris</name>
> <tel>001-871-1341</tel>
> </author>
> <author @.id="3">
> <name>Jack</name>
> <tel>001-452-8721</tel>
> </author>
> </authors>
> book.xml
> --
> <catalog>
> <book>
> <title>SQL Server for Dummies</title>
> <author @.id="1">Tom</author>
> </book>
> <book>
> <title>XQuery for Dummies</title>
> <author @.id="2">Chris</author>
> </book>
> <book>
> <title>It's All About XML</title>
> <author @.id="3">Jack</author>
> </book>
> </catalog>
> Suppose I store "author.xml" as xml data type in the AUTHOR table and
> "book.xml" as xml data type in the BOOK table. I would like to get the
> information of authors who have written a book with title containing
> "XQuery".
> How can I write XQuery in SQL Server 2005 ?
> Thanks

Query for Segments

All
I have a table that contains data for when a stretch of road was last
resurfaced and I am trying to return the cost per year for each completed
road segment. In the example below the road was resurfaced in 1971 and 1973
.
In 1971 the road was resurfaced from 1 kilometre to 4 kilometres and 5
kilometres to 9 kilometres. In 1973 the kilometre in between was resurfaced
.
I only want to show from start to finish the complete parts of the road that
were resurfaced and when as illustrated below:
CREATE TABLE rd_resurface
(
RoadNo NVARCHAR(20),
KMStart INT,
KMEnd INT,
Cost NUMERIC(13, 2),
Deprec NUMERIC(13, 2),
[Year] SMALLINT
)
GO
INSERT rd_resurface SELECT 'H001', 1, 2, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 2, 3, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 3, 4, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 4, 5, 100.00, 10.00, 1973
INSERT rd_resurface SELECT 'H001', 5, 6, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 6, 7, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 7, 8, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 8, 9, 100.00, 10.00, 1971
I am trying to return the results below:
RoadNo KMStart KMEnd Cost Deprec Year
H001 1 4 $300.00 $30.00 1971
H001 4 5 $100.00 $10.00 1973
H001 5 9 $400.00 $40.00 1971
Thanks for any assistance that can be provided.David,
I'm not sure I got it right as you didn't say if the same km of road can
appear more than once in the same year. Assuming it can't...
The following query calculates a grouping factor which is the last kmstart
value within the segment:
select *,
(select min(kmstart)
from rd_resurface as r2
where r2.roadno = r1.roadno
and r2.year = r1.year
and r2.kmstart >= r1.kmstart
and not exists
(select *
from rd_resurface as r3
where r3.roadno = r2.roadno
and r3.year = r2.year
and r3.kmstart = r2.kmend)) as grp
from rd_resurface as r1
RoadNo KMStart KMEnd Cost Deprec Year grp
-- -- -- -- -- -- --
H001 1 2 100.00 10.00 1971 3
H001 2 3 100.00 10.00 1971 3
H001 3 4 100.00 10.00 1971 3
H001 4 5 100.00 10.00 1973 4
H001 5 6 100.00 10.00 1971 8
H001 6 7 100.00 10.00 1971 8
H001 7 8 100.00 10.00 1971 8
H001 8 9 100.00 10.00 1971 8
The rest is simply to group the data and return the desired aggregates:
select roadno, year, min(kmstart) as kmstart, max(kmend) as kmend,
sum(cost) as cost, sum(deprec) as deprec
from (select *,
(select min(kmstart)
from rd_resurface as r2
where r2.roadno = r1.roadno
and r2.year = r1.year
and r2.kmstart >= r1.kmstart
and not exists
(select *
from rd_resurface as r3
where r3.roadno = r2.roadno
and r3.year = r2.year
and r3.kmstart = r2.kmend)) as grp
from rd_resurface as r1) as d
group by roadno, year, grp
roadno year kmstart kmend cost deprec
-- -- -- -- -- --
H001 1971 1 4 300.00 30.00
H001 1971 5 9 400.00 40.00
H001 1973 4 5 100.00 10.00
BG, SQL Server MVP
www.SolidQualityLearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:3A73BA53-4633-4E5C-B72C-EBB3D4B73472@.microsoft.com...
> All
> I have a table that contains data for when a stretch of road was last
> resurfaced and I am trying to return the cost per year for each completed
> road segment. In the example below the road was resurfaced in 1971 and
> 1973.
> In 1971 the road was resurfaced from 1 kilometre to 4 kilometres and 5
> kilometres to 9 kilometres. In 1973 the kilometre in between was
> resurfaced.
> I only want to show from start to finish the complete parts of the road
> that
> were resurfaced and when as illustrated below:
> CREATE TABLE rd_resurface
> (
> RoadNo NVARCHAR(20),
> KMStart INT,
> KMEnd INT,
> Cost NUMERIC(13, 2),
> Deprec NUMERIC(13, 2),
> [Year] SMALLINT
> )
> GO
> INSERT rd_resurface SELECT 'H001', 1, 2, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 2, 3, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 3, 4, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 4, 5, 100.00, 10.00, 1973
> INSERT rd_resurface SELECT 'H001', 5, 6, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 6, 7, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 7, 8, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 8, 9, 100.00, 10.00, 1971
> I am trying to return the results below:
> RoadNo KMStart KMEnd Cost Deprec Year
> H001 1 4 $300.00 $30.00 1971
> H001 4 5 $100.00 $10.00 1973
> H001 5 9 $400.00 $40.00 1971
>
> Thanks for any assistance that can be provided.sql

Monday, March 12, 2012

Query Empty node

Hello,

I have an XML file which contains different shops, where a certain node (<openinhours>) is empty in some occasions.

The node displays as <openinghours /> in my xml file in that case.

Now when I try to get the openinghour value of all the shops, it shows an error that I cannot atomize.

Is there any way to check if the node contains text by using the value or exist?

Thanks in advance!

I will try to clarify myself.

In my db, I have every shop in a different row, with columns: ShopID, ShopName, ShopXML. ShopXML contains the rest of the info of te shop, and is xml-field.

In that xml, i have something like this:

However, in some cases this just is:

I want to get all the openinghours where available from my xml (and I use .value),

but using

ShopXML.value('(/shopinfo/openinghours)[1]','varchar(max)') doesn't work because of the empty nodes.

How can I solve this?

Thank you!

|||

Thanks for providing more information. I'll have to ask some more questions before I can help you.

In the previous post you said that you got an error saying that you cannot atomize. Which is the exact error message that you get? I would assume that it is a static error, which means that the ShopXML is typed with an XML schema collection. Is this true? If it is, what's the type of openinghours element?

Thanks,

Adrian

|||

Yes I have a schema collection. The type is string.

When I disable the schemas it works fine. But if I choose not to use the schema's, will it affect the speed of querying data in my XML field?

|||

It's better to use the schema collection if you can.

I still don't understand what exactly doesn't work when you have a schema collection. Do you get an error? If yes, what error do you get and when? Or is it that you don't want to have empty openinghours in your result?

Regards,

Adrian

Friday, March 9, 2012

Query decimal points value in T-sql

I have one field with data type float contains data e.g 245.5,65,36.2..etc.
I
need to query out all the value with decimal points only, means those value
=
245.5,36.2..
How can I use transact sql to check?
ThanksOne way might be:
SELECT Col
FROM YourTable
WHERE (Col - CONVERT(INT, Col)) > 0
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"WS" <WS@.discussions.microsoft.com> wrote in message
news:49651063-1618-4DBD-975A-81DCD607E35F@.microsoft.com...
> I have one field with data type float contains data e.g
245.5,65,36.2..etc. I
> need to query out all the value with decimal points only, means those
value =
> 245.5,36.2..
> How can I use transact sql to check?
> Thanks

Query Data using Date

I using Windows XP SP2. Install SQL Server 2000 Developer Edition.
I want to query a table which contains a column of data type datetime using
ASP form, where the input of type text. Before querying I cahnge input text
into date format using CDate() function.
When I run the query I got this message:
"Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data
type to a datetime data type resulted in an out-of-range datetime value.
"
I have another Machine Installed with Windows 2000 Pro, SQL Server 2000
Developer Ed. When I ran the same query It works.
Wish to know what is the problem?Hi
Use YYYYMMDD format to deal with dates.
"wira659" <wira659@.discussions.microsoft.com> wrote in message
news:9E5FE92D-BA5B-45F4-B445-FF2DA189EC72@.microsoft.com...
> I using Windows XP SP2. Install SQL Server 2000 Developer Edition.
> I want to query a table which contains a column of data type datetime
using
> ASP form, where the input of type text. Before querying I cahnge input
text
> into date format using CDate() function.
> When I run the query I got this message:
> "Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char
data
> type to a datetime data type resulted in an out-of-range datetime value.
> "
> I have another Machine Installed with Windows 2000 Pro, SQL Server 2000
> Developer Ed. When I ran the same query It works.
> Wish to know what is the problem?

Wednesday, March 7, 2012

query contains concatenated inner results

I have a table item(id, item_name, item_added_date, item_price).
The second table is item_viewedby(id, item_id, userid).
The second table holds the users who are viewing a certain item.
So data in first table item is like:
id item_name item_added_date item_price
1 toothbrush 01/01/2003 100
2 toothpaste 02/02/2003 125
And data in second table item_viewedby is like:
id item_id userid
1 1 jane
2 1 john
3 1 kim
4 2 jim
5 2 tim
I want to execute a query so that for each item I have a single row of
item_id item_price item_viewed_by
Where item_viewed_by is a semi_colon separated list of users who are
viewing a certain item.
So my result table is like
item_id item_price item_viewed_by
1 100 jane;john;kim
2 125 jim;tim
I am wanting to put this query in a stored procedure and call it from
my ASP.NET code.
If this is to be done using cursors then please help me with that, too
Thanks,
FredSee the following example which will give you a fair idea how can you
convert rows to columns.
Ex:
create table tab(ID int,
cats varchar(50))
go
insert into tab values(1 ,'jane')
insert into tab values(1 ,'john')
insert into tab values(1 ,'kim')
insert into tab values(2 ,'nick')
insert into tab values(3 ,'jim')
insert into tab values(3 ,'tim')
go
drop table #tmp
create table #tmp(id int, tmpval varchar(50))
go
declare @.id int, @.old_id int
declare @.seq_num int
declare @.cats varchar(50), @.f_cats varchar(50)
select @.id=0, @.old_id=0,@.seq_num=0, @.cats='', @.f_cats=''
declare c1 cursor for
select id, cats from tab order by id
open c1
fetch c1 into @.id,@.cats
while @.@.fetch_status = 0
begin
If @.old_id <> @.id and @.old_id <> 0
begin
insert into #tmp values(@.old_id, @.f_cats)
select @.seq_num=0, @.f_cats=''
end
select @.f_cats = @.f_cats + case @.f_cats when '' then '' else ',' end + @.cats
select @.old_id = @.id
fetch c1 into @.id,@.cats
end
close c1
deallocate c1
insert into #tmp values(@.old_id, @.f_cats)
select * from #tmp
--
-Vishal
"Fred" <fredg1232003@.yahoo.com> wrote in message
news:2022fcea.0307281714.3529f2f@.posting.google.com...
> I have a table item(id, item_name, item_added_date, item_price).
> The second table is item_viewedby(id, item_id, userid).
> The second table holds the users who are viewing a certain item.
> So data in first table item is like:
> id item_name item_added_date item_price
> 1 toothbrush 01/01/2003 100
> 2 toothpaste 02/02/2003 125
> And data in second table item_viewedby is like:
> id item_id userid
> 1 1 jane
> 2 1 john
> 3 1 kim
> 4 2 jim
> 5 2 tim
> I want to execute a query so that for each item I have a single row of
> item_id item_price item_viewed_by
> Where item_viewed_by is a semi_colon separated list of users who are
> viewing a certain item.
> So my result table is like
> item_id item_price item_viewed_by
> 1 100 jane;john;kim
> 2 125 jim;tim
> I am wanting to put this query in a stored procedure and call it from
> my ASP.NET code.
> If this is to be done using cursors then please help me with that, too
> Thanks,
> Fred|||yes you can review it..........
if you get any issues just inform.
regards
Hari Sharma, India
Wangkhar@.yahoo.com (WangKhar) wrote in message news:<bb269444.0308040106.63f76743@.posting.google.com>...
> hkvats - I took the liberty of reviewing your code - hope you dont mind.|||This type of concatenation over rows is
very easy with the RAC utility for S2k.
No coding required.
Check out:
http://www.rac4sql.net/onlinehelp.asp?topic=236
RAC v2.2 and QALite released.
www.rac4sql.net

Saturday, February 25, 2012

Query batch completed with errors...

Hi,
I am executin a stored procedure which contains a sequence of Stored
procedures. One of these stored procedures is a cursor-stored procedure
(basically a cursor).
At the end of the execution of this Stp(cursor), it gives me "query batch
completed with errors". I tried to capture the error through Printing
@.@.error, but to no luck. The problem is that this stp is part of a sequence
of stored procedures. Because of this error the subsequent stps dont get
executed.
Could someone give some leads how to resolve this?
Thanx,
S.How are you executing the proc? Did you try running it in isolation in Query
Analyzer? That should at least show you the error message in the results
window. Debug the proc to find the statement that throws the error (In
Query Analyzer's Object Browser right-click the proc name and select Debug).
If you're still stuck once you've narrowed it down a bit then post some code
here to reproduce the error.
David Portas
SQL Server MVP
--|||David,
Yes I was executing it in the Analyser..that how i found out it was this stp
which was givin the prob...
Here is the code for the problem stp..
---
CREATE PROCEDURE CALC_MyDealValue_As_DealPartner
AS
DECLARE
@.pCustomerCivilId nvarchar(15),
@.pInvoiceNo nvarchar(50),
@.pLiability char(1),
@.pAppId char(3),
@.pGrossBalance decimal(15,3),
@.pShareHolderCivilID nvarchar(15),
@.pPartnerShare decimal(15,3)
--Declare the copy cursor
DECLARE DealPartner_Cursor CURSOR FOR
SELECT CustomerCivilId,InvoiceNumber,Liability,
AppId,ShareHolderCivilID,
Convert(Decimal(15,3),SharePercentage)/100 as share
FROM dbo.Shareholder
--where Left(CustomerCivilId,2) ='JA'
where (InvoiceNumber<>'') and (Liability <> '') and (AppId <>'') --For
Deal Partner This is not empty in Shareholder Table
--Open the cursor
BEGIN
OPEN DealPartner_Cursor
FETCH NEXT FROM DealPartner_Cursor
INTO
@.pCustomerCivilId,@.pInvoiceNo,@.pLiabilit
y,@.pAppId,@.pShareHolderCivilID,@.pPar
tnerShare
IF @.@.Error <>0
Begin
Print 'Fetch 1' + convert(nvarchar(50),@.@.Error)
End
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Print 'Parnter- ' + convert(nvarchar(50),@.pShareHolderCivilI
D)
--Print 'Share- ' + convert(nvarchar(50),@.pPartnerShare)
SET @.pGrossBalance = (SELECT isnull(GrossBalance,0) FROM vwMyDealAmounts
WHERE ( --dbo.vwMyDealAmounts.CivilID = @.pCustomerCivilId and
dbo.vwMyDealAmounts.InvoiceNumber = @.pInvoiceNo and
dbo.vwMyDealAmounts.Liability = @.pLiability and
dbo.vwMyDealAmounts.AppId = @.pAppId))
IF @.@.Error <>0
Begin
Print 'Fetch GB ' + convert(nvarchar(50),@.@.Error)
End
--Print 'GrossBal - ' + convert(nvarchar(50),@.pGrossBalance)
--Print 'DealCivilId - ' + convert(nvarchar(50),@.pDealCivilID)
Set @.pGrossBalance = isnull(@.pGrossBalance,0)
if (@.pGrossBalance <> 0.000)
Begin
if Left(@.pCustomerCivilId,2) ='JA'
Begin
Update TmpMyDealings
Set ValAsDealPartner= ValAsDealPartner +(@.pGrossBalance*@.pPartnerShare)
where TmpMyDealings.CivilId=@.pCustomerCivilId -- update TO JA
End
IF @.@.Error <>0
Begin
Print 'UP JA ' + convert(nvarchar(50),@.@.Error)
End
Update TmpMyDealings
Set ValAsDealPartner= ValAsDealPartner +(@.pGrossBalance*@.pPartnerShare)
where TmpMyDealings.CivilId=@.pShareHolderCivilID
IF @.@.Error <>0
Begin
Print 'UP NON-JA ' + convert(nvarchar(50),@.@.Error)
End
--Print 'Done for ' + convert(nvarchar(50),@.pCustomerCivilId) + ' - ' +
convert(nvarchar(50),@.pShareHolderCivilI
D)
--Print 'Done for Count ' + convert(nvarchar(50),@.iCount)
End
FETCH NEXT FROM DealPartner_Cursor
INTO
@.pCustomerCivilId,@.pInvoiceNo,@.pLiabilit
y,@.pAppId,@.pShareHolderCivilID,@.pPar
tnerShare
IF @.@.Error <>0
Begin
Print 'Fetch 2 ' + convert(nvarchar(50),@.@.Error)
End
END
CLOSE DealPartner_Cursor
DEALLOCATE DealPartner_Cursor
END
GO
---
"David Portas" wrote:

> How are you executing the proc? Did you try running it in isolation in Que
ry
> Analyzer? That should at least show you the error message in the results
> window. Debug the proc to find the statement that throws the error (In
> Query Analyzer's Object Browser right-click the proc name and select Debug
).
> If you're still stuck once you've narrowed it down a bit then post some co
de
> here to reproduce the error.
> --
> David Portas
> SQL Server MVP
> --
>
>|||You still didn't tell us the error message. Did you run it in Debug?
Honestly I wouldn't bother though. You should always try to avoid cursors
and 90% of this code is redundant. The effect seems to be the same as that
of a single UPDATE statement.
The following is my best guess of what you need to do. It's untested and as
it's done without a spec you'll probably have to make some mods. In
particular you may want to add WHERE EXISTS depending on requirements.
CREATE PROCEDURE calc_mydealvalue_as_dealpartner
AS
UPDATE TmpMyDealings
SET valasdealpartner = valasdealpartner +
(SELECT ISNULL(SUM(D.grossbalance*
CONVERT(DECIMAL(15,3),S.sharepercentage)/100),0)
FROM vwMyDealAmounts AS D
JOIN Shareholder AS S
ON D.invoicenumber = S.invoicenumber
AND D.liability = S.liability
AND D.appid = S.appid
AND D.grossbalance <>0
AND tmpmydealings.civilid = S.customercivilid
AND S.customercivilid LIKE 'JA%')
RETURN
GO
If you need more help please post DDL, a few rows of sample data (INSERT
statements are the best way to post sample data) and show us what result you
require from the sample. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--

Friday, March 30, 2012

query help

Query Help

Query Help

Wednesday, March 28, 2012

query help

Query help

query help

Friday, March 23, 2012

Query from 2 XML data sources

Query from 2 XML data sources

Query for Segments

Monday, March 12, 2012

Query Empty node

Friday, March 9, 2012

Query decimal points value in T-sql

Query Data using Date

Wednesday, March 7, 2012

query contains concatenated inner results

Saturday, February 25, 2012

Query batch completed with errors...

query assistance

Blog Archive

About Me