Re: Query challenge

Kenneth Li (qili_at_indiana.edu) wrote:
> Fields: ID Month DelCat
> where ID is customer identification number, DelCat is the customer's
> delinquency status with values of 10, 15, 20 or 50 in month specified by Month
> Fields: ID Ct10Mth Ct15Mth Ct20Mth Ct50Mth
> where ID is the customer id in Table 1, Ct10Mth is the *FIRST* month
> that customer was in DelCat 10; Ct15Mth is the *FIRST* month that customer
> was in DelCat 15, etc.

Since you do not have a date/time stamp field I am going to assume that you really do not have a correct way to find out the *FIRST* record. I am going to consider *FIRST* as *ONE*.

select t1.id, t1_10.month, t1_15.month, t1_20.month, t1_50.month from table_1 t1_10, table_1 t1_15, table_1 t1_20, table_1 t1_50, table_1 t1 where

       t1_10.rowid = ( select  min(rowid)
                       from    table1 t_10a
                       where   t_10a.id    = t1.id and 
                               t_10.delcat = 10
                     ) and 
       t1_15.rowid = ( select  min(rowid)
                       from    table1 t_15a
                       where   t_15a.id    = t1.id and 
                               t_15.delcat = 15
                     ) and 
       .....

--
**************************************************************
*                          Saad Ahmad                        *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************