Re: Help in SQL - How to find status of a category as of given date

akchittanuri_at_gmail.com wrote:
> Help in SQL - How to find status of a category as of given date
>
> Hi,
>
> Here are two tables.
>
> create table CATEGORY
> (
> CAT_CODE varchar2(100) not null,
> CAT_SEQ_ID number(11) not null,
> ACTV_IND char(1)
> );
>
> -- CATEGORY Table
> -- CAT_SEQ_ID is the primary key and is generated using a sequqnce.
> -- ACTV_IND indicates the current status of the category whether ACTIVE
> (Y) or DISABLED (N)
>
> -- Now there is a new requirement to audit the status changes to each
> category.
> -- Each Category can be either activated/disabled and the audit of this
> is stored in the table CAT_AUDIT.
>
> create table CAT_AUDIT
> (
> AUDIT_ID number(11) not null,
> AUDIT_DT_TM date not null,
> CAT_SEQ_ID number(11) not null,
> ACTV_IND char(1) not null
> );
>
> --CAT_AUDIT Table
> -- AUDIT_ID is primary key and is generated using a sequqnce.
> -- ACTV_IND will store the new status of the category.
>
>
> -- sample data
>
>
> Insert into CATEGORY
> (CAT_CODE, CAT_SEQ_ID, ACTV_IND)
> Values
> ('ABCD', 1000, 'Y');
>
> Insert into CATEGORY
> (CAT_CODE, CAT_SEQ_ID, ACTV_IND)
> Values
> ('BCDE', 1001, 'Y');
>
> Insert into CATEGORY
> (CAT_CODE, CAT_SEQ_ID, ACTV_IND)
> Values
> ('CDEF', 1002, 'Y');
>
> Insert into CATEGORY
> (CAT_CODE, CAT_SEQ_ID, ACTV_IND)
> Values
> ('DEFG', 1003, 'Y');
>
> commit;
>
>
> insert into cat_audit values(1, '01-jan-2006',1000,'N');
> insert into cat_audit values(2, '05-jan-2006',1001,'N');
> insert into cat_audit values(3, '10-jan-2006',1000,'Y');
> insert into cat_audit values(4, '15-jan-2006',1001,'Y');
> insert into cat_audit values(5, '20-jan-2006',1000,'N');
> insert into cat_audit values(6, '25-jan-2006',1001,'N');
> insert into cat_audit values(7, '31-jan-2006',1000,'Y');
> insert into cat_audit values(8, '05-feb-2006',1001,'Y');
> insert into cat_audit values(9, '10-feb-2006',1000,'N');
> insert into cat_audit values(10, '07-feb-2006',1001,'N');
> insert into cat_audit values(11, '09-feb-2006',1001,'Y');
> -- updating the CATEGORY table also to reflect the new status.
> update CATEGORY set actv_ind = 'N' where cat_seq_id = 1000;
>
> commit;
>
> --My goal is build a query which will return all the categories and
> their status as of given date.
>
> select category.cat_seq_id, cat_code,
> (case
> when cat_audit_new.actv_ind is null
> then category.actv_ind
> when :as_of_date >= audit_dt_tm
> then cat_audit_new.actv_ind
> end)
> actv_ind
> from category,
>
> (select audit_dt_tm, cat_seq_id, actv_ind from cat_audit where
> (audit_dt_tm, cat_seq_id) in
> (select max(audit_dt_tm), cat_seq_id from cat_audit where audit_dt_tm
> <= :as_of_date group by cat_seq_id)) cat_audit_new
> where category.cat_seq_id = cat_audit_new.cat_seq_id(+)
>
>
>
> --the query is giving proper results for 12-jan-2006 and 16-jan-2006
> and 17-may-2006
>
> results for as_of_date = 12-jan-2006
> -------------------------------------
> CAT_SEQ_ID CAT_CODE ACTV_IND
> -------------------------------------
> 1000 ABCD Y
> 1001 BCDE N
> 1002 CDEF Y
> 1003 DEFG Y
>
>
> results for as_of_date = 16-jan-2006
> -------------------------------------
> CAT_SEQ_ID CAT_CODE ACTV_IND
> -------------------------------------
> 1000 ABCD Y
> 1001 BCDE Y
> 1002 CDEF Y
> 1003 DEFG Y
>
> results for as_of_date = 17-may-2006
> -------------------------------------
> CAT_SEQ_ID CAT_CODE ACTV_IND
> -------------------------------------
> 1000 ABCD N
> 1001 BCDE Y
> 1002 CDEF Y
> 1003 DEFG Y
>
>
> results for as_of_date = 01-dec-2005 ( incorrect results)
> -------------------------------------
> CAT_SEQ_ID CAT_CODE ACTV_IND
> -------------------------------------
> 1000 ABCD N
> 1001 BCDE Y
> 1002 CDEF Y
> 1003 DEFG Y
>
>
> Questions)
> 1) How to modify the query to return correct results for the date
> 01-dec-2005??
>
> Status of Category 'ABCD' was updated to "N" on 01-jan-2006. So on
> 01-dec-2005 is the status should be 'Y' and not 'N' as shown in the
> resultset.
>
> 2) How to create a view on the CATEGORY and CAT_AUDIT tables so that I
> can join this view with other tables in my database and get the status
> of a category as of a given date.
>
>
> Thanks in advance..
> Anand

Go to Morgan's Library at www.psoug.org. Click on DBMS_FGA
Run the demo and then modify to perform what you need.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org