Using a view in SQL Server 2000
Table1:
id z1 z2 z3 z4 type
--------------------------------------
01A 300 400 300 400 01
2B 300 400 300 500 02
3C 700 600 400 300 01
04A 500 400 800 900 01
05B 400 300 400 300 02
06 150 200 200 150 03
....
Table2:
type value1 value2
------------------------------------
01 0 300
01 301 500
02 0 200
02 201 400
03
.....
I want to select the table1 rows based on table2 range:
Combination for table1- max(Z1, Z2) and max(Z3, Z4)
If Max(z1,z2) range is less than or equal to table2 max(value2) where table1.type = table2.type If Max(z1, z2) range is less than or equal to table2 max(value2) where table1.type = table2.type
If z1, Z2 range less than or equal to table2 then i want to display the z1, z2 rows otherwise null If z3, z4 range less than or equal to table2 then i want to display the z3, z4 rows otherwise null
Expected output
Table1:
id z1 z2 z3 z4 type
--------------------------------------
01A 300 400 300 400 01 ' `Both (z1, z2), (z3, z4) rows are matching with table2 for type 01`
2B 300 400 null null 02 ' (z1, z2) are matching, (z3, z4) rows are not matching with table2 for type 02
3C null null 400 300 01 ' (z1, z2) rows are not matching, (z3, z4) rows are matching with table2 for type 01
04A 500 400 null null 01 ' (z1, z2) rows are matching, (z3, z4) rows are not matching with table2 for type 01
05B 400 300 400 300 02 ' Both (z1, z2), (z3, z4) rows are matching with table2 for type 02
....
Currently I'm using a view, I don't want to change to stored procedure because most of the report are using this view.
How to do it in sql..?
This seems to give the expected results. (I'd have preferred to use a CTE, but you specified 2000).
Sample data:
declare @T1 table (Id varchar(3) not null,z1 int not null,z2 int not null,z3 int not null,z4 int not null,type char(2) not null)
insert into @T1 (id,z1,z2,z3,z4,type) values
('01A',300,400,300,400,'01'),
('2B',300,400,300,500,'02'),
('3C',700,600,400,300,'01'),
('04A',500,400,800,900,'01'),
('05B',400,300,400,300,'02'),
('06',150,200,200,150,'03')
declare @T2 table (type char(2) not null,value1 int not null,value2 int not null)
insert into @T2 (type,value1,value2) values
('01',0,300),
('01',301,500),
('02',0,200),
('02',201,400)
Query:
select
t1.Id,
CASE WHEN t2.type is not null then z1 END as z1,
CASE WHEN t2.type is not null then z2 END as z2,
CASE WHEN t3.type is not null then z3 END as z3,
CASE WHEN t3.type is not null then z4 END as z4,
t1.type
from
@T1 t1
left join
(select type,MAX(value2) as val2 from @T2 group by type) t2
on
t1.type = t2.type and
t1.z1 <= t2.val2 and
t1.z2 <= t2.val2
left join
(select type,MAX(value2) as val2 from @T2 group by type) t3
on
t1.type = t3.type and
t1.z3 <= t3.val2 and
t1.z4 <= t3.val2
where
t2.type is not null or
t3.type is not null
Result:
Id z1 z2 z3 z4 type
---- ----------- ----------- ----------- ----------- ----
01A 300 400 300 400 01
2B 300 400 NULL NULL 02
3C NULL NULL 400 300 01
04A 500 400 NULL NULL 01
05B 400 300 400 300 02
I'm not sure what the relevance of your question title was, the verbiage in the middle is quite confusing, and I made a guess at the final WHERE clause since the 06 row seems to disappear.