Hi,

I actually trying to build simple s/w bug tracking app.

Database:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production

CREATING TABLES
=============
CREATE TABLE IST_ISSUE_INFORMATION(
ISSUE_ID NUMBER PRIMARY KEY,
SUBJECT VARCHAR2(100),
PROJECT_NAME VARCHAR2(100),
MODULE VARCHAR2(100),
SEVERITY VARCHAR2(10),
PRIORITY VARCHAR2(10),
ISSUE_DATE TIMESTAMP,
ADDITIONAL_INFO_LINK VARCHAR(100),
COMMENTS VARCHAR2(100)
)

create table IST_ISSUE_TRACKING(
TRAKING_ID NUMBER,
ISSUE_DESC VARCHAR2(300),
BUILD_VERSION VARCHAR2(10),
START_DATE TIMESTAMP,
COMPLETION_DATE TIMESTAMP,
ASSIGNED_BY VARCHAR2(50),
RESOLVED_BY VARCHAR2(50),
CLOSED_COMMENTS VARCHAR2(100),
TOTAL_DAYS NUMBER,
TOTAL_HRS NUMBER,
TOTAL_MINS NUMBER,
ISSUE_ID NUMBER,
ISSUE_STATUS VARCHAR2(10)
CONSTRAINT FK_ISSUETRACKING
FOREIGN KEY (ISSUE_ID)
REFERENCES supplier(ISSUE_ID)
)

CREATE SEQUENCE
================


CREATE SEQUENCE IST_ISSUEINFO_SEQ INCREMENT BY 1 START WITH 11 MAXVALUE 99999999 MINVALUE 1 NOCACHE ;
CREATE SEQUENCE IST_TRACKING_SEQ INCREMENT BY 1 START WITH 11 MAXVALUE 99999999 MINVALUE 1 NOCACHE ;
(sequence is not using this sample data)

INSERT TEST DATA
================


INSERT INTO IST_ISSUE_INFORMATION VALUES(1,'TEST1','PROJ1','MODULE1','SEV1','PRO1',' 07-FEB-09 09.06.40.930263000 PM','','')
INSERT INTO IST_ISSUE_INFORMATION VALUES(2,'TEST2','PROJ2','MODULE2','SEV2','PRO2',' 07-FEB-09 09.06.40.930263000 PM','','')
INSERT INTO IST_ISSUE_INFORMATION VALUES(3,'TEST3','PROJ3','MODULE3','SEV3','PRO3',' 07-FEB-09 09.06.40.930263000 PM','','')

INSERT INTO IST_ISSUE_TRACKING VALUES(1,'TEST1 DESC','1.0','07-FEB-09 09.06.40.930263000 PM','08-FEB-09 09.06.40.930263000 PM','BBB','GGG','','','','',1,'1');
INSERT INTO IST_ISSUE_TRACKING VALUES(2,'TEST3 DESC','1.0','07-FEB-09 09.06.40.930263000 PM','08-FEB-09 09.06.40.930263000 PM','BBB','GGG','','','','',2,'2');

Epxected Report result
---------------------------

Monthly report
Day | total bugs | resolved bugs| pending bugs | Rejected bugs|
1 10 7 2 1

Quarterly Report
Q | total bugs | resolved bugs| pending bugs | Rejected bugs|

Yearly report
Month | total bugs | resolved bugs| pending bugs | Rejected bugs|

information in database could be for years.

appreciate your comments if you recommend any changes required to tables design.

Thank you,