Re: SQL to detect when a record was entered

From: Alt255 <alt255.2005_at_gmail.com>
Date: Thu, 21 May 2009 11:38:33 -0700 (PDT)
Message-ID: <17f84cfd-c1d2-4864-a1c1-2b24924a2f4d_at_q16g2000yqg.googlegroups.com>



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 Received on Thu May 21 2009 - 13:38:33 CDT

Original text of this message