Re: SQL to detect when a record was entered

From: ddf <oratune_at_msn.com>
Date: Fri, 22 May 2009 06:17:53 -0700 (PDT)
Message-ID: <7ae4535a-266c-4e17-be53-d49ae3d7487c_at_r3g2000vbp.googlegroups.com>



On May 21, 1:38 pm, Alt255 <alt255.2..._at_gmail.com> wrote:
> On May 21, 10:17 am, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On May 21, 10:10 am, ddf <orat..._at_msn.com> wrote:
>
> > > On May 21, 8:17 am, Alt255 <alt255.2..._at_gmail.com> wrote:
>
> > > > Hello all, this is a puzzle thats been bugging me for sometime, and
> > > > now it looks like I *have* to build something.
>
> > > > I would like to know when a code number changed for an account in a
> > > > delta table.
> > > > The table contains the account number, several code numbers, a row
> > > > insert date and basically looks like this
> > > > DPLH_ROW_EFFCT_DTE DPLH_PLAN_NUM USERID_NAM DPLH_SIOB_CDE
> > > > DPLH_SLIQ_CDE
> > > > 4/15/2009          164328       LACSOA1       99              99
> > > > 4/30/2009          164328       LACSOA1       5               99
> > > > 5/19/2009          164328       LACSOA1       101             105
>
> > > > There are many other fields, so records could be added unrelated to
> > > > the code numbers I'm interested in. So my challenged is to identify
> > > > any DPLH_PLAN_NUMs that have had their DPLH_SIOB_CDE or SLIQ_CDE
> > > > changed. It seems I need to compare the last record to the second last
> > > > record. Is this even possible?
>
> > > This might give you a start:
>
> > > SQL> create table dplh(
> > >   2          dplh_row_effct_dte date,
> > >   3          dplh_plan_num   number,
> > >   4          userid_name     varchar2(10),
> > >   5          dplh_siob_cde number,
> > >   6          dplh_sliq_cde number
> > >   7  );
>
> > > Table created.
>
> > > SQL>
> > > SQL> insert all
> > >   2  into dplh
> > >   3  values(to_date('04/15/2009','MM/DD/RRRR'), 164328, 'LACSOA1', 99,
> > > 99)
> > >   4  into dplh
> > >   5  values(to_date('04/30/2009','MM/DD/RRRR'), 164328, 'LACSOA1', 5,
> > > 99)
> > >   6  into dplh
> > >   7  values(to_date('05/19/2009','MM/DD/RRRR'), 164328, 'LACSOA1',
> > > 101, 105)
> > >   8  select * From dual;
>
> > > 3 rows created.
>
> > > SQL>
> > > SQL> commit;
>
> > > Commit complete.
>
> > > SQL>
> > > SQL> select dplh_plan_num,
> > >   2         dplh_siob_cde,
> > >   3         lag(dplh_siob_cde) over (order by dplh_row_effct_dte,
> > > dplh_plan_num) prior_siob_cde,
> > >   4         dplh_sliq_cde,
> > >   5         lag(dplh_sliq_cde) over (order by dplh_row_effct_dte,
> > > dplh_plan_num) prior_sliq_cde
> > >   6  from dplh;
>
> > > DPLH_PLAN_NUM DPLH_SIOB_CDE PRIOR_SIOB_CDE DPLH_SLIQ_CDE
> > > PRIOR_SLIQ_CDE
> > > ------------- ------------- -------------- -------------
> > > --------------
> > >        164328            99                           99
> > >        164328             5             99            99
> > > 99
> > >        164328           101              5           105
> > > 99
>
> > > SQL>
>
> > > David Fitzjarrell- Hide quoted text -
>
> > > - Show quoted text -
>
> > Another possibility:
>
> > SQL> with cde_comp as (
> >   2  select dplh_plan_num,
> >   3         dplh_siob_cde,
> >   4         lag(dplh_siob_cde) over (order by dplh_row_effct_dte,
> > dplh_plan_num) prior_siob_cde,
> >   5         dplh_sliq_cde,
> >   6         lag(dplh_sliq_cde) over (order by dplh_row_effct_dte,
> > dplh_plan_num) prior_sliq_cde
> >   7  from dplh)
> >   8  select dplh_plan_num, dplh_siob_cde,
> >   9         case when prior_siob_cde <> dplh_siob_cde then
> > prior_siob_cde else null end prior_siob_cde,
> >  10         dplh_sliq_cde,
> >  11         case when prior_sliq_cde <> dplh_sliq_cde then
> > prior_sliq_cde else null end prior_sliq_cde
> >  12  from cde_comp;
>
> > DPLH_PLAN_NUM DPLH_SIOB_CDE PRIOR_SIOB_CDE DPLH_SLIQ_CDE
> > PRIOR_SLIQ_CDE
> > ------------- ------------- -------------- -------------
> > --------------
> >        164328            99                           99
> >        164328             5             99            99
> >        164328           101              5           105
> > 99
>
> > SQL>
>
> > Reports only changed values between records (notice the SIOB value
> > changed in the second record and the SLIQ value did not).
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> I ran the  SQL and something is still not right Here is one example
> from your SQL.
>
> DPLH_PLAN_NUM   DPLH_SIOB_CDE   PRIOR_SIOB_CDE
> 210935                          103     101
>
> I did some changes to the SQL to test only 1 field
> with cde_comp as (
>  select dplh_plan_num, dplh_siob_cde,  lag(dplh_siob_cde) over (order
> by dplh_row_effct_dte, dplh_plan_num) prior_siob_cde
>    from wh_dplh_all where dplh_row_effct_dte = '20-MAY-2009' )
>     select dplh_plan_num, dplh_siob_cde,
>  case when prior_siob_cde <> dplh_siob_cde then
> prior_siob_cde else null end prior_siob_cde
>    from cde_comp
>
> I added that the date of the current record must be May 20
> (Yesterday)
>
> DPLH_NUM        DPLH_ROW_EFFCT_DTE      DPLH_PLAN_NUM   DPLH_SIOB_CDE
> 812389                         7/28/2005
> 210935  2
> 3271626                        9/17/2006
> 210935  106
> 4960272                       12/17/2006
> 210935  103
> 6676619                       11/23/2007
> 210935  103
> 7794027                        5/20/2009
> 210935  103
> As you can see SIOB did not change, and also it never ever had a 101
> value (though its a valid code). I Compared all the fields from the
> May 20 to the November 23 and there was no difference. This means
> someone was reviewing the plan, made no changes, and closed the query.
>
> I also checked this one
>
> DPLH_PLAN_NUM   DPLH_SIOB_CDE   PRIOR_SIOB_CDE
> 219191                          101                     103
>
> An the detail history:
>
> DPLH_NUM        DPLH_ROW_EFFCT_DTE      DPLH_PLAN_NUM   DPLH_SIOB_CDE
> 878331                        7/28/2005
> 219191  4
> 3336823                       9/17/2006
> 219191  106
> 4518721                      11/2/2006
> 219191  101
> 6549221                       10/24/2007
> 219191  101
> 7794047                          5/20/2009
> 219191  101
>
> This the prior_siob is miss reporting the code number. Your code seems
> to make sense  I checked the previous record in the table to make sure
> it wasn't picking up that record, and that doesn't match- Hide quoted text -
>
> - Show quoted text -

My intent was to provide you a starting point, and apparently I have been successful in that attempt. Not having the exact table and data you have and not knowing what output you're wanting makes it very difficult to produce code which provides the desired outcome. It's your turn to play with the examples and modify them to meet your needs.

Of course if you choose to provide the ddl for the table, some sample data and a description of what, exactly, you want to see in this report a more specific example/solution can be offered.

David Fitzjarrell Received on Fri May 22 2009 - 08:17:53 CDT

Original text of this message