PL/SQL query conversion? 2

[tbear6602]

Hi!
I'm a VERY junior DBA running 9i.

I need to create a query that loops through a table and based on a condition returns the record. My scenario is I need take an ID and it's date (starting Jan 1 of each year) then goes back through the table again and searches the prior 30 days for other incidents of the same ID. If the count of the ID is 3 or greater I need it to return the count, and the ID. Here is the current query I'm using:

SELECT
a.SYSTEMID,
trunc(a.FAILUREENDDATETIME) as "DAYENDING",
count(b.DAILYTOTAL) as "30DAYTOTAL"
FROM
OUTAGE a,
(
SELECT
trunc(a.FAILUREENDDATE) as "DAYENDING",
a.SYSTEMID,
count(a.SYSTEM) as "DAILYTOTAL"
FROM TROUBLEINSTANCETABLE a
WHERE a.RESPONSIBLEENTITY = 'CENTERID'
AND a.SERVICEAFFECTING = 1
AND a.ACTION <> 'Upgraded'
AND a.FAILUREENDDATE > to_date('DEC-01-' || (to_char(sysdate, 'YYYY')-1), 'MON-DD-YYYY')
GROUP BY trunc(a.FAILUREENDDATE),a.SYSTEMID
) b
WHERE
a.RESPONSIBLEENTITY = 'CENTERID'
AND a.ACTION <> 'Upgraded'
AND a.SERVICEAFFECTING = 1
AND trunc(b.DAYENDING) <= trunc(a.FAILUREENDDATETIME)
AND trunc(b.DAYENDING) >= (trunc(a.FAILUREENDDATETIME) - 30)
AND a.SYSTEMID = b.SYSTEMID
GROUP BY a.SYSTEMID, trunc(a.FAILUREENDDATETIME)

OUTAGE is a restricted view of the data in TROUBLEINSTANCETABLE.

RESPONSIBLEENTITY and SYSTEMID are VARCHAR,
FAILUREENDDATETIME and FAILUREENDDATE are date/time

However, 1)I cannot get the query to return the information based on the count >=3 and 2) the count it returns is incorrect about 50% of the time and I cannot figure it out.

Could someone please help?

 

[SantaMufasa]

TBear,

First, so that I don't miss anything important, your Subject for this thread is "PL/SQL query conversion?"...What does the subject have to do with your post? I don't see anything PL/SQL nor anyhing that looks like "conversion from" or "conversion to" anything.

Second, I don't see anything in your code that is checking for "count(*) >=3".

Third, since you didn't post any sample data to correspond to any sample output (which you didn't post either), I believe you would like us to make up our own data, create our own OUTAGE and TROUBLEINSTANCETABLE data and finally "desk execute" your logic.

I'm not aware of any gainfully employed Tek-Tipsters whose time allows that to happen. So, could you be kind enough to either a) post a much simpler scenario (preferrably along with CREATE TABLE, INSERT statements, a query you have tried, its output, and a description of what you don't like or understand about the code) from which you can gain the syntactical knowledge for which your are looking, or b) post the same components for your existing tables.

I'm sure others and I would be happy to help if you do some of the above leg work for us to make it more feasible to help you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[tbear6602]

Well, you have a vaild point. I kinda got my point lost in everything. I apologize. My question is really, do I need to convert this to PL/SQL to get the result set I want? I do not know PL/SQL at all, and neither does my DBA. (Our original one left with no notice and we were just dumped into the rolls with no training).

To your second question: I'm not sure where to insert it. I've never really had to deal with subqueries before, I tried to place it in my final where clause, but it will not function.


I am looking for something like the following:

TROUBLEINSTANCE TABLE DATA:
SYSTEMID RESPONSIBLEENTITY FAILUREENDDATE TICKETNUMBER
SYSTEM11 CENTERID 12/01/2004 111111
SYSTEM12 CENTERID 12/01/2004 111112
SYSTEM11 CENTERID 12/15/2004 111113
SYSTEM13 CENTERID 12/16/2004 111114
SYSTEM11 CENTERID 12/30/2004 111115
SYSTEM11 CENTERID 01/01/2005 111116

Returns
SYSTEMID FAILUREENDDATE 30DAYTOTAL
SYSTEM11 12/30/2004 3
SYSTEM11 01/01/2005 3

The reason they look the same is the 1st record should not be in the window of the 2nd incident, only the last 3.

I hope this makes sense.

I would post the loader statements for you, but I do not know how to create them.

 

[SantaMufasa]

The reason they look the same is the 1st record should not be in the window of the 2nd incident, only the last 3.

Your assertion is very ambiguous: Do you mean:

"the 1st record (of output, specifically "SYSTEM11...12/30/2004...3" ) should not be part of the result set (since "12/30/2004" is not in 2005 and you want to assess the past-30-day-failures record of only those failures that occurred in 2005)?"

If that is what you mean, then this code should give you want you want:

select b.systemid, b.failureenddate,count(*)
from (select systemid,failureenddate
        from troubleinstance) a,
     (select systemid,failureenddate
        from troubleinstance) b
where a.failureenddate between b.failureenddate-30 and b.failureenddate
      and a.systemid = b.systemid
having count(*) >= 3
   and to_char(b.failureenddate,'yyyy') = to_char(sysdate,'YYYY')
group by b.systemid, b.failureenddate;

SYSTEMID             FAILUREEN   COUNT(*)
-------------------- --------- ----------
SYSTEM11             01-JAN-05          3

Let us know if I have properly interpreted your meaning.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[tbear6602]

Actually, you're relatively close. Maybe I can clarify better:

Senario

System11 has an incident on December 15. I then need to go back 30 days from December 30 and count the number of incidents (tickets). The result is 3 ( December 1, 15, and 30). Then System11 has another incident on January 1. I need to go back 30 days from that incident and count again. The result will again be 3 (December 15, 30 and Jan 1).If there were to incidents on Jan 1 for System11, then the result would be 4, etc. as long as they are within the 30 day window.

I need to do this for all of my data for a year. I.E. incidents starting on Jan 1 for the rest of the year need to be able to go back 30 days and do a count of the number of tickets that are in the table.

Does this make more sense? I can post more sample data if you wish.

 

[Wholsea]

Just a quickie off the top of my head, give it a try...

Select count(*), systemID from
TROUBLEINSTANCE, (select max(FAILUREENDDATE), systemID from troubleinstance) latest
where latest.systemid = troubleinstance.systemid
and failureenddate >= latest.failureenddate-3
group by systemID



ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[Wholsea]

If you have a specific date time range you are looking for, use a where clause in the "in-line view" to limit the number of source records (the latest view)

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[SantaMufasa]

Yes, TBear, please just post what you want to see for output from your given data. That will explain everything for me.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[tbear6602]

Ok. Expanded my data set for clairification:

sample data:

SYSTEMID RESPONSIBLEENTITY FAILUREENDDATE TICKETNUMBER
SYSTEM11 CENTERID 12/01/2004 111111
SYSTEM12 CENTERID 12/01/2004 111112
SYSTEM11 CENTERID 12/15/2004 111113
SYSTEM13 CENTERID 12/16/2004 111114
SYSTEM11 CENTERID 12/30/2004 111115
SYSTEM11 CENTERID 01/01/2005 111116
SYSTEM15 CENTERID 01/12/2005 111117
SYSTEM13 CENTERID 01/12/2005 111118
SYSTEM11 CENTERID 01/16/2005 111119
SYSTEM11 CENTERID 01/16/2005 111120
SYSTEM13 CENTERID 01/20/2005 111121
SYSTEM11 CENTERID 01/22/2005 111122
SYSTEM12 CENTERID 01/22/2005 111123
SYSTEM13 CENTERID 01/22/2005 111124


DESIRED RESULTS:

SYSTEMID FAILUREENDDATE COUNT(*)

SYSTEM11 12/30/2004 3
SYSTEM11 01/01/2005 3
SYSTEM11 01/16/2005 4
SYSTEM13 01/16/2005 3
SYSTEM11 01/22/2005 5
SYSTEM13 01/22/2005 3

 

[Wholsea]

Given your logic of 30 days, one might expect to see the following results:

Expr1000 systemID failureenddate
3 SYSTEM11 12/30/2004
3 SYSTEM11 1/1/2005
6 SYSTEM11 1/16/2005
4 SYSTEM11 1/22/2005
3 SYSTEM13 1/22/2005

which was obtained with this query:

SELECT 
  Count(*) AS Expr1, 
  TROUBLEINSTANCE.SYSTEMID, 
  TROUBLEINSTANCE.FAILUREENDDATE
FROM 
 TROUBLEINSTANCE,
 (select distinct FAILUREENDDATE as enddate, systemID from troubleinstance ) AS latest
WHERE 
  latest.systemID=troubleinstance.systemid
 AND failureenddate-30<=latest.enddate
 AND TROUBLEINSTANCE.FAILUREENDDATE>= latest.enddate
GROUP BY TROUBLEINSTANCE.SYSTEMID, TROUBLEINSTANCE.FAILUREENDDATE
HAVING Count(*)>=3;

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[Wholsea]

Hang on, I have a cartesian issue... Almost there...



ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[Wholsea]

Okay, new results:
Expr1000 SYSTEMID enddate
3 SYSTEM11 12/30/2004
3 SYSTEM11 1/1/2005
4 SYSTEM11 1/16/2005
5 SYSTEM11 1/22/2005
3 SYSTEM13 1/22/2005

SELECT 
count(*),
 latest.SYSTEMID, 
 latest.enddate
FROM 
 TROUBLEINSTANCE,
 (select distinct FAILUREENDDATE as enddate, systemID from troubleinstance ) AS latest
WHERE 
  latest.systemID=troubleinstance.systemid
 AND enddate-30<= failureenddate
 AND TROUBLEINSTANCE.FAILUREENDDATe <= latest.enddate
GROUP BY latest.SYSTEMID,   enddate
HAVING Count(*)>=3;

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[tbear6602]

Thanks for all your help, SantaMufasa and Wholesea. Got it working finally. I guess that I am in SEVERE need of the further training I have scheduled next month....

Thanks & Kudos!