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)
 Need a formula?

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 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

133 Posts

Posted - 2013-01-22 : 18:14:59
Anyone??
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.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')
Go to Top of Page

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 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.
Go to Top of Page

ssunny
Posting Yak Master

133 Posts

Posted - 2013-01-23 : 16:01:31
Help please!!
Go to Top of Page

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 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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -