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.
Author |
Topic |
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-01-22 : 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 1declare @table1 table (t1id int,t2id int,t1value decimal (14,4),t1text varchar (300))insert into @table1values (1,3,5.0000,'Less than 5 lbs')insert into @table1values (2,3,6.0000,'Less than 6 lbs')insert into @table1values (3,3,4.0000,'Less than 4 lbs')insert into @table1values (4,3,2.0000,'Less than 2 lbs')insert into @table1values (5,2,6.0000,'Greater than 6 lbs')insert into @table1values (6,2,3.0000,'Greater than 3 lbs')select * from @table1-- table 2declare @table2 table (t2id int,t2description varchar (200))insert into @table2values (1,'equal to')insert into @table2values (2,'greater than')insert into @table2values (3,'less than')insert into @table2values (4,'between')select * from @table2-- table 3declare @table3 table (t3id int,t4categoryid int,t3value varchar (200))insert into @table3values (1,1,'3.35')select * from @table3-- table 4declare @table4 table (t4categoryid int, t4categoryunit varchar (100))insert into @table4values (1,'lb')select * from @table4go-- expected outputt3id t3value t1text1 '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 forfollowing 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
133 Posts |
Posted - 2013-01-22 : 18:14:59
|
Anyone?? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-23 : 01:42:17
|
can there be mutiple units for weights existing?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sqlbay
Starting Member
12 Posts |
Posted - 2013-01-23 : 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.t1textfrom @table3 ainner join @table1 bon abs(a.t3value-b.t1value)<1where exists (select 'a' from @table4 c where a.t4categoryid=c.t4categoryid and c.t4categoryunit='lb') |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-01-23 : 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 t1text1 '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. |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-01-23 : 16:01:31
|
Help please!! |
|
|
sqlbay
Starting Member
12 Posts |
Posted - 2013-01-24 : 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 t1textfrom @table3 ainner join @table1 bon round(a.t3value,0)<=b.t1valueinner join @table2 don b.t2id=d.t2idinner join @table4 eon a.t4categoryid=e.t4categoryidwhere 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.35or 'less than 3 lbs' (if u store that type of record in @table1) for 3.35SQL Server Professional http://sqlbay.blogspot.in |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-01-25 : 09:44:15
|
@sqlbay,Sorry for the late reply. This works perfectly and the way I wanted. Thank you for your help. Much appreciated. |
|
|
|
|
|
|
|