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

From: <akchittanuri_at_gmail.com>
Date: 31 May 2006 01:49:20 -0700
Message-ID: <1149065360.836940.247350@f6g2000cwb.googlegroups.com>

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)

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 Received on Wed May 31 2006 - 03:49:20 CDT