| Need Help on a complex SQL to eliminate data [message #372884] |
Thu, 15 March 2001 11:07  |
Debi
Messages: 1
Registered: March 2001
|
Junior Member |
|
|
I have the following scenerio:
The Columns are:
=====>CustNo.-Type-Month-Amt
Row 1 = 1234 -- A --- 03 ---- 0
Row 2 = 1234 -- A --- 04 ---- 10
Row 3 = 1234 -- B --- 03 ---- 10
Row 4 = 1234 -- B --- 04 ---- 0
Row 5 = 5678 -- A --- 03 ---- 10
Row 6 = 5678 -- A --- 04 ---- 10
Row 7 = 5678 -- B --- 03 ---- 10
Row 8 = 5678 -- B --- 04 ---- 10
For Ex. the first record is:
CustNo = 1234
Type = A
Month = 03
Amt = 0
The key here is CustNo., Type and Month. I need to pull off from the table only records where for a CustNo, that there is atleast two months where the Amt is greater than zero for a Type. In the example above, CustNo. 1234 would not pass because Type A AND B both do NOT have 2 full months of Amt data.
How can this be done in an SQL select? Your input would be appreciated!
|
|
|
|
| Re: Need Help on a complex SQL to eliminate data [message #372887 is a reply to message #372884] |
Thu, 15 March 2001 12:00  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Maybe the easiest way is to start working on the subquery first.
create table t1 (custno number, typ varchar2(1), month number, amt number);
insert into t1 values (1234, 'A', 3, 0);
insert into t1 values (1234, 'A', 4, 10);
insert into t1 values (1234, 'B', 3, 10);
insert into t1 values (1234, 'B', 4, 0);
insert into t1 values (5678, 'A', 3, 10);
insert into t1 values (5678, 'A', 4, 10);
insert into t1 values (5678, 'B', 3, 10);
insert into t1 values (5678, 'B', 4, 10);
-- All for the idedtified cust
select * from t1 x
where x.custno in (select y.custno
from t1 y
where y.amt > 0
having count(y.month) > 1
group by y.custno,y.typ);
-- Only those types for a given cust where more
-- than 2 months of non-zero data exists
--(same in this test data)
select * from t1 x
where (x.custno, x.typ) in (select y.custno, y.typ
from t1 y
where y.amt > 0
having count(y.month) > 1
group by y.custno,y.typ);
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
