Re: SQL to detect when a record was entered

From: ddf <oratune_at_msn.com>
Date: Thu, 21 May 2009 08:10:02 -0700 (PDT)
Message-ID: <dc54ca72-a655-4ae4-9551-501f875395d3_at_h23g2000vbc.googlegroups.com>

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