Re: SQL Question

Matt,

To solve your problem, I would do the following:

• Include FIND_ID and TRUNC(LOG_DATE) to VIEW_1. Using the trunc function will remove any references to times. Call this new column LOG_DATE.
• Create a new view thus: CREATE VIEW VIEW_2 AS SELECT LOG_DATE, COUNT(*) FROM VIEW_2 WHERE LOG_DATE > trunc(SYSDATE - 10);

This will give you a list of how many logs were made in the last 10 days.

Alternatively, you could leave out the where clause and get a list of logs made for each day. If you only want the last ten days then add the above where clause to your select statement.

I hope this help you out.

Cheers,
Ivan

(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
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Apr 09 1998 - 01:49:47 CDT