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 |
|
leahsmart
Posting Yak Master
133 Posts |
 Posted - 2003-04-07 : 05:33:13
|
| Where to begin....I have a sproc that is an out of range report selecting all the scores that are basically out of their range. There are 3 fields that are used to determine whether a score is out of range they are, Worst, Best and ActualScore. I have used the between statement “WHERE ActualScore NOT BETWEEN Worst AND Best” but that does not work as the Worst score can be greater than the Best score. I am stuck, help.Thanks |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-04-07 : 06:29:42
|
Is this you are looking for  create table #test1 (actualscore int, best int, worst int)goinsert into #test1 select 4, 2, 9 -- best > actual < worstinsert into #test1 select 8, 3, 6 -- best < worst insert into #test1 select 9, 5, 2 -- best > worstinsert into #test1 select 4, 9, 2 -- best < actual > worst goselect * from #test1 where actualscore between best and worst or actualscore between worst and bestgodrop table #test1Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
 |
|
|
leahsmart
Posting Yak Master
133 Posts |
Posted - 2003-04-07 : 06:37:35
|
That does not seem to work. As I have results back which are within range. See below (can't get list to display probably) ActualScore Best Worst ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 0.0 100.0 0.00.0 100.0 0.059.0 100.0 0.091.0 100.0 0.00.0 0.0 2.00.0 0.0 1.015.0 20.0 0.03.5099999999999998 10.0 0.00.0 0.25 0.012.5 5.0 15.00.0 100.0 0.00.0 100.0 0.00.0 0.25 0.05.0 2.0 10.05.0 2.0 10.012.25 5.0 15.013.0 20.0 0.07.0 15.0 0.048.0 60.0 0.0100.0 100.0 0.0(20 row(s) affected) Edited by - leahsmart on 04/07/2003 06:56:54 |
|
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-04-07 : 06:58:46
|
| I have given the example for actual score falling between the other two values.. I belive changing to NOT BETWEEN will solve your problem. Can you Post some few sample data & the desired result Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
|
|
|
leahsmart
Posting Yak Master
133 Posts |
Posted - 2003-04-07 : 07:05:22
|
I have tried using not between but it does not work. Below is some example data. Example table|-----------------------|| Score | Best | Worst ||-----------------------|| 3 | 10 | 0 || 6 | 10 | 0 || 7 | 10 | 0 || 11 | 10 | 0 || 7 | 10 | 0 || 9 | 10 | 0 || 4 | 4 | 10 || 2 | 4 | 10 || 2 | 4 | 10 ||-----------------------|Out of range results from sproc|-----------------------|| Score | Best | Worst ||-----------------------|| 11 | 10 | 0 || 2 | 4 | 10 || 2 | 4 | 10 ||-----------------------| Edited by - leahsmart on 04/07/2003 07:06:14 |
|
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-04-07 : 08:30:38
|
| Try This..select * from (select 3 as score, 10 as best, 0 as Worstunion allselect 6, 10, 0union allselect 7, 10, 0union allselect 11, 10, 0union allselect 7, 10, 0 union allselect 9, 10, 0union allselect 4, 4, 10union allselect 2, 4, 10union allselect 2, 4, 10) as A where score not between worst and best and score not between best and worstSekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
|
|
|
leahsmart
Posting Yak Master
133 Posts |
Posted - 2003-04-07 : 08:56:35
|
| Thanks, that seems to work, dunno how though. Cheers!! |
|
|
|
|
|
|
|
|
Range report