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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need a formula?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssunny
Posting Yak Master

USA
133 Posts

Posted - 01/22/2013 :  14:16:12  Show Profile  Reply with Quote
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
133 Posts

Posted - 01/22/2013 :  18:14:59  Show Profile  Reply with Quote
Anyone??
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/23/2013 :  01:42:17  Show Profile  Reply with Quote
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 - 01/23/2013 :  05:47:34  Show Profile  Reply with Quote
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

USA
133 Posts

Posted - 01/23/2013 :  10:13:23  Show Profile  Reply with Quote
@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
Go to Top of Page

ssunny
Posting Yak Master

USA
133 Posts

Posted - 01/23/2013 :  16:01:31  Show Profile  Reply with Quote
Help please!!
Go to Top of Page

sqlbay
Starting Member

12 Posts

Posted - 01/24/2013 :  06:36:19  Show Profile  Reply with Quote
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

USA
133 Posts

Posted - 01/25/2013 :  09:44:15  Show Profile  Reply with Quote
@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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000