Re: SQL to detect when a record was entered
From: Alt255 <alt255.2005_at_gmail.com>
Date: Fri, 22 May 2009 07:57:59 -0700 (PDT)
Message-ID: <c508fd62-d411-4b51-94ed-b8144bb16f65_at_z5g2000yqn.googlegroups.com>
On May 22, 8:17 am, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Date: Fri, 22 May 2009 07:57:59 -0700 (PDT)
Message-ID: <c508fd62-d411-4b51-94ed-b8144bb16f65_at_z5g2000yqn.googlegroups.com>
On May 22, 8:17 am, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Yes thanks David, I realised this after I posted, I'm in contact with my DB group to look at the speficis of the table. Thanks for the help Received on Fri May 22 2009 - 09:57:59 CDT