How To question... not sure what to ask

[neronikita]

Hi, all. Please bear with me as I explain myself.

Can this be done:

I have a ticket database. Say Ticket1 has 50 tickets related to that ticket number. Then there may be additional tickets related to those tickets, which may or may not be related to Ticket1. Ultimately, I need a count of all distinct ticket numbers, but first I want to be sure I'm pulling them right.

So, I did

SELECT *
FROM ticketdatabase
WHERE relatedticket.linked = 'ticket1'

Now there is also a source field that I can use in place of Linked that pulled the same info. My problem is, it only pulls anything directly related to Ticket1. so if Ticket2 is related to Ticket1 it will count it, but if Ticket2 has 10 tickets related to it, it will NOT count those. To make it more confusing, in my trials, I used the problem tickets, but there could be related call tickets as well (problem - problem, problem-call, or call-call). What I need is to keep compiling lists of tickets related in anyway until I get a master list, then eliminate duplicates to get an accurate count.

For example:

Ticket1 =50
ticket2 =4
ticket3 =5
ticket4 =5
ticket5 =10
which makes it 74 distinct tickets total.

I hope I haven't made it too confusing. Please let me know if you need more information. Also, if there is a "name" for what I'm trying to do, that would be helpful too so I know what to look up.

Thanks!

 

[PHV]

Any chance you could post your actual table(s) structure and relationships ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[neronikita]

Thanks for responding.

OK, here is the actual base query:

SELECT *
FROM PG#rpt.SCRELATION
where PG#rpt.SCRELATION.depend = 'p0479404'

Here are the field names:

SOURCE_NME
SOURCE_FILENAME
DEPEND
DEPEND_FILENAME
TYPE
SOURCE_ACTIVE
DEPEND_ACTIVE
SYSMODCOUNT
SYSMODUSER
SYSMODTIME
DESC
WCH_OPEN_DTM


To make it easier to read, I'll give you just the list of ticket numbers(source_nme) and "type"(source_filename) generated for the source_nme field. Depend is all the original ticket number, and the rest of the info is really irrelevant because the end result for this will be a count.

ct4938972 incidents
ct4938985 incidents
ct4939105 incidents
ct4939115 incidents
ct4939117 incidents
ct4939204 incidents
ct4939209 incidents
ct4939312 incidents
ct4939318 incidents
ct4939337 incidents
ct4939341 incidents
ct4939394 incidents
ct4939447 incidents
ct4939626 incidents
ct4939659 incidents
ct4939661 incidents
ct4939694 incidents
ct4939724 incidents
ct4939739 incidents
ct4939749 incidents
ct4939761 incidents
ct4939865 incidents
ct4939874 incidents
ct4939908 incidents
ct4939914 incidents
ct4939916 incidents
ct4939928 incidents
ct4939944 incidents
ct4939945 incidents
ct4939959 incidents
ct4939986 incidents
ct4940075 incidents
ct4940187 incidents
ct4940211 incidents
ct4940236 incidents
ct4940264 incidents
ct4940348 incidents
ct4940372 incidents
ct4940509 incidents
ct4940527 incidents
ct4940546 incidents
ct4940577 incidents
ct4940678 incidents
ct4940769 incidents
ct4940809 incidents
ct4941046 incidents
ct4941334 incidents
ct4942146 incidents
ct4942368 incidents
ct4945124 incidents
p0479426 problem
p0479560 problem
p0479601 problem

Technically, there aren't any other tables used. Incidents has a incident_id and a problem_id, and probsummary has a number field, but screlation (used above) is used for all relationships. What I need is after I get the results above, to have the query automatically look to see if any of those ticket numbers have tickets attached, and then check those, and so on until I've generated a complete list, at which point I need a distinct count. Am I asking too much of SQL? Am I confusing anyone yet?

Does this help?

Thanks,

 

[neronikita]

here are two possibilities that are very close:

This gives me a summary of all master tickets, plus a count of how many are linked to each. It provides the correct tickets, however, it also includes a count of the "master" master (p0479404) for a total of 96. The total should be around 85 or 87, so we are close. Problem is, it doesn't list the tickets with each master.

Select
kk.dsource, count(kk.dsource)
from
(SELECT
distinct dd.source_nme dSource, dd.Source_filename dType, dd.Depend dDepend
FROM
pg#rpt.SCRELATION dd,
(SELECT bb.SOURCE_NME, bb.SOURCE_FILENAME, bb.DEPEND
FROM pg#rpt.SCRELATION bb,
(SELECT SOURCE_NME, SOURCE_FILENAME, DEPEND
FROM pg#rpt.SCRELATION aa
WHERE aa.depend = 'p0479404')cc
WHERE bb.depend = cc.SOURCE_NME)ee
where
dd.depend = ee.source_nme) kk
Group by kk.dsource
having count(kk.dsource) > 1

Union All

Select
kk.ddepend, count(kk.ddepend)
from
(SELECT
distinct dd.source_nme dSource, dd.Source_filename dType, dd.Depend dDepend
FROM
pg#rpt.SCRELATION dd,
(SELECT bb.SOURCE_NME, bb.SOURCE_FILENAME, bb.DEPEND
FROM pg#rpt.SCRELATION bb,
(SELECT SOURCE_NME, SOURCE_FILENAME, DEPEND
FROM pg#rpt.SCRELATION aa
WHERE aa.depend = 'p0479404')cc
WHERE bb.depend = cc.SOURCE_NME)ee
where
dd.depend = ee.source_nme) kk
Group by kk.ddepend
having count(kk.ddepend) > 1



This query is close - it gives a list of all tickets, but there are some duplicates, as it gives a total of 92.

SELECT dd.source_nme dSource, dd.Source_filename dType, dd.Depend dDepend
FROM pg#rpt.SCRELATION dd, (SELECT bb.SOURCE_NME, bb.SOURCE_FILENAME, bb.DEPEND
FROM pg#rpt.SCRELATION bb, (SELECT SOURCE_NME, SOURCE_FILENAME, DEPEND
FROM pg#rpt.SCRELATION aa
WHERE aa.depend = 'p0479404')cc
WHERE bb.depend = cc.SOURCE_NME)ee
where dd.depend = ee.source_nme

Can anyone help me find the happy medium?

Let me know if you need to see the results from either of these.