Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Help

Author  Topic 

Sonu619
Posting Yak Master

202 Posts

Posted - 2013-02-14 : 21:23:54
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

202 Posts

Posted - 2013-02-14 : 21:51:32
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

202 Posts

Posted - 2013-02-14 : 21:57:23
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

202 Posts

Posted - 2013-02-14 : 22:07:56
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)

17689 Posts

Posted - 2013-02-14 : 22:15:51
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Sonu619
Posting Yak Master

202 Posts

Posted - 2013-02-14 : 22:21:39
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

202 Posts

Posted - 2013-02-15 : 01:06:15
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)

17689 Posts

Posted - 2013-02-17 : 23:46:54
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-02-17 : 23:48:20
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Sonu619
Posting Yak Master

202 Posts

Posted - 2013-02-18 : 00:03:52
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
   

- Advertisement -