SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sonu619
Posting Yak Master

195 Posts

Posted - 02/14/2013 :  21:23:54  Show Profile  Reply with Quote
Hi Guys,

Here is my Sample Table and Data

Create Table TableA
(
ID INT,
Gender varchar(10),
BP varchar(50)

)

Create Table TableB
(
ID Int,
City varchar(20),
BP1 varchar(20),
BP2 Varchar(20)
)

Insert Into TableA
values ('1','Male','140/90')
Insert Into TableA
values ('2','Male','130/80')
Insert Into TableA
values ('3','Male','140/75')
Insert Into TableA
values ('4','Male','112/90')


Insert Into TableB
Values ('1','NY','140','90')
Insert Into TableB
Values ('10','NY','140','90')
Insert Into TableB
Values ('11','NY','140','75')
Insert Into TableB
Values ('4','NY','112','90')


Select * from TableA
Select * from TableB

Select
A.*,
B.*
From TableA A
Inner Join TableB B On A.ID = B.ID
Where A.BP <= ???

My Requirement IS If BP is <= 14/90. I want to Compare TABLEA.BP <= TABLEB.BP1 & TABLEB.BP2.I know its gonna use Substring Function.
End Result should be

ID Gender BP ID City BP1 BP2
1 Male 140/90 1 NY 140 90

Thanks for help.

Sonu619
Posting Yak Master

195 Posts

Posted - 02/14/2013 :  21:51:32  Show Profile  Reply with Quote
I got Half Solution.

Select
A.*,
B.*
From TableA A
Inner Join TableB B On A.ID = B.ID
Where B.BP1 <= SUBSTRING(BP,1,CHARINDEX('/',BP)-1)
AND B.BP2 <= LTRIM(RIGHT(BP, CHARINDEX('/', BP + '/')-2) )

My requirement is Matching row from both table and BP Less then 140/90..

Any advise?
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/14/2013 :  21:57:23  Show Profile  Reply with Quote
Just for update I can use
Select
A.*,
B.*
From TableA A
Inner Join TableB B On A.ID = B.ID
Where SUBSTRING(BP,1,CHARINDEX('/',BP)-1) = '140'
AND LTRIM(RIGHT(BP, CHARINDEX('/', BP + '/')-2) ) = '90'
to get the result but the point is i don't want to use hard coded value, i have to use Field value. let say 2morrow value change. At least my query runs fine....
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/14/2013 :  22:07:56  Show Profile  Reply with Quote
Guys Want to give you update.
I got it

Here is the query is some one want to know...

Select
A.*,
B.*
From TableA A
Inner Join TableB B On A.ID = B.ID
Where SUBSTRING(BP,1,CHARINDEX('/',BP)-1)> B.BP1
AND LTRIM(RIGHT(BP, CHARINDEX('/', BP + '/')-2) ) > B.BP2

Please feel free to point out if something wrong.

Thank You.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 02/14/2013 :  22:15:51  Show Profile  Reply with Quote
you should store the BP in integer else when you are doing a string comparison and '90' will be consider greater than '100'

; with tblA as
(
	Select 	*, 
		BP1 = convert(int, left(BP, charindex('/', BP) - 1)),
		BP2 = convert(int, right(BP, len(BP) - charindex('/', BP)))
	from 	TableA
),
tblB as
(
        -- here i have convert it to integer so that the comparison make sense
	Select 	ID, City, BP1 = convert(int, BP1), BP2 = convert(int, BP2)
	from 	TableB
)
select	*
from	tblA a inner join tblB b	on	a.ID = b.ID
WHERE	a.BP1	<= 140
and	a.BP2	<= 90
and	b.BP1	<= 140
and	b.BP2	<= 90



KH
Time is always against us


Edited by - khtan on 02/14/2013 22:16:47
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/14/2013 :  22:21:39  Show Profile  Reply with Quote
Kh Thank you for your reply. Like i said i don't want to use hard coded value. I really want to use actual field. So someone change the data. at least my query runs fine...
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/15/2013 :  01:06:15  Show Profile  Reply with Quote
Guys just find out this query is giving right value for me
Select
A.*,
B.*
From TableA A
Inner Join TableB B On A.ID = B.ID
Where SUBSTRING(BP,1,CHARINDEX('/',BP)-1) = '140'
AND LTRIM(RIGHT(BP, CHARINDEX('/', BP + '/')-2) ) = '90'

Above query is giving me if one value match giving me record. How i bound it if both condition match then give me resule?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 02/17/2013 :  23:46:54  Show Profile  Reply with Quote
quote:
Originally posted by Sonu619

Kh Thank you for your reply. Like i said i don't want to use hard coded value. I really want to use actual field. So someone change the data. at least my query runs fine...



what hardcoded value are you referring to ? you can always change the 140 / 90 to a variable in the query


KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 02/17/2013 :  23:48:20  Show Profile  Reply with Quote
quote:
Originally posted by Sonu619

Guys just find out this query is giving right value for me
Select
A.*,
B.*
From TableA A
Inner Join TableB B On A.ID = B.ID
Where SUBSTRING(BP,1,CHARINDEX('/',BP)-1) = '140'
AND LTRIM(RIGHT(BP, CHARINDEX('/', BP + '/')-2) ) = '90'

Above query is giving me if one value match giving me record. How i bound it if both condition match then give me resule?



quote:
My Requirement IS If BP is <= 140/90.


Your query does not really matches your requirement stated in your earlier post.


KH
Time is always against us

Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/18/2013 :  00:03:52  Show Profile  Reply with Quote
Kh Thank you for your reply. I got the solution and its works fine. If you want i will provide the final query Here?

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000