Help Comparing '01234' With '1234' (with Complications)
Aug 31, 2004
I have to join on two fields, both are of type char(8), but one is entered by the application so it maintains leading 0's while the other is hand entered, so there are no leading 0's (and we're never going to get our users to ALWAYS enter leading 0's).
So, since they're numbers anyway, I figured I would just convert them both to integers and join on that as in:
...CAST(ord_no1 as integer) = CAST(ord_no2 as integer)
Which works :), BUT...
Since the ord_no field is actually a char(8) field, users can enter stuff other than numbers in it. Is there a way that I can use CAST as in the above, but let it ignore values that can not be converted? If it's not all numberals, it won't match anyway so I don't need to worry about them, however, when joining on that field, it has to look at all the records and perform the calculation and it's failing on a handful of them.
Any help would be greatly appreciated, thank you.
View 4 Replies
ADVERTISEMENT
Jan 31, 2006
Here is the first table:CREATE TABLE "Games"("ID" int,"Title" varchar(30),"Popularity" int)Here is the second table:CREATE TABLE "Related"("ID" int,"rID" int)Here is the data for the first table:INSERT INTO Games (id, title, popularity) VALUES (1, 'Tag Dodgeball', 10)INSERT INTO Games (id, title, popularity) VALUES (2, 'Freeball', 10)INSERT INTO Games (id, title, popularity) VALUES (3, 'Doctor Dodgeball', 10)INSERT INTO Games (id, title, popularity) VALUES (4, 'Kickball', 8)INSERT INTO Games (id, title, popularity) VALUES (5, 'Fooseball', 8)INSERT INTO Games (id, title, popularity) VALUES (6, 'Basketball', 7)INSERT INTO Games (id, title, popularity) VALUES (7, 'Knockout', 6)Here is the data for the second table:INSERT INTO Related (ID, rID) VALUES (1,2)INSERT INTO Related (ID, rID) VALUES (2,1)INSERT INTO Related (ID, rID) VALUES (1,3)INSERT INTO Related (ID, rID) VALUES (2,3)INSERT INTO Related (ID, rID) VALUES (3,1)INSERT INTO Related (ID, rID) VALUES (3,2)INSERT INTO Related (ID, rID) VALUES (6,7)INSERT INTO Related (ID, rID) VALUES (7,6)Now, lets say a person wants to grab the top three results:SET ROWCOUNT 3Select * from Game ORDER BY PopularityResult Set:1 Tag Dodgeball 102 Freeball 103 Dr. Dodgeball 10But,
what if this person doesn't want to play three related games in a row?
This is my dilemma. How do I get the following result set:1 Tag Dodgeball 102 Kickball 83 Fooseball 8The
related table tells which games are related to which, but how do I get
the select statement to realize that it should not select any games
that are related once it already has one in the result set?I can't use distinct, b/c if I do, any games which don't have a relationship are eliminated, as their rID is null.Pseudo-Code might look something like this:SET ROWCOUNT 3Select * from Game ORDER BY Popularity (where ID !=rID)Please help. I've banging my head against this one for quite some time.Respectfully,David.
View 1 Replies
View Related
Jun 26, 2006
Hello,
I am trying to retrieve the data that are not more than 3 months. How do I do this? The closest thing that I can do is...
(dbo.CLASSIFIEDADS.PostDate > CONVERT(DATETIME, '2006-03-26 00:00:00', 102))
I want to be able to put 3 months in there somehow...
Thanks in Advance!
View 2 Replies
View Related
Jun 13, 2008
Hi,
I have a table with 3 columns inside
- record_id (int)
- user_id (varchar)
- login_date (date)
it is a many-to-many relationship table that record login date of users
Now, I want if I want to COUNT the users who login before 31 May 2008, I would use
SELECT COUNT(*)
FROM table1
WHERE login_date < '2008-31-05'
That's works
But the problem is I want to split the result to
- How many people visit the site only once?
- How many people visit the site only twice?
- How many people visit the site three time?
How do I do that?
please help
Thank you
View 8 Replies
View Related
Aug 29, 2004
Hi,
I have a field in database money. When I enter value for it the amount entered is for example 20.000. How can I compare this value with noraml vaules i.e. like 20 in my search engine. Will I need to convert it to varchar and then compare it or is there some other way. Also if I need to convert it to varchar, how can I do it?
Thanks in advance,
Uday
View 6 Replies
View Related
Jan 29, 2005
hi ... please look at it :
<< The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. >>
ok ... it was my error mess, but i just wrote this code :
***********************************************************
Dim conPubs As SqlConnection
Dim cmdSelect As SqlCommand
Dim strSelect As String
conPubs = New SqlConnection ("Server=localhost;uid=ggggg;pwd=uuuu;database=eeee")
strSelect = "Select username From userpass Where username = '" & usernametxt.Text & "' And password = '" & passtxt.Text & "'"
cmdSelect = New SqlCommand( strSelect, conPubs )
conPubs.Open()
if Not(cmdSelect.ExecuteScalar() IS Nothing) then 'findeduser.text
response.redirect("welcome.aspx")
conPubs.Close()
else
msg.text="ID or Pass is incorrect!!"
conPubs.Close()
end if
***********************************************************
what is wrong?
can you help me?
me : mhamini@gmail.com
View 2 Replies
View Related
Jan 28, 2006
I am wondering how I would create a SELECT that will select the most recent date from one of two tables. For example, table1 has a field called LastUpdate and table 2 has a field called LastUpdate. I need to grab only the most recent date. I tried this using an inner join...but that didn't work because it only picks the lastupdate form one table only. talbe1 and table2 are tied by table2.table1id.
Can anyone help?
View 7 Replies
View Related
May 23, 2006
Hi All,
I have a database field (datestamp) which returns the date the records were inserted into the database. The datestamp was created with the now(); function in .net and is in the following format: 5/23/2006 2:27:45 AM
I basically want to return all records that were inputted more than 28 days ago. I have had alook though some other posts and below is the closest query that i could find but unfortunately it does not work for me.
SELECT id, datestampFROM table WHERE datestamp > DateAdd(d, 28, GetDate())
Thanks in advance,
Jake
View 2 Replies
View Related
Mar 1, 2001
Hi Guys,
I'm afraid the schemas of the Development and Production versions of a database are in fact diferent.
How may I compare two database schemas?
Thanks in advance !-)
View 1 Replies
View Related
May 3, 2001
Hi ,
I have three tables T1 , T2 AND T3. T3 is having fields as a combination of T1 and T2 fields.How can I compare T1 and T2 field values with T3 FIELD VALUES.
View 4 Replies
View Related
Jul 10, 2001
How do i compare 2 dates without comparing their Time portions. I have a Datetime column in my table which for example has values :-
6/6/2001 7:23:42 PM
6/6/2001 8:01:08 PM and so on....
I have a Stored Procedure in which i am trying to fetch values from this table. The SP has 2 DateTime parameters @DateFrom and @Dateto. This SP is called from a Web page. On the Web Page the user just enters mm/dd/yyyy values for the 2 Date parameters. Therefore the Time portions defaults to 12:00 AM. So when the user passes 6/6/2001 as Date From and 6/6/2001 as Date To values, he doesn't get back the 2 records mentioned above since the Time portion makes the 2 records fall out of the date range.
Is there a way to just do a comparison on the Date portions i.e. give me all the records for 6/6/2001 irrespective of the time. I don't wan't to do individual comparisons for Day then Month and then Year.
Thanks
Sanjay.
View 5 Replies
View Related
Sep 1, 2003
Hello all,
I'm new to SQL Server so if the following sounds stupid then apologies. I am trying to design a query which compares columns and filters according to the result of the comparison. We are a UK based charity that provides financial help to families with disabled children (www.familyfund.org.uk). We have a large database (250,000 entries) which we know contains some duplicate/split files from a recent migration. We need to identify these files but not delete them. Currently I am using the following:
SELECT dbo.Families.famId, dbo.Address.street, dbo.Children.childId, dbo.Address.postcode
FROM dbo.Children INNER JOIN
dbo.Families ON dbo.Children.family_no = dbo.Families.famId INNER JOIN
dbo.Persons ON dbo.Families.famId = dbo.Persons.famId INNER JOIN
dbo.Address ON dbo.Persons.addressId = dbo.Address.addressId
WHERE (NOT (dbo.Children.eligStatus IN (3, 4)))
GROUP BY dbo.Children.childId, dbo.Address.postcode, dbo.Families.famId, dbo.Address.street
HAVING (dbo.Address.street IS NOT NULL)
ORDER BY dbo.Address.street
Obviously this returns all 250,000 records and then we have to search manually. We would like to run a query which compares families.famID to address.street so that where famId has more than one address attched it is returned to the results grid. Does this make sense? is it possible? Any help would be gratefully received
Thanks in advance
Mark
:confused:
View 8 Replies
View Related
Mar 22, 1999
I have a Development and Production database and they are a little out of sink. Does any one know of a way to compare two databases (tables, fields, sp) and report the differences? Thanks in advance.
View 3 Replies
View Related
Sep 28, 2005
Hey all,
Trying to compare unique fields from two databases.
Basically what i'm trying to do is get all of the id's out of one field that don't exsist in the 2nd database.
so I have this query
Code:
select Distinct Id
from table1
where ID!= (select Distinct ID from table2)
I'm getting this error
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Now I don't understand why because then I query
select id from tables 2
I only get 1 value for each row.
Now you should know, the two columns in either table are different, in one of them the first value is "1" in the other the first value is "00000001" although I don't think that should effect anything.
Any idea's?
Thanks
View 1 Replies
View Related
Dec 10, 2004
i am using sql server ver 7
i am trying to do
select *
from independent_investment_certificate_temp
where independent_investment_certificate_temp.ticker_key = 31
and convert(varchar(40), cast(independent_investment_certificate_temp.maturity_date AS datetime),107) >
convert(varchar(40), cast('2006-12-10 10:29:50.733' AS datetime),107)
independent_investment_certificate_temp.maturity_date value is '2005-01-21 00:00:00.000'
the query above returns a value even though '2005-01-21 00:00:00.000' is clearly < '2006-12-10 10:29:50.733'
but why can anyone plz explain.. how can i compare dates stripping tyhem of the time so that all dates on the same day will be
equivalent regardless of the time
View 1 Replies
View Related
Aug 25, 2005
Hi, i got this query below, which I'd like to use to compare two sets of date...
select count(*) as rec, convert(char(6),dateEntered,13) as dateEntered from EmailTracker where chtype = '1' AND convert(char(12),dateEntered,13) >= '"& stdate &"' AND convert(char(12),dateEntered,13) <= '"& endate &"' group by convert(char(6),dateEntered,13)
the format of the stdate and dateEntered (a datetime datatype) looks like - 25 Aug 2005
right now, the query simply uses the first 2 letters of the date to fetch the record. That would not do the job as i want the thing to compare the whole lot! not just the 1st two letters...
Any help pls?
View 5 Replies
View Related
Aug 2, 2004
Hi everyone,
I have 2 seperate databases and I need to check for rows that are different from each other in a table.
I used access to link the tables in a database and am using queries to check the tables. However, I am having trouble formulating the SQL. What I want to do is not just check for the ID field to see if it exists, but to make sure the whole row exists. How can I form an SQL statement for this?
I tried something like:
Select * from table1 where Column1 NOT IN (Select Column1 from Table2) AND Column2 NOT IN (Select Column2 from Table2). However, I do not think this is correct. I want to make sure that the rows are compared, not individual values.
Any help would be appreciated.
Thanks,
Pankaj
View 5 Replies
View Related
Oct 29, 2004
IS there an easy way to compare two tables in sqlserver? I only need to display items that are different between table a and b.
thanks in advance for your help!
View 9 Replies
View Related
Dec 16, 2004
Does anyone know of a quick and easy to compare 2 datetime values based only on the month and year.
For example,
FromDate = 11/30/2004
ToDate = 12/30/2004
I just need to compare the 11/2004 to 12/2004 using function like <=, =, >=, etc.
Any suggestions?
Thanks
View 5 Replies
View Related
Jan 15, 2007
whats the best way to compare two row values and concatenate those two
For example
Table1
-------
Col1 Col2
----------
A 1
A 4
B 2
C 3
A 5
I need a query to return something like
"where (A=1 or A=4 or A=5) and (b=2) and (c=3)"
Thanks in advance
View 11 Replies
View Related
Jan 14, 2004
Hi all,
Sql server 7
i have two databases best and books.Most of the tables present in best are in books except some.
i have been given a task to list out the tables and columns of which are present in best database and not in books database.
Pls suggest me the simplest and quickest way to do this.
this is very urgent.
waiting for reply.
TIA
Adil
View 4 Replies
View Related
Apr 4, 2008
I will try to explain my problem as best as possible without going into too much detail.
Basically I have two tables,
AgentSchedule
AgentPunchData
These tables both contain agent names.
The schedule table has a column called "SHEND" which is a DATETIME datatype which identifies when an agent shift should end.
The entries all have a date attached which means nothing, for example "12/30/1899 9:00:00 PM"
The PunchData table has a column called "LOGOUT" which is also a DATETIME, the entries will have the real date and look something like this: "3/28/2008 9:23:00 PM"
I need to strip the date out of these functions so I can compare the times. I need to query the databases and return any instances where the LOGOUT time is > the shift end time (SHEND).
So far nothing I've tried has been successful. I'm completely stumped, I've tried so many different things and nothing compares the two times correctly. SQL DATETIME is the biggest pain in the ass, I wish we had SQL 2008 so I could just use TIME.
View 6 Replies
View Related
Apr 9, 2008
hi, i have a table with persons(babies) and their date of birth. I want to delete the persons above 6 months old can u plse tell me the query ?
delete
from babies
where ......
View 7 Replies
View Related
May 30, 2008
Hi Guys,
I am looking for some links to white paper where I can find comparison between SQL 2008 BI features and Business Objects.
Can anyone help me on this please? Is this right place to ask this question, if not where can i post this question.
Thanks
View 1 Replies
View Related
Jun 3, 2008
Hello,
I have some code that is trying to first find the first date of service that a person visited. Using the min function is not working. Is there another function? Right now I am still getting multiple rows for each person instead of just the first visit.
Then I want to be able to compare the first date of service to the date of birth and determine if the patient first visited before they were 2 years old. I can't figure out how to do this.
select distinct b.person_id,b.date_of_birth,min(a.enc_timestamp)
from patient_encounter a
join person b on a.person_id = b.person_id
where b.date_of_birth >= '20060301' and b.date_of_birth <= '20060531'
group by b.person_id,b.date_of_birth,a.enc_timestamp
View 4 Replies
View Related
Nov 30, 2005
Comparing only Time in Sql?
Hi Dears!
I have two Different DateTime Values i want to Compare only their times independent of Dates How to do it?
let
DateTime @dt1='2004-10-30 19:30:00.917'
DateTime @dt2='2005-11-29 15:00:00.917'
accordint to my requirements
@dt1>=@dt2 should be true as 19:30>=15:00
because i have to compare only the time not the Date how to achieve this in sql .Function required.
Thnx In Advance
View 3 Replies
View Related
Jan 31, 2006
This is a bit similar to the topic at
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56058
but with a bit of a twist...
I have
Batch Sample Value
0 0 A
0 1 A
1 0 A
1 1 B
2 0 B
2 1 B
...
I'm looking to get something that would provide a row comparison - something that looks like
Batch Different
0 FALSE
1 TRUE
2 FALSE
...
Any suggestions for a query that would do this? Thanks!
View 3 Replies
View Related
Feb 23, 2006
Hi All
I have 2 tables
dbo.aus
dbo.us
aus has column ausid
us has column usersid
how do i ran a query to filter out all matching values in the 2 tables
basically i want a list of values that are the same in both tables
thanks
View 3 Replies
View Related
Apr 10, 2007
Hello,
I am looking for some "best practice" information on how to properly setup a comparison of SQL data -vs- an Excel spreadsheet. I've never performed this task before, and will likely have to do more in the future. How can I define the range of cells to be used from the xls? There are over 1000 rows in the xls, so a Select * from dbo.table where dbo.column = 'x' just isn't practical.
Any information/direction that's out there is most appreciated!
Many thanks
View 1 Replies
View Related
Aug 24, 2007
I'm having a bit of a brain melt.
I've got two identical tables (except for the names) and I need to run a query that outputs the rows that aren't in the primary table.
To clarify table one has 38,450 records and table two has 30,703. I need to output the records that are in table one but not table two.
Everything I've tried keeps returning the records that are in it. They have a ID as primary key and a userid field which is what I want to be able to list.
Any ideas?
Thanks.
View 12 Replies
View Related
Oct 24, 2007
Hi,
I have a table with 50 + columns....
I have a single record and i want to basically say are there any that are the same as this one.. but without using 50 where clauses...
Any Suggestions
View 7 Replies
View Related
Nov 20, 2007
Is there a way to create a query which compares two result sets of results?
for example the result set of my first select statement is 7,1,8 and the second result set is 7,1,9..is there a query wherein i can compare the two results? i just wanted to reveal that there is a diffrence between the two..
thank you..
Funnyfrog
View 5 Replies
View Related
Dec 24, 2007
How can I select records that have changed or are new when comparing a previous copy of a table with the live version of the table? There is no datetime stamp in these tables. Many thanks in advance.
View 3 Replies
View Related
