| Author |
 Topic  |
|
|
Sonu619
Posting Yak Master
193 Posts |
 Posted - 02/14/2013 : 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
193 Posts |
Posted - 02/14/2013 : 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? |
 |
|
|
Sonu619
Posting Yak Master
193 Posts |
Posted - 02/14/2013 : 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.... |
|
|
|
Sonu619
Posting Yak Master
193 Posts |
Posted - 02/14/2013 : 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. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 02/14/2013 : 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
Time is always against us
|
Edited by - khtan on 02/14/2013 22:16:47 |
|
|
|
Sonu619
Posting Yak Master
193 Posts |
Posted - 02/14/2013 : 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... |
|
|
|
Sonu619
Posting Yak Master
193 Posts |
Posted - 02/15/2013 : 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? |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 02/17/2013 : 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
Time is always against us
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 02/17/2013 : 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
Time is always against us
|
|
|
|
Sonu619
Posting Yak Master
193 Posts |
Posted - 02/18/2013 : 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?
|
|
|
| |
Topic |
|
|
|
Query Help
