Re: SQL to detect when a record was entered

From: ddf <oratune_at_msn.com>
Date: Thu, 21 May 2009 08:17:29 -0700 (PDT)
Message-ID: <3d040e7d-3c22-49b8-9fb1-b7603461e231_at_g20g2000vba.googlegroups.com>

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 Received on Thu May 21 2009 - 10:17:29 CDT