Re: SQL to detect when a record was entered
From: ddf <oratune_at_msn.com>
Date: Fri, 22 May 2009 06:17:53 -0700 (PDT)
Message-ID: <7ae4535a-266c-4e17-be53-d49ae3d7487c_at_r3g2000vbp.googlegroups.com>
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 -
Date: Fri, 22 May 2009 06:17:53 -0700 (PDT)
Message-ID: <7ae4535a-266c-4e17-be53-d49ae3d7487c_at_r3g2000vbp.googlegroups.com>
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 Received on Fri May 22 2009 - 08:17:53 CDT