|
View previous topic :: View next topic
|
| Author |
Message |
paritosh mathur
New User
Joined: 10 Apr 2006
Posts: 14
|
|
|
|
i have following database
| Code: |
id status eff-dt exp-dt
---------------------------------------------------
001 open 2008-05-01 _
002 open 2008-04-01 _
003 open 2008-03-01 _
004 closed 2008-03-01 2008-05-01
005 closed 2008-04-01 2008-07-01
|
my desired result should contain one open id which has minimum eff_dt and one closed id which has maximum expiry date.
like
| Code: |
id status eff-dt exp-dt
---------------------------------------------------
003 open 2008-03-01 _
005 closed 2008-04-01 2008-07-01
|
|
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
you've set-up unrealistic data. Require that solutions use this as column value definitions:
| Code: |
id status eff-dt exp-dt
---------------------------------------------------
001 open 2008-05-01 _
002 open 2008-04-01 _
003 OPEN 2008-03-01 _
004 open 2008-04-01 _
005 Open 2008-03-01 _
006 closed 2008-03-01 2008-05-01
007 CLOSED 2008-04-01 2008-07-01
008 closed 2008-03-01 2008-05-01
009 Closed 2008-02-01 2008-07-01
|
unfortunately, in the real world, multiple rows with the same value (which ends up being a min or max) exist.
also
keep in mind, using lower case data a column values means you either must stipulate only one or the other, or look for both. Believe me, even though your INSERTs may be coded as upper or lower, some idiot with DB2 File-Aid or Spufi, or QMF can make life really difficult.
The solution taking into account these values is something you want someone to provide. A query for your data would not work for mine, unless coded for multiple MINs, MAXs, and case values. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Plus, are the date fields of DATE Datatype, integer, or char? |
|
| Back to top |
|
|
paritosh mathur
New User
Joined: 10 Apr 2006
Posts: 14
|
|
|
|
hey dick i have shown that data as an example only. and date is in date format. even open and close cases are also defined while inserting data.
Anyway i have got the solution which is (as per my data)-
| Code: |
select * from table where
eff_dt in (
(select min(eff_dt) from table where status = 'open'),
(select eff_dt from table where exp_dt in (select max(exp_dt) from table where status = 'closed'))
);
|
thanx for ur suggestions. |
|
| Back to top |
|
|
|
|
