IF ELSE statement - SQL Server Forums

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

Transact-SQL (2005)

IF ELSE statement

Author

Topic

Mir
Starting Member

19 Posts

Posted - 2009-05-25 : 06:05:59

Hi guys,

I have a 1 table with few columns that have a data like below
depth_from depth_to
0.00 - 2.00
2.00 - 4.00
2.00 - 3.00
3.00 - 4.00
4.00 - 6.00

How can i display result like below

depth_from depth_to
0.00 - 2.00
2.00 - 3.00
3.00 - 4.00
4.00 - 6.00

If u look closely, interval from 2.00 to 4.00 is not display because
interval 2.00 to 3.00 and 3.00 to 4.00 is already exist.
Can someone help, i tryt to use IF ELSE satement but got stuck..
please help ;)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 06:10:25

why not display 2.00 to 4.00 and skip the 2.00 to 3.00 and 3.00 to 4.00 ?

Do you have a primary key in the table ?

KH
[spoiler]Time is always against us[/spoiler]

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 06:16:18

Doing that is quite easy but do you also want to deal with

2, 5
2, 4
3, 5
2, 3
3, 4
4, 5

It is easier to split everything into 1 hour slots.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-05-25 : 06:22:18

0
--2
2
--3
--4
3
--4
4
--6
nested if?

Mir
Starting Member

19 Posts

Posted - 2009-05-25 : 07:39:40

Thanks for reply.. but can someone enlighten with the example queries? There is a primary key in the table which in column interval_id that start from P1 so on, and it look like below

interval_id depth_from depth_to
P1 0.00 - 2.00
P2 2.00 - 4.00
P3 2.00 - 3.00
P4 3.00 - 4.00
P5 4.00 - 6.00

How can i solve this? I dont want to display the interval 2.00 - 4.00 because interval
2.00 -3.00 and 3.00 - 4.00 is already exist. Please help :) Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-25 : 08:00:11

try this one and check it once


select depth_from,depth_to from 
(select t1.depth_from,t1.depth_to  ,count(*)  as cnt
from @tab t left join @tab t1 on  t.depth_from between t1.depth_from and t1.depth_to 
and t.depth_to between t1.depth_from and t1.depth_to
group by t1.depth_from,t1.depth_to)s
where cnt <2

Mir
Starting Member

19 Posts

Posted - 2009-05-25 : 08:14:07

Thanks bklr :) , the result of the query look like below

depth_from depth_to
0.00 2.00
2.00 3.00
3.00 4.00
4.00 6.00

Thanks so much for ur help ;) Just like what i want.
Sorry for my mistake in the first reply. I already edit this message.
Thanks mate.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-25 : 08:32:56

[code]
declare @tab table( depth_from decimal(18,2),depth_to decimal(18,2))
insert into @tab select
0.00 , 2.00 union all select
2.00 , 4.00 union all select
2.00 , 3.00 union all select
3.00 , 4.00 union all select
4.00 , 6.00

select depth_from,depth_to from
(select t1.depth_from,t1.depth_to ,count(*) as cnt
from @tab t left join @tab t1 on t.depth_from between t1.depth_from and t1.depth_to
and t.depth_to between t1.depth_from and t1.depth_to
group by t1.depth_from,t1.depth_to)s
where cnt <2

[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 08:40:44

You still have not define the rules of selecting 2.00 - 3.00 and 3.00 - 4.00 over 2.00 - 4.00.

quote:
I dont want to display the interval 2.00 - 4.00 because interval
2.00 -3.00 and 3.00 - 4.00 is already exist.

Why not the other way round ? ? Don't display 2.00 - 3.00 and 3.00 - 4.00 because interval 2.00 - 4.00 already exists.
What is the rules of selecting one instead of another ? You must have a rules to do that

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-25 : 08:46:59

Welcome

Mir
Starting Member

19 Posts

Posted - 2009-05-25 : 09:53:08

quote:
Originally posted by bklr

Welcome

Hi bklr,

I try to figure out how did u define the statement below:

on t.depth_from between t1.depth_from and t1.depth_to
and t.depth_to between t1.depth_from and t1.depth_to

can u explain a bit? Thanks, i just want to understand it clearly..

cheers mate

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 10:07:36

That query would cope with the cases you have there but what about if you added 4, 7 or added a 1, 2 without a 0, 1.

Don't know if that's possible but I think that wouldn't show all the ranges with that data.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Mir
Starting Member

19 Posts

Posted - 2009-11-12 : 04:33:54

Hi guys,

Almost few months i did'nt post anything.
There is a few changes that was made to the table and now it has a new primary key.
From the sample data below can someone help, how i can display the result as what i want? I got stuck! thanks

This is the raw data. Sample_number is the primary key and hole_number is the foreign key.
If u look closely in the Result, interval from 2.00 to 4.00 for hole number A1 is not display because interval 2.00 to 3.00 and 3.00 to 4.00 is already exist. Same it goes to hole number A2, interval 0.00 to 2.00 is not display because interval 0.00 to 1.00 and 1.00 to 2.00 is exist.

RAW DATA
hole_number depth_from depth_to sample_number sample_type
A1 0.00 2.00 P100 ASSAY
A1 2.00 4.00 P101 ASSAY
A1 2.00 3.00 P102 RES
A1 3.00 4.00 P103 RES
A1 4.00 6.00 P104 ASSAY
A2 0.00 2.00 P200 ASSAY
A2 0.00 1.00 P201 RES
A2 1.00 2.00 P202 RES
A2 2.00 4.00 P203 ASSAY
A2 4.00 6.00 P204 ASSAY

This is the result.
RESULT
hole_number depth_from depth_to sample_number sample_type
A1 0.00 2.00 P100 ASSAY
A1 2.00 3.00 P102 RES
A1 3.00 4.00 P103 RES
A1 4.00 6.00 P104 ASSAY
A2 0.00 1.00 P201 RES
A2 1.00 2.00 P202 RES
A2 2.00 4.00 P203 ASSAY
A2 4.00 6.00 P204 ASSAY

Please help! Really apreciate that. Thanks

Mir
Starting Member

19 Posts

Posted - 2009-11-13 : 23:29:24

Any help?

quote:
Originally posted by Mir

Hi guys,

Almost few months i did'nt post anything.
There is a few changes that was made to the table and now it has a new primary key.
From the sample data below can someone help, how i can display the result as what i want? I got stuck! thanks

This is the raw data. Sample_number is the primary key and hole_number is the foreign key.
If u look closely in the Result, interval from 2.00 to 4.00 for hole number A1 is not display because interval 2.00 to 3.00 and 3.00 to 4.00 is already exist. Same it goes to hole number A2, interval 0.00 to 2.00 is not display because interval 0.00 to 1.00 and 1.00 to 2.00 is exist.

RAW DATA
hole_number depth_from depth_to sample_number sample_type
A1 0.00 2.00 P100 ASSAY
A1 2.00 4.00 P101 ASSAY
A1 2.00 3.00 P102 RES
A1 3.00 4.00 P103 RES
A1 4.00 6.00 P104 ASSAY
A2 0.00 2.00 P200 ASSAY
A2 0.00 1.00 P201 RES
A2 1.00 2.00 P202 RES
A2 2.00 4.00 P203 ASSAY
A2 4.00 6.00 P204 ASSAY

This is the result.
RESULT
hole_number depth_from depth_to sample_number sample_type
A1 0.00 2.00 P100 ASSAY
A1 2.00 3.00 P102 RES
A1 3.00 4.00 P103 RES
A1 4.00 6.00 P104 ASSAY
A2 0.00 1.00 P201 RES
A2 1.00 2.00 P202 RES
A2 2.00 4.00 P203 ASSAY
A2 4.00 6.00 P204 ASSAY

Please help! Really apreciate that. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-14 : 00:00:03

quote:
Originally posted by Mir
If u look closely in the Result, interval from 2.00 to 4.00 for hole number A1 is not display because interval 2.00 to 3.00 and 3.00 to 4.00 is already exist. Same it goes to hole number A2, interval 0.00 to 2.00 is not display because interval 0.00 to 1.00 and 1.00 to 2.00 is exist.

What is the rule for this ?

What make you choose
A1 2.00 3.00 P102 RES
A1 3.00 4.00 P103 RES
over
A1 2.00 4.00 P101 ASSAY

KH
[spoiler]Time is always against us[/spoiler]

Mir
Starting Member

19 Posts

Posted - 2009-11-14 : 03:13:57

Thanks khtan.

The rule is i want to display only
2.00 to 3.00
3.00 to 4.00
with type RES because
2.00 to 4.00 with type Assay is no longger used.

quote:
Originally posted by khtan

quote:
Originally posted by Mir
If u look closely in the Result, interval from 2.00 to 4.00 for hole number A1 is not display because interval 2.00 to 3.00 and 3.00 to 4.00 is already exist. Same it goes to hole number A2, interval 0.00 to 2.00 is not display because interval 0.00 to 1.00 and 1.00 to 2.00 is exist.

What is the rule for this ?

What make you choose
A1 2.00 3.00 P102 RES
A1 3.00 4.00 P103 RES
over
A1 2.00 4.00 P101 ASSAY

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-14 : 05:12:23

so you mean RES will take precedence over ASSAY ?

KH
[spoiler]Time is always against us[/spoiler]

Mir
Starting Member

19 Posts

Posted - 2009-11-14 : 05:26:30

Yes :)

quote:
Originally posted by khtan

so you mean RES will take precedence over ASSAY ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-14 : 09:52:08

quote:
Originally posted by Mir

Yes :)
quote:
Originally posted by khtan

so you mean RES will take precedence over ASSAY ?

KH
[spoiler]Time is always against us[/spoiler]

I have ask that question back in May and finally got an answer 6 months later.

Any other business rules or scenario that you need to let us know ?

KH
[spoiler]Time is always against us[/spoiler]

Mir
Starting Member

19 Posts

Posted - 2009-11-14 : 20:00:38

Thanks for ur reply :)
I have ask that question back in May and finally got an answer 6 months later.
Sorry, i concentrate on the other matter on that time.

Any other business rules or scenario that you need to let us know ?
No , there is no other business rules.

quote:
Originally posted by khtan

quote:
Originally posted by Mir

Yes :)
quote:
Originally posted by khtan

so you mean RES will take precedence over ASSAY ?

KH
[spoiler]Time is always against us[/spoiler]

I have ask that question back in May and finally got an answer 6 months later.

Any other business rules or scenario that you need to let us know ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources