Re: SQL Question

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 8 Apr 1998 12:11:30 GMT
Message-ID: <6gfphi$u49$3@news01.btx.dtag.de>

Hi Matt,

I'd add FIND_ID to your VIEW_1 to have all distinguished defects in your view. WORK_STATION_ID allone would not be enough since some defects may occur twice in a few days. Assuming you add FIND_ID try:

SELECT

		TO_CHAR(SYSDATE-1, 'MM/DD/YYYY'), COUNT(FIND_ID) NUMBER_OF_DEFECTS
	FROM
		VIEW_1 v, TABLE_1 t
	WHERE
		v.FIND_ID=t.FIND_ID
	AND
		t.LOGDATE BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE-1) + 0.999999

UNION
SELECT

		TO_CHAR(SYSDATE-2, 'MM/DD/YYYY'), COUNT(FIND_ID) NUMBER_OF_DEFECTS
	FROM
		VIEW_1 v, TABLE_1 t
	WHERE
		v.FIND_ID=t.FIND_ID
	AND
		t.LOGDATE BETWEEN TRUNC(SYSDATE-2) AND TRUNC(SYSDATE-2) + 0.999999

... AND SO ON...
ORDER BY 1
;

Since LOG_DATE contains a time portion you need to select all dates that are between the beginning of the day and the end of the day.         

On Tue, 07 Apr 1998 17:06:37 GMT, nospam_itsystems_at_dial.pipex.com (Matt Griffiths) wrote:

>All,
>I am having problems creating a query/s and I am really in need of
>some assistance. I have 3 tables storing information about product
>defects. Each table has a PK uniquely identifying the defect, and the
>PK is the same in each table (FIND_ID). Without going into the details
>of why the tables are created so, (which is beyond my control) they
>can be summarised below. I will describe as best I can!!
>
>TABLE_1
>*************
>FIND_ID
>WORK_STATION_ID
>DEFECTIVE_SUBSYSTEM
>DEFECT_SYMPTOM_CODE
>PROBLEM_TYPE
>LOG_DATE
>
>TABLE_2
>************
>FIND_ID
>WORK_STATION_ID
>ACTION_TAKEN
>DEFECT_CAUSE
>
>TABLE_3
>************
>FIND_ID
>WORK_STATION_ID
>DEFECTIVE_PART
>DEFECTIVE_CONNECTOR
>
>I am dealing only with the last 70 days information at any one time,
>(about 400 records) and have created a view which holds distinct
>defects (not including FIND_ID and LOG_DATE which make them unique).
>The view holds about 100 records.
>
>VIEW_1
>************
>WORK_STATION_ID
>DEFECTIVE_SUBSYSTEM
>DEFECT_SYMPTOM_CODE
>PROBLEM_TYPE
>DEFECTIVE_PART
>DEFECTIVE_CONNECTOR
>DEFECT_CAUSE
>ACTION_TAKEN
>
>Ok. My problem is that I need to work out and record how many of those
>100 distinct defects occurred on EACH of the last 10 days (SYSDATE-1,
>SYSDATE-2 etc) using the LOD_DATE from TABLE_1.
>I need to either create a new view displaying this information,
>appended it to the existing view, or run it as a query or PL/SQL
>stored proc. I know that this is derived data and that 'officially' I
>shouldn't store it in the database, but I believe manipulating it on
>the client to calculate the figures would be extremely time consuming.
>
>All suggestions, criticisms, and comments are extremely welcome.
>
>Many thanks and Best Regards
>
>Matt Griffiths
>Matt.Griffiths_No_Spam__at_dial.pipex.com
>remove "_No_Spam_" to email directly
>
>***********************
>Using Oracle 7.3 for NT
>Client Development using VB5
>

--

Regards

Matthias Gresz :-)

GreMa_at_T-online.de Received on Wed Apr 08 1998 - 07:11:30 CDT