Re: SQL to detect when a record was entered

From: Alt255 <alt255.2005_at_gmail.com>
Date: Fri, 22 May 2009 07:57:59 -0700 (PDT)
Message-ID: <c508fd62-d411-4b51-94ed-b8144bb16f65_at_z5g2000yqn.googlegroups.com>



On May 22, 8:17 am, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Yes thanks David, I realised this after I posted, I'm in contact with my DB group to look at the speficis of the table. Thanks for the help Received on Fri May 22 2009 - 09:57:59 CDT

Original text of this message