query assistance: column

Showing posts with label column. Show all posts

Friday, March 30, 2012

query help

I will try to be brief
I have two tables I am trying to join that share a foreign key.
the structures are:
table a: column table b: columns
foreign key employee ID employeeID
status earnings
effectivedate checkdate
each table can have multiple rows with the same employee ID. Table b can have multiple rows with the same checkdate. I am trying to query the two tables so I can get the sum of the earnings for a particular checkdate and the employees status at the time
of the check. Here is an example of the data and what I have written so far:
tableA:
employeeID STATUS Effectivedate
100 fulltime 01/01/03
100 parttime 01/01/04
100 fulltime 03/27/04
101 fulltime 01/01/03
101 parttime 04/01/04
tableB:
employeeID earnings checkdate
100 25.00 03/25/04
100 97.00 03/25/04
101 10.00 03/25/04
If I query with the employeeID it is no problem:
select tableB.employeeID, STATUS, Effectivedate, sum(earnings), checkdate
from tableA, tableB
where tableA.employeeID=tableB.employeeID
and effectivedate=(select max(effectivedate) from tableA where tableA.employeeID=100 and effectivdate <='03/25/04')
and checkdate='03/25/04'
group by tableB.employeeID, STATUS, Effectivedate, checkdate
but... to do this on my tables which have thousands and thousands of rows for each ID each month will be painful.
How can I write this query so that the select will return the sum of earnings by employeeID on a specific checkdate and the employee status on that date. I thought of using a cursor, but I am not advanced enough to write one. I am sure there has to be a
way to do this. Any help will be greatly appreciated!!!!
I realize I should have posted this in data mining, but I don't want to double post

sql

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 need to update 6 column on 1 table depending on 2 other
columns on another table (All 8 columns exists on both
tables)
here is what I am trying to write and it gives me error:
Update Table1
Set start_date = (Select start_date from Table2),
end_date = (Select end_date from Table2),
user1 = (Select user1 from Table2),
user2 = (Select user2 from Table2),
user3 = (Select user3 from Table2),
user4 = (Select user4 from Table2)
FROM Table2
Where Table2.project = Table1.project AND
Table2.Pjt_entity = Table1.Pjt_entity
Thanks for any help.
Hi,
Try this,
Update Table1
Set a.start_date = b.start_date ,
a.end_date = b.end_date,
a.user1 = b.user1,
a.user2 = b.user2,
a.user3 = b.user3,
a.user4 = b.user4
FROM Table1 a,Table2 b
Where a.project = b.project
AND a..Pjt_entity = b.Pjt_entity
Tahnks
Hari
MCDBA
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:2d2001c486d0$5a97bdb0$a301280a@.phx.gbl...
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.
|||Todd wrote:
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.
Well if you have a 1:1 between the tables, you just need to specify the
column to update:
Update Table1
Set start_date = b.start_date,
end_date = b.end_date,
etc...
FROM Table2 b
Where b.project = Table1.project AND
b.Pjt_entity = Table1.Pjt_entity
David G.
|||David G. wrote:
> Todd wrote:
> Well if you have a 1:1 between the tables, you just need to specify
> the column to update:
> Update Table1
> Set start_date = b.start_date,
> end_date = b.end_date,
> etc...
> FROM Table2 b
> Where b.project = Table1.project AND
> b.Pjt_entity = Table1.Pjt_entity
Left off a table in the FROM clause. See Hari's post instead.
David G.

Query Help

I have query with following column names:
cust_no, Firm_no, Name, homeaddress, homecity, homestate, homezip,
officeaddress, officecity, officestate, officezip
I want to be able to pull it this way that whenever there is a certain
unique Firm_no it should populate the homeaddress, homecity, homestate,
homezip for this customer.
What do I have to do to accomplish this?
ThanksU can achieve this by the following way:
Use the following expresson to display Homeaddress:
=IIF(IsNothing(Previous(Fields!Firm_no.Value)),
Fields!homeaddress.Value,
IIF(Fields!Firm_no.Value = Previous(Fields!Firm_no.Value),
"",
Fields!homeaddress.Value))
In the same way implement the same logic to display homecity, homestate,
homezip.
Regards,
SaraS|||saras,
I have a unique firm_no that I want to use when my report runs it should
look like:
Custo_no Firm_no Name Address City State Zip
123 1111 Jon Doe Main Dallas TX 75000
321 1122 Jon Doe Globe Pano TX 75001
So when anytime the Firm_no is '1122' I want the address to print their
homeaddress.
Thanks for your help.
"saras" wrote:
> U can achieve this by the following way:
> Use the following expresson to display Homeaddress:
> =IIF(IsNothing(Previous(Fields!Firm_no.Value)),
> Fields!homeaddress.Value,
> IIF(Fields!Firm_no.Value = Previous(Fields!Firm_no.Value),
> "",
> Fields!homeaddress.Value))
>
> In the same way implement the same logic to display homecity, homestate,
> homezip.
> Regards,
> SaraS
>|||Thanks Saras, It worked I just dint have it in the correct place. Stupid
typo mistake. Thanks a bunch have a great day!
"saras" wrote:
> U can achieve this by the following way:
> Use the following expresson to display Homeaddress:
> =IIF(IsNothing(Previous(Fields!Firm_no.Value)),
> Fields!homeaddress.Value,
> IIF(Fields!Firm_no.Value = Previous(Fields!Firm_no.Value),
> "",
> Fields!homeaddress.Value))
>
> In the same way implement the same logic to display homecity, homestate,
> homezip.
> Regards,
> SaraS
>|||Saras,
Sorry to bother again...but now its printing all the homeaddress I still
have this questions:
I have query with following column names:
cust_no, Firm_no, Name, homeaddress, homecity, homestate, homezip,
officeaddress, officecity, officestate, officezip
I want to be able to pull it this way that whenever there is a certain
unique Firm_no it should populate the homeaddress, homecity, homestate,
homezip for this customer.
What do I have to do to accomplish this?
Thanks
"Shan" wrote:
> I have query with following column names:
> cust_no, Firm_no, Name, homeaddress, homecity, homestate, homezip,
> officeaddress, officecity, officestate, officezip
> I want to be able to pull it this way that whenever there is a certain
> unique Firm_no it should populate the homeaddress, homecity, homestate,
> homezip for this customer.
> What do I have to do to accomplish this?
> Thanks|||Ok. I have these fields.
Cust_no, Firm_name, Firm_no, Name, OfficeAddr, OffCity, OffSt, OffZip,
HomeAddr, Homecity, HomeSt, HomeZip
In my report I only want to show based on my query the following format
Custo_no Firm_name Firm_no Name Address City State Zip
123 Keller 1111 Jon Doe Main Dallas TX 75000
321 Ebby 1122 Jon Doe Globe Pano TX 75009
102 Cold 1234 Jon Doe Holly Plano TX 75002
103 Nonmember 1000 Jon Doe Trench Allen TX 75001
109 Nonmember 1000 Jon Doe Trail Prosper TX 75003
Now I want to populate the Address field with customer's homeAddr anytime
the firm_no is 1000 which is a Non member otherwise if the firm_no is
anything else then populate the Address with their OfficeAddr. The Firm_no
1000 doesn't have an OfficeAddr so we want to populate it with customer's
homeaddr.
Thanks for your help.
"Shan" wrote:
> saras,
> I have a unique firm_no that I want to use when my report runs it should
> look like:
> Custo_no Firm_no Name Address City State Zip
> 123 1111 Jon Doe Main Dallas TX 75000
> 321 1122 Jon Doe Globe Pano TX 75001
> So when anytime the Firm_no is '1122' I want the address to print their
> homeaddress.
> Thanks for your help.
> "saras" wrote:
> > U can achieve this by the following way:
> >
> > Use the following expresson to display Homeaddress:
> >
> > =IIF(IsNothing(Previous(Fields!Firm_no.Value)),
> > Fields!homeaddress.Value,
> > IIF(Fields!Firm_no.Value = Previous(Fields!Firm_no.Value),
> > "",
> > Fields!homeaddress.Value))
> >
> >
> > In the same way implement the same logic to display homecity, homestate,
> > homezip.
> >
> > Regards,
> > SaraS
> >

query help

Here is a simplified example of what I need to accomplish.

I have a table that keeps track of plastic balls in tubs. Based on a bit column the record is either defining balls added to a tub or taken away. When the record is defined as adding balls to a tub it will say how many and what color, but when the flag says they have been taken away from a tub I only know the number removed and not the color.

There are multiple tubs and multiple colors. So say tub1 has 20 balls in it (50% red, %25 green, and %25 blue). Also say tub2 has 10 balls in it (100% red). This is our starting point.

Now on day one, 5 balls from tub two are put into tub one. So we know that 5 balls of 100% red are put into tub one. This means that tub one now has 25 balls in it. By doing some weighted percentages, tub one now has these percentages: red = 60%, green = 20%, and blue = 20%.

Say on day two however, 5 balls are removed from tub one and placed back into tub two. We cannot say anything about the colors, but that they are: .6red, .2green, and .2 blue. So if we want a percentage for tub two on day 2 we now get: .8red, .1green and .1blue.

The math for the new percentage is I believe = ((originalPercent * originalCount) + (addedPercent * addedAmount)) / newTotalBallCount

I need a query that will give me the percentages of the different colors in the tub for any given day. This is really a running percentage that has to take every transaction into account.

This is a complicated query fro me to figure out, but can someone point me in the right direction?

LLeuthard wrote:

Say on day two however, 5 balls are removed from tub one and placed back into tub two. We cannot say anything about the colors, but that they are: .6red, .2green, and .2 blue. So if we want a percentage for tub two on day 2 we now get: .8red, .1green and .1blue.

too superfluous.

is this thing about permutation and combination/? how did u say taht 6r,2g,2b?

|||Are you saying it is not worth my time or that it is impossible? Is there a stored proc I could write that would do this easily?|||

I got .6Red .2Blue and .2Green by saying that:

5 balls of 100% red were moved to tub1. so tub1 originally has a estimate of .5*20 = 10Red balls.

Take the estimate of 10Red and add the estimate of 5Red and we get an estimate of 15Red out of 25 in tub1. This makes for .6Red at the end of day 1.

Do the same with the others.

Query help

I have a project almost (or so I thought!) completed but now I need to add a column to a table which is throwing everything off. Right now I have TableMain which queries TableA. I need to add a column (bit) to TableMain and then run a query that is basicallyif (bitColumn) query TableB, else query TableA.for each row.

TableA contains a listing, TableB is groups where each group contains multiple items from TableA. The columns I want to pull from each table have the same names (ie. regardless of bitColumn, I want TableA.Name or TableB.Name)

I'm not sure how I can go about doing this, I greatly appreciate any help.

Can you please give following details.

Primary key and Fkey in each table

Example input and output data

|||

Paper (id(pkey), name, code, description, clipID(fkey), etc) <= Think of these as a sheet of paper.

Clip(id(pkey), name, description) <= Think of these as a group of papers paperclipped together.

Cart(id(pkey),user,paperID(fkey),qty)

if someone tries to order a paper that has a clipID, they are forced to buy the entire paperclip. My shopping cart works for regular papers, and I have it notifying the user that they are really ordering the paperclip. What I'm stuck on is displaying the shopping cart, etc.

Shopping Cart: Paper1 + Paper2 + Clip1, where Clip1 has Paper3 and Paper4.

I don't know how to make the cart show Clip1 or whether I should just force add each individual item to the shopping cart (making editing qtys difficult), etc. I was thinking about adding an isClip column to the cart and then the stored procudure returns Paper.name or Clip.name depending on whether isClip is 0 or 1... so that it would be

Cart(id(pkey),user,paperID(fkey),qty,isClip)

I don't know how I would write that query, and I'm not sure its even the best design Sad [:(]

|||

I would design like this.

Category table

Products Table

Product details table

In category table : categories are paper and clip

In products table: Different papers(only papers not associated with clips) and clips

In product Details table: Papers that goes with clips

Papers that are associated to clips are only sold as a bunch

When user click on paper, show paper details and when user clicks on clip, show all the papers associated to clip by querying from product details table.

|||A better design would be to have ALL papers clipped, even if it is only clipping a single paper. Then the user can only buy clips.|||

With all of the code already written, I'd like to avoid a complete design restructuring.

Motley: this is what I started working on last night until I ran into a problem. Each paper belongs to a certain topic, and in the catalog are listed under topic headings as the sp returns with order by topic. The papers in a clip do not need to (and rarely will) belong to the same topic. I could have a Clips topic that I display first and then the individual topics, but it would involve checking each clip and seeing how many papers reference it; or perhaps adding a Count field to the Clips table? I would still then need to figure out a way to return the correct ordering...I'm open to hearing other suggestions...

|||

edit...yeah, that doesn't work, never mind...

SELECT

clip.id, clip.name, clip.description,

ISNULL(paper.topic,'CLIP')

FROM

clips clip

LEFT JOIN

papers paper

clip.id = paper.kitID

ORDER BY

topic

|||

Basic query:

SELECT c.id as ClipID,c.Name as ClipName,c.Description as ClipDescription,p.id as PaperID,p.name as PaperName, p.code as PaperCode, p.Description as PaperDescription, etc

FROM clip c

JOIN paper p ON (c.PaperID=p.id)

Return clips and how many papers are attached:

SELECT c.id AS ClipID,c.name,c.description,COUNT(*)

FROM clip c

JOIN paper p ON (c.PaperID=p.id)

GROUP BY c.id,c.name,c.description

Of course this assumes that each paper has a clip associated with it.

With your original structure:

SELECT s.*,name,description

FROM cart s

JOIN clip c ON (s.ID=c.id and s.isClip=1)

UNION

SELECT s.*,name,description

FROM cart s

JOIN paper p ON (s.ID=p.id and s.isClip=0)

SELECT s.*,CASE WHEN c.id IS NOT NULL THEN c.name ELSE p.name END as Name, CASE WHEN c.id IS NOT NULL THEN c.description ELSE p.description END as Description

FROM cart s

LEFT JOIN clip c ON (c.id=s.id and s.isClip=1)

LEFT JOIN paper p ON (p.id=s.id and s.isClip=0)

sql

Wednesday, March 28, 2012

query help

I will try to be brief
I have two tables I am trying to join that share a foreign key.
the structures are:
table a: column table b: columns
foreign key employee ID employeeID
status earnings
effectivedate checkdate
each table can have multiple rows with the same employee ID. Table b can ha
ve multiple rows with the same checkdate. I am trying to query the two tabl
es so I can get the sum of the earnings for a particular checkdate and the e
mployees status at the time
of the check. Here is an example of the data and what I have written so far
:
tableA:
employeeID STATUS Effectivedate
100 fulltime 01/01/03
100 parttime 01/01/04
100 fulltime 03/27/04
101 fulltime 01/01/03
101 parttime 04/01/04
tableB:
employeeID earnings checkdate
100 25.00 03/25/04
100 97.00 03/25/04
101 10.00 03/25/04
If I query with the employeeID it is no problem:
select tableB.employeeID, STATUS, Effectivedate, sum(earnings), checkdate
from tableA, tableB
where tableA.employeeID=tableB.employeeID
and effectivedate=(select max(effectivedate) from tableA where tableA.employ
eeID=100 and effectivdate <='03/25/04')
and checkdate='03/25/04'
group by tableB.employeeID, STATUS, Effectivedate, checkdate
but... to do this on my tables which have thousands and thousands of rows f
or each ID each month will be painful.
How can I write this query so that the select will return the sum of earning
s by employeeID on a specific checkdate and the employee status on that date
. I thought of using a cursor, but I am not advanced enough to write one.
I am sure there has to be a
way to do this. Any help will be greatly appreciated!!!!I realize I should have posted this in data mining, but I don't want to doub
le post

Query help

I need to update 6 column on 1 table depending on 2 other
columns on another table (All 8 columns exists on both
tables)
here is what I am trying to write and it gives me error:
Update Table1
Set start_date = (Select start_date from Table2),
end_date = (Select end_date from Table2),
user1 = (Select user1 from Table2),
user2 = (Select user2 from Table2),
user3 = (Select user3 from Table2),
user4 = (Select user4 from Table2)
FROM Table2
Where Table2.project = Table1.project AND
Table2.Pjt_entity = Table1.Pjt_entity
Thanks for any help.Hi,
Try this,
Update Table1
Set a.start_date = b.start_date ,
a.end_date = b.end_date,
a.user1 = b.user1,
a.user2 = b.user2,
a.user3 = b.user3,
a.user4 = b.user4
FROM Table1 a,Table2 b
Where a.project = b.project
AND a..Pjt_entity = b.Pjt_entity
Tahnks
Hari
MCDBA
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:2d2001c486d0$5a97bdb0$a301280a@.phx.gbl...
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.|||Todd wrote:
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.
Well if you have a 1:1 between the tables, you just need to specify the
column to update:
Update Table1
Set start_date = b.start_date,
end_date = b.end_date,
etc...
FROM Table2 b
Where b.project = Table1.project AND
b.Pjt_entity = Table1.Pjt_entity
David G.|||David G. wrote:
> Todd wrote:
> Well if you have a 1:1 between the tables, you just need to specify
> the column to update:
> Update Table1
> Set start_date = b.start_date,
> end_date = b.end_date,
> etc...
> FROM Table2 b
> Where b.project = Table1.project AND
> b.Pjt_entity = Table1.Pjt_entity
Left off a table in the FROM clause. See Hari's post instead.
David G.

query help

Query help

I am trying to find invalid dates in a column after loading from a text file
but the queries I have doesn't give me any result.
Because of the error, I have loaded column as a varchar rather than datetime
so that I can find the invalid dates. My query is:
Select col from dbo.mytable
where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- For
month
-----
Select col from dbo.mytable
where substr(col, 4, 2) NOT in
(01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31) -- For Day
-----
Select col from dbo.mytable
where substr(col, 7, 2) NOT in (19,20) -- For year
-----
Typical dates in the text file are
01/12/1958
09/05/2007
04/23/1978
12/28/2003
01/01/1900
If I try to change the column data type from varchar to datetime I get an
error like 'can not convert to datetime, date is out of range'.
Thanks for any help.Well, I can think of a procedural approach.
Load the data as you have done and create a table variable with a datetime
column.
For each row, attempt an insert, or update to the table variable (put a
dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that key
somewhere else. Or on success, move the row to the final table, or choose
your own logic.
All that said, I was pretty sure DTS did this kind of thing for you.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
>I am trying to find invalid dates in a column after loading from a text
>file
> but the queries I have doesn't give me any result.
> Because of the error, I have loaded column as a varchar rather than
> datetime
> so that I can find the invalid dates. My query is:
> Select col from dbo.mytable
> where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
> For
> month
> -----
> Select col from dbo.mytable
> where substr(col, 4, 2) NOT in
> (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
> -- For Day
> -----
> Select col from dbo.mytable
> where substr(col, 7, 2) NOT in (19,20) -- For year
> -----
> Typical dates in the text file are
> 01/12/1958
> 09/05/2007
> 04/23/1978
> 12/28/2003
> 01/01/1900
> If I try to change the column data type from varchar to datetime I get an
> error like 'can not convert to datetime, date is out of range'.
> Thanks for any help.|||The message from DTS is not what the data is. It is something like this (I
don't have the exact error):
Can not insert data Source column (Column number 70) data type string -
destination column (column_name) data type
DATE..................something like that which doesn't tell you what
the exact invalid date is. If the data is small, you can scroll it up and
down and find it but when it is 2.4 million rows, it is hard to find it
manually or when the text file is ~800 MB.........
"Jay" wrote:
> Well, I can think of a procedural approach.
> Load the data as you have done and create a table variable with a datetime
> column.
> For each row, attempt an insert, or update to the table variable (put a
> dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that key
> somewhere else. Or on success, move the row to the final table, or choose
> your own logic.
> All that said, I was pretty sure DTS did this kind of thing for you.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
> >I am trying to find invalid dates in a column after loading from a text
> >file
> > but the queries I have doesn't give me any result.
> >
> > Because of the error, I have loaded column as a varchar rather than
> > datetime
> > so that I can find the invalid dates. My query is:
> >
> > Select col from dbo.mytable
> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
> > For
> > month
> > -----
> > Select col from dbo.mytable
> > where substr(col, 4, 2) NOT in
> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
> > -- For Day
> > -----
> > Select col from dbo.mytable
> > where substr(col, 7, 2) NOT in (19,20) -- For year
> > -----
> >
> > Typical dates in the text file are
> >
> > 01/12/1958
> > 09/05/2007
> > 04/23/1978
> > 12/28/2003
> > 01/01/1900
> >
> > If I try to change the column data type from varchar to datetime I get an
> > error like 'can not convert to datetime, date is out of range'.
> >
> > Thanks for any help.
>
>|||I've only used DTS when I had full control of the complete data stream, but
I though I remembered something about exceptions.
Still, if that isn't working for you and no one can help you with exception
filters in DTS, write the import filter yourself.
You could also try:
select *
from table
where datestr not LIKE '[0-1][0-9]/[[0-3][0-9]/[1-2][09][0-9][0-9]'
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:91B0F853-FD70-415D-A31E-17CE8EDC4E0E@.microsoft.com...
> The message from DTS is not what the data is. It is something like this (I
> don't have the exact error):
> Can not insert data Source column (Column number 70) data type string -
> destination column (column_name) data type
> DATE..................something like that which doesn't tell you what
> the exact invalid date is. If the data is small, you can scroll it up and
> down and find it but when it is 2.4 million rows, it is hard to find it
> manually or when the text file is ~800 MB.........
>
> "Jay" wrote:
>> Well, I can think of a procedural approach.
>> Load the data as you have done and create a table variable with a
>> datetime
>> column.
>> For each row, attempt an insert, or update to the table variable (put a
>> dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that
>> key
>> somewhere else. Or on success, move the row to the final table, or choose
>> your own logic.
>> All that said, I was pretty sure DTS did this kind of thing for you.
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
>> >I am trying to find invalid dates in a column after loading from a text
>> >file
>> > but the queries I have doesn't give me any result.
>> >
>> > Because of the error, I have loaded column as a varchar rather than
>> > datetime
>> > so that I can find the invalid dates. My query is:
>> >
>> > Select col from dbo.mytable
>> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
>> > For
>> > month
>> > -----
>> > Select col from dbo.mytable
>> > where substr(col, 4, 2) NOT in
>> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
>> > -- For Day
>> > -----
>> > Select col from dbo.mytable
>> > where substr(col, 7, 2) NOT in (19,20) -- For year
>> > -----
>> >
>> > Typical dates in the text file are
>> >
>> > 01/12/1958
>> > 09/05/2007
>> > 04/23/1978
>> > 12/28/2003
>> > 01/01/1900
>> >
>> > If I try to change the column data type from varchar to datetime I get
>> > an
>> > error like 'can not convert to datetime, date is out of range'.
>> >
>> > Thanks for any help.
>>|||On Aug 28, 4:40 am, DXC <D...@.discussions.microsoft.com> wrote:
> I am trying to find invalid dates in a column after loading from a text f=ile
> but the queries I have doesn't give me any result.
> Because of the error, I have loaded column as a varchar rather than datet=ime
> so that I can find the invalid dates. My query is:
> Select col from dbo.mytable
> where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- F=or
> month
> ----=--=AD--
> Select col from dbo.mytable
> where substr(col, 4, 2) NOT in
> (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,=25=AD,26,27,28,29,30,31) -- For Day
> ----=--=AD--
> Select col from dbo.mytable
> where substr(col, 7, 2) NOT in (19,20) -- For year
> ----=--=AD--
> Typical dates in the text file are
> 01/12/1958
> 09/05/2007
> 04/23/1978
> 12/28/2003
> 01/01/1900
> If I try to change the column data type from varchar to datetime I get an
> error like 'can not convert to datetime, date is out of range'.
> Thanks for any help.
Hi, there is an isdate function. Find the invalid dates and handle
them separately. HTH.|||That did it............Thanks..........
"SB" wrote:
> On Aug 28, 4:40 am, DXC <D...@.discussions.microsoft.com> wrote:
> > I am trying to find invalid dates in a column after loading from a text file
> > but the queries I have doesn't give me any result.
> >
> > Because of the error, I have loaded column as a varchar rather than datetime
> > so that I can find the invalid dates. My query is:
> >
> > Select col from dbo.mytable
> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- For
> > month
> > ----
> > Select col from dbo.mytable
> > where substr(col, 4, 2) NOT in
> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25-,26,27,28,29,30,31) -- For Day
> > ----
> > Select col from dbo.mytable
> > where substr(col, 7, 2) NOT in (19,20) -- For year
> > ----
> >
> > Typical dates in the text file are
> >
> > 01/12/1958
> > 09/05/2007
> > 04/23/1978
> > 12/28/2003
> > 01/01/1900
> >
> > If I try to change the column data type from varchar to datetime I get an
> > error like 'can not convert to datetime, date is out of range'.
> >
> > Thanks for any help.
> Hi, there is an isdate function. Find the invalid dates and handle
> them separately. HTH.
>

Query help

I have a table with one column that has all negative values.
IO
-70.72
-72.93
-67.38
-65.05
-62.31
-65.52
-69.98
now we have to find that how many samples of IO are between
-50 to -40 range
-60 to -70 range
-70 to -60 range
How can I accomplish this. Thanks for your help.This should give you something to think about.
SELECT CONVERT(int, ThatColumn / 10) * 10 as RangeStart,
count(*) as Rows
FROM TheTable
GROUP BY CONVERT(int, ThatColumn / 10)
ORDER BY RangeStart
Roy Harvey
Beacon Falls, CT
On Fri, 7 Sep 2007 10:00:08 -0700, Shan
<Shan@.discussions.microsoft.com> wrote:
>I have a table with one column that has all negative values.
>IO
>-70.72
>-72.93
>-67.38
>-65.05
>-62.31
>-65.52
>-69.98
>now we have to find that how many samples of IO are between
>-50 to -40 range
>-60 to -70 range
>-70 to -60 range
>How can I accomplish this. Thanks for your help.|||This doesn't work. Any other ideas.
"Shan" wrote:
> I have a table with one column that has all negative values.
> IO
> -70.72
> -72.93
> -67.38
> -65.05
> -62.31
> -65.52
> -69.98
> now we have to find that how many samples of IO are between
> -50 to -40 range
> -60 to -70 range
> -70 to -60 range
> How can I accomplish this. Thanks for your help.|||On Fri, 7 Sep 2007 15:02:02 -0700, Shan
<Shan@.discussions.microsoft.com> wrote:
>This doesn't work. Any other ideas.
CREATE TABLE TestRanges (d decimal (13,2) NOT NULL)
INSERT TestRanges VALUES (-70.72)
INSERT TestRanges VALUES (-72.93)
INSERT TestRanges VALUES (-67.38)
INSERT TestRanges VALUES (-65.05)
INSERT TestRanges VALUES (-62.31)
INSERT TestRanges VALUES (-65.52)
INSERT TestRanges VALUES (-69.98)
SELECT CONVERT(int, d / 10) * 10 as RangeStart,
count(*) as Rows
FROM TestRanges
GROUP BY CONVERT(int, d / 10)
ORDER BY RangeStart
RangeStart Rows
-- --
-70 2
-60 5
Is this not close?
Roy Harvey
Beacon Falls, CT

Query Help

How can I select the count of the distinct values of a
column '
Thanks.Try:
select
count (distinct MyCol)
from
MyTable
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
How can I select the count of the distinct values of a
column '
Thanks.|||SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
FROM YourTable
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.|||Thanks but I forgot to add that it is a char column with
the numbers in it '
>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>> How can I select the count of the distinct values of a
>> column '
>> Thanks.
>
>.
>|||Please ignore the message about the column being a char
column........
Thanks.
>--Original Message--
>How can I select the count of the distinct values of a
>column '
>Thanks.
>.
>|||It makes no difference. You wanted a count of the distinct values. From
Northwind, try:
select
count (distinct CustomerID)
from
Orders
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:503401c4c5c7$6ed7d9a0$a301280a@.phx.gbl...
Thanks but I forgot to add that it is a char column with
the numbers in it '
>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>> How can I select the count of the distinct values of a
>> column '
>> Thanks.
>
>.
>|||Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>|||Thanx much. It sure means a lot. I saw Itzik and his wife in April.
They're both doing well. :-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OBcRNPtyEHA.3820@.TK2MSFTNGP11.phx.gbl...
Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>

Query help

I need to update 6 column on 1 table depending on 2 other
columns on another table (All 8 columns exists on both
tables)
here is what I am trying to write and it gives me error:
Update Table1
Set start_date = (Select start_date from Table2),
end_date = (Select end_date from Table2),
user1 = (Select user1 from Table2),
user2 = (Select user2 from Table2),
user3 = (Select user3 from Table2),
user4 = (Select user4 from Table2)
FROM Table2
Where Table2.project = Table1.project AND
Table2.Pjt_entity = Table1.Pjt_entity
Thanks for any help.Hi,
Try this,
Update Table1
Set a.start_date = b.start_date ,
a.end_date = b.end_date,
a.user1 = b.user1,
a.user2 = b.user2,
a.user3 = b.user3,
a.user4 = b.user4
FROM Table1 a,Table2 b
Where a.project = b.project
AND a..Pjt_entity = b.Pjt_entity
Tahnks
Hari
MCDBA
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:2d2001c486d0$5a97bdb0$a301280a@.phx.gbl...
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.|||Todd wrote:
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.
Well if you have a 1:1 between the tables, you just need to specify the
column to update:
Update Table1
Set start_date = b.start_date,
end_date = b.end_date,
etc...
FROM Table2 b
Where b.project = Table1.project AND
b.Pjt_entity = Table1.Pjt_entity
David G.|||David G. wrote:
> Todd wrote:
>> I need to update 6 column on 1 table depending on 2 other
>> columns on another table (All 8 columns exists on both
>> tables)
>> here is what I am trying to write and it gives me error:
>> Update Table1
>> Set start_date = (Select start_date from Table2),
>> end_date = (Select end_date from Table2),
>> user1 = (Select user1 from Table2),
>> user2 = (Select user2 from Table2),
>> user3 = (Select user3 from Table2),
>> user4 = (Select user4 from Table2)
>> FROM Table2
>> Where Table2.project = Table1.project AND
>> Table2.Pjt_entity = Table1.Pjt_entity
>> Thanks for any help.
> Well if you have a 1:1 between the tables, you just need to specify
> the column to update:
> Update Table1
> Set start_date = b.start_date,
> end_date = b.end_date,
> etc...
> FROM Table2 b
> Where b.project = Table1.project AND
> b.Pjt_entity = Table1.Pjt_entity
Left off a table in the FROM clause. See Hari's post instead.
--
David G.

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 for table info....

Hello,
What table / column would I query to get the DESCRIPTION
from a table.
This query does not include the Column_Description
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME = 'service_ticket'
order by Column_name
Thanks in advance,
Bobuse fn_listextendedproperty , change 'YourTable' to the name of your
table
SELECT * FROM ::fn_listextendedproperty (null, 'user', 'dbo', 'table',
'YourTable', 'column',
default)
http://sqlservercode.blogspot.com/|||That's awesome...
Thanks much!
Bob
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141310307.524701.104020@.i39g2000cwa.googlegroups.com...
> use fn_listextendedproperty , change 'YourTable' to the name of your
> table
> SELECT * FROM ::fn_listextendedproperty (null, 'user', 'dbo', 'table',
> 'YourTable', 'column',
> default)
>
> http://sqlservercode.blogspot.com/
>sql

Query for finding trailing spaces in a column

It seems like our application inserting trailing spaces
into the varchar field (spaces before and after the
string). Can anyone help me with a query to find out which
rows have trailing spaces in a column ?
Thanks for any help......
SET NOCOUNT ON
CREATE TABLE #splunge
(
foo VARCHAR(10)
)
GO
INSERT #splunge SELECT 'val1'
INSERT #splunge SELECT 'val2 '
INSERT #splunge SELECT ' val3'
INSERT #splunge SELECT ' val4 '
GO
-- leading spaces:
SELECT * FROM #splunge WHERE LTRIM(foo) != foo
-- trailing spaces:
SELECT * FROM #splunge WHERE DATALENGTH(RTRIM(foo))!=DATALENGTH(foo)
GO
DROP TABLE #splunge
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2796201c4642a$94e8df30$a601280a@.phx.gbl...
> It seems like our application inserting trailing spaces
> into the varchar field (spaces before and after the
> string). Can anyone help me with a query to find out which
> rows have trailing spaces in a column ?
> Thanks for any help......
|||Thanks........

>--Original Message--
>SET NOCOUNT ON
>CREATE TABLE #splunge
>(
> foo VARCHAR(10)
>)
>GO
>INSERT #splunge SELECT 'val1'
>INSERT #splunge SELECT 'val2 '
>INSERT #splunge SELECT ' val3'
>INSERT #splunge SELECT ' val4 '
>GO
>-- leading spaces:
>SELECT * FROM #splunge WHERE LTRIM(foo) != foo
>-- trailing spaces:
>SELECT * FROM #splunge WHERE DATALENGTH(RTRIM(foo))!
=DATALENGTH(foo)
>GO
>DROP TABLE #splunge
>GO
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"John" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2796201c4642a$94e8df30$a601280a@.phx.gbl...
which
>
>.
>
|||select * from TheTable where theColumn like '% '
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2796201c4642a$94e8df30$a601280a@.phx.gbl...
> It seems like our application inserting trailing spaces
> into the varchar field (spaces before and after the
> string). Can anyone help me with a query to find out which
> rows have trailing spaces in a column ?

Query for finding trailing spaces in a column

It seems like our application inserting trailing spaces
into the varchar field (spaces before and after the
string). Can anyone help me with a query to find out which
rows have trailing spaces in a column '
Thanks for any help......SET NOCOUNT ON
CREATE TABLE #splunge
(
foo VARCHAR(10)
)
GO
INSERT #splunge SELECT 'val1'
INSERT #splunge SELECT 'val2 '
INSERT #splunge SELECT ' val3'
INSERT #splunge SELECT ' val4 '
GO
-- leading spaces:
SELECT * FROM #splunge WHERE LTRIM(foo) != foo
-- trailing spaces:
SELECT * FROM #splunge WHERE DATALENGTH(RTRIM(foo))!=DATALENGTH(foo)
GO
DROP TABLE #splunge
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2796201c4642a$94e8df30$a601280a@.phx.gbl...
> It seems like our application inserting trailing spaces
> into the varchar field (spaces before and after the
> string). Can anyone help me with a query to find out which
> rows have trailing spaces in a column '
> Thanks for any help......|||Thanks........
>--Original Message--
>SET NOCOUNT ON
>CREATE TABLE #splunge
>(
> foo VARCHAR(10)
>)
>GO
>INSERT #splunge SELECT 'val1'
>INSERT #splunge SELECT 'val2 '
>INSERT #splunge SELECT ' val3'
>INSERT #splunge SELECT ' val4 '
>GO
>-- leading spaces:
>SELECT * FROM #splunge WHERE LTRIM(foo) != foo
>-- trailing spaces:
>SELECT * FROM #splunge WHERE DATALENGTH(RTRIM(foo))!
=DATALENGTH(foo)
>GO
>DROP TABLE #splunge
>GO
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"John" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2796201c4642a$94e8df30$a601280a@.phx.gbl...
>> It seems like our application inserting trailing spaces
>> into the varchar field (spaces before and after the
>> string). Can anyone help me with a query to find out
which
>> rows have trailing spaces in a column '
>> Thanks for any help......
>
>.
>|||select * from TheTable where theColumn like '% '
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2796201c4642a$94e8df30$a601280a@.phx.gbl...
> It seems like our application inserting trailing spaces
> into the varchar field (spaces before and after the
> string). Can anyone help me with a query to find out which
> rows have trailing spaces in a column '

Query for finding trailing spaces in a column

It seems like our application inserting trailing spaces
into the varchar field (spaces before and after the
string). Can anyone help me with a query to find out which
rows have trailing spaces in a column '
Thanks for any help......SET NOCOUNT ON
CREATE TABLE #splunge
(
foo VARCHAR(10)
)
GO
INSERT #splunge SELECT 'val1'
INSERT #splunge SELECT 'val2 '
INSERT #splunge SELECT ' val3'
INSERT #splunge SELECT ' val4 '
GO
-- leading spaces:
SELECT * FROM #splunge WHERE LTRIM(foo) != foo
-- trailing spaces:
SELECT * FROM #splunge WHERE DATALENGTH(RTRIM(foo))!=DATALENGTH(foo)
GO
DROP TABLE #splunge
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2796201c4642a$94e8df30$a601280a@.phx
.gbl...
> It seems like our application inserting trailing spaces
> into the varchar field (spaces before and after the
> string). Can anyone help me with a query to find out which
> rows have trailing spaces in a column '
> Thanks for any help......|||Thanks........

>--Original Message--
>SET NOCOUNT ON
>CREATE TABLE #splunge
>(
> foo VARCHAR(10)
> )
>GO
>INSERT #splunge SELECT 'val1'
>INSERT #splunge SELECT 'val2 '
>INSERT #splunge SELECT ' val3'
>INSERT #splunge SELECT ' val4 '
>GO
>-- leading spaces:
>SELECT * FROM #splunge WHERE LTRIM(foo) != foo
>-- trailing spaces:
>SELECT * FROM #splunge WHERE DATALENGTH(RTRIM(foo))!
=DATALENGTH(foo)
>GO
>DROP TABLE #splunge
>GO
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"John" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2796201c4642a$94e8df30$a601280a@.phx
.gbl...
which[vbcol=seagreen]
>
>.
>|||select * from TheTable where theColumn like '% '
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2796201c4642a$94e8df30$a601280a@.phx
.gbl...
> It seems like our application inserting trailing spaces
> into the varchar field (spaces before and after the
> string). Can anyone help me with a query to find out which
> rows have trailing spaces in a column '

Wednesday, March 21, 2012

Query for column name and type

hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar
You could use sp_help.
Also consider INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS.
See SQL Server 2000 Books Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar
|||Hi,
Execute the below query, replace the object_name with your object name
required.
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns
where table_name='object_name'
Tahnks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
> hi,
> can some one give me a query which retrieves column name ,
> and it's type ,by taking input as a table or view name..
> Sridhar
|||Thanks to you all...
>--Original Message--
>You could use sp_help.
>Also consider INFORMATION_SCHEMA.TABLES and
INFORMATION_SCHEMA.COLUMNS.
>See SQL Server 2000 Books Online for more information.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:54b501c42d17$90dce570$a401280a@.phx.gbl...
>hi,
>can some one give me a query which retrieves column name ,
>and it's type ,by taking input as a table or view name..
>Sridhar
>
>.
>

Query for column name and type

hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
SridharYou could use sp_help.
Also consider INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS.
See SQL Server 2000 Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar|||Hi,
Execute the below query, replace the object_name with your object name
required.
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns
where table_name='object_name'
Tahnks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
> hi,
> can some one give me a query which retrieves column name ,
> and it's type ,by taking input as a table or view name..
> Sridhar|||Thanks to you all...
>--Original Message--
>You could use sp_help.
>Also consider INFORMATION_SCHEMA.TABLES and
INFORMATION_SCHEMA.COLUMNS.
>See SQL Server 2000 Books Online for more information.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:54b501c42d17$90dce570$a401280a@.phx.gbl...
>hi,
>can some one give me a query which retrieves column name ,
>and it's type ,by taking input as a table or view name..
>Sridhar
>
>.
>

Friday, March 30, 2012

query help

query help

Query help

Query Help

query help

Query help

Wednesday, March 28, 2012

query help

Query help

query help

Query help

Query help

Query Help

Query help

query help

Friday, March 23, 2012

Query for table info....

Query for finding trailing spaces in a column

Query for finding trailing spaces in a column

Query for finding trailing spaces in a column

Wednesday, March 21, 2012

Query for column name and type

Query for column name and type

query assistance

Blog Archive

About Me