| Author |
 Topic  |
|
|
ssunny
Posting Yak Master
USA
130 Posts |
 Posted - 01/22/2013 : 14:16:12
|
Hello Friends,
I need a help with calculating weight range using some kind of formula.Here's my table structure and sample data:
-- table 1
declare @table1 table (t1id int,t2id int,t1value decimal (14,4),t1text varchar (300))
insert into @table1
values (1,3,5.0000,'Less than 5 lbs')
insert into @table1
values (2,3,6.0000,'Less than 6 lbs')
insert into @table1
values (3,3,4.0000,'Less than 4 lbs')
insert into @table1
values (4,3,2.0000,'Less than 2 lbs')
insert into @table1
values (5,2,6.0000,'Greater than 6 lbs')
insert into @table1
values (6,2,3.0000,'Greater than 3 lbs')
select * from @table1
-- table 2
declare @table2 table (t2id int,t2description varchar (200))
insert into @table2
values (1,'equal to')
insert into @table2
values (2,'greater than')
insert into @table2
values (3,'less than')
insert into @table2
values (4,'between')
select * from @table2
-- table 3
declare @table3 table (t3id int,t4categoryid int,t3value varchar (200))
insert into @table3
values (1,1,'3.35')
select * from @table3
-- table 4
declare @table4 table (t4categoryid int, t4categoryunit varchar (100))
insert into @table4
values (1,'lb')
select * from @table4
go
-- expected output
t3id t3value t1text
1 '3.35' 'Less than 4 lbs'
1 '3.35' 'Greater than 3 lbs'
From the sampler data, it's clear that only record in @table3 is stands for weight (lb) based on t4categoryid in @table4. Now weight (3.35) will qualify for
following values from @table1 :
'Less than 4 lbs'
'Greater than 3 lbs'
based on t2id relationship with @table2.
Note that there is no relationship from @table1 or @table2 with @table3 or @table4. I hope this is clear.
Thanks.
|
|
|
ssunny
Posting Yak Master
USA
130 Posts |
Posted - 01/22/2013 : 18:14:59
|
| Anyone?? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 01/23/2013 : 01:42:17
|
can there be mutiple units for weights existing?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sqlbay
Starting Member
12 Posts |
Posted - 01/23/2013 : 05:47:34
|
Hi,
/* if u have only one unit in @table4 and based on that if u want the output following query should work for u */
select a.t3id,a.t3value,b.t1text
from @table3 a
inner join @table1 b
on abs(a.t3value-b.t1value)<1
where exists (select 'a' from @table4 c where a.t4categoryid=c.t4categoryid and c.t4categoryunit='lb')
|
|
|
|
ssunny
Posting Yak Master
USA
130 Posts |
Posted - 01/23/2013 : 10:13:23
|
@Visakh,
There is only one id in @table4 for lb however @table3 can have multiple rows for t4categoryid = 1 (lb)
@sqlbay,
Your query works for what I have asked for but just now I realize that based on my criteria my expected output should be:
t3id t3value t1text
1 '3.35' 'Less than 6 lbs'
1 '3.35' 'Less than 5 lbs'
1 '3.35' 'Less than 4 lbs'
1 '3.35' 'Greater than 3 lbs'
Because weight 3.35 also qualified for 'Less than 5 lbs' and 'Less than 6 lbs'.What I need is to take t2id from @table2 into consideration and find out how many matches I get from @table1 based on that. Sorry about that. I will see if I can come up with something your query.
Thank you guys. |
Edited by - ssunny on 01/23/2013 14:23:34 |
|
|
|
ssunny
Posting Yak Master
USA
130 Posts |
Posted - 01/23/2013 : 16:01:31
|
| Help please!! |
|
|
|
sqlbay
Starting Member
12 Posts |
Posted - 01/24/2013 : 06:36:19
|
Hi,
/* Here for last column 't1text' instead of t1text column from @table1, it is fetched from @table2,@table1 and @table4
*/
select a.t3id,a.t3value,b.t1text,
d.t2description+' '+CONVERT(VARCHAR,CONVERT(INT,b.t1value))+' '+e.t4categoryunit+'s' as t1text
from @table3 a
inner join @table1 b
on round(a.t3value,0)<=b.t1value
inner join @table2 d
on b.t2id=d.t2id
inner join @table4 e
on a.t4categoryid=e.t4categoryid
where exists (select 'a' from @table4 c where a.t4categoryid=c.t4categoryid and c.t4categoryunit='lb')
and not exists (select 'a' from @table2 f where a.t3value<b.t1value and b.t2id=f.t2id and f.t2description='greater than')
and not exists (select 'a' from @table2 g where a.t3value>b.t1value and b.t2id=g.t2id and g.t2description='less than')
Not exists is used because we dont want to show 'greater than 6 lbs' for 3.35
or 'less than 3 lbs' (if u store that type of record in @table1) for 3.35
SQL Server Professional http://sqlbay.blogspot.in |
|
|
|
ssunny
Posting Yak Master
USA
130 Posts |
Posted - 01/25/2013 : 09:44:15
|
@sqlbay,
Sorry for the late reply. This works perfectly and the way I wanted. Thank you for your help. Much appreciated. |
|
|
| |
Topic |
|
|
|
Need a formula?
