Originally posted by Marilyn de Queiroz:
Oh, I just had a thought, what if I compare each row's ticket number to the next row's ticket number. If they're different, the current row must be the last row for that ticket.
Originally posted by Michael Matola:
I need a table consisting of only three columns:
entry (if entry_type is 'ADD NOTE', get the most recent entry),
count of rows where entry_type is 'Call Attached'
OK, but a table? I thought you were just writing a query here? Are you working with temporary tables? Shuttling some data around?
Ok, not a table, exactly. When I run the query and get the results in a ResultSet, I (actually the code I'm supposed to use) just turn around and write to a csv file (with no other manipulation), but that file looks just like a table with headers, rows and columns. So I'm thinking in terms of writing from one table to another. Not too much of a stretch. I could override that method if I need to and make it have two queries, but I'd rather not if I don't have to.
You say "if entry_type is 'ADD NOTE', get the most recent entry". What to do if entry_type is not 'ADD NOTE'? This is really important to know.
If entry_type is not 'ADD NOTE' and entry_type is not "Call Attached", we can ignore that record. If it is "Call Attached", we count it. If it is 'ADD NOTE', we care only if it is the most recent. If there is no 'ADD NOTE' entry_type for that problem_id, the result is null. Clear as mud?
These 14 rows need to be converted to one row with three columns which would look something like this:
Converted. So if you're writing some conversion process is it really necessary to do all this with a single SQL statement?
Sorry, bad choice of words. The code in the class I'm using only makes one sql query and writes the file based on that query. I'll think about how easy it would be to change that.
I'm not sure what you mean by "keys".
Which field(s) in the table have been set up to uniquely identify a row. It's my assumption based on the table structure and data that you list that the primary key is the combination of three fields: problem_id, entry_date, and entry_time.
I believe that's true although I have been unable to get my describe statement to work on this particular table (appears to be a permissions problem). There is also a column titled probhist_id which is assigned which could also be the primary key. It's a number which means nothing to me.
entry (if entry_type is 'ADD NOTE', get the most recent entry)
but elsewhere you've said
the first 500 chars from most recent ENTRY of the current PROBLEM_ID where ENTRY_TYPE='ADD NOTE'
Do you mean for each problem_id, find all rows where entry_type = 'ADD NOTE', then find the most recent ENTRY from among that subset?
[B][I]In other words, what to do if this 15th row of data is the absolute most recent:
Just add it to the total number of Call_Attached_Totals for this problem_id.
[B][I]Anyhow, here's something a lot closer given the new info. But I didn't know what to do about the entry_type = 'ADD NOTE', so for now I didn't do anything.
(If the date/time was a single field, we would only have to take the max on that single field, instead of what I've done here: first taking the max date at (A), then taking the max time at (B) for the already-determined max date at (C).) [/I][/B]
Thank you so much, Mike, I'll try this first thing in the morning.
[ June 06, 2006: Message edited by: Marilyn de Queiroz ]
Originally posted by Michael Matola:
This query works (sort of)
...from pfxtsd.problems p
Where'd the table "problems" come from? What's the story on that?
I need the join on the problems table to get the "first_contact_id" and the "close_date" and the "open_date" because this table is shared by clients other than FTT and open_date and close_date are limiting dates for the problem_id's.
LFMDate = LastFullMonth
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koophttps://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton