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 -
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.
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