Problem using Max and Count

[utspam]

Hello,

I'm working on a report from a service documentation database application with SQL backend.  There is a table for work orders and a separate table for the work done related by wo_number.  I need to report the last entry in the work done table only if it is a certain code.

work done table=wct.*.  Fields: wo_number, sequence, response, wct_id, plus more fields

I've tried many variations of my sql statement but run into > 60 second execution time.  There are just over 1 million entries.

Goal: List only work orders where the last entry(MAX sequence) has a response="PMU".  (Note: PMU entry can be made multiple times on different dates.  I'm looking for only work orders where the last entry was PMU.  Other codes used after a PMU entry should exclude the work order from the report.)

My best attempt is:

SELECT wo_number, response, MAX(sequence) AS maxseq, COUNT (wo_number) AS maxcnt FROM wct WHERE wo_number > 550000 AND response='PMU' GROUP BY wo_number, response ORDER BY wo_number. 

On one particular work order, this gives me maxseq = 22 and maxcnt = 18.  I was expecting maxseq = 18 (as max number of PMU entries) and maxcnt = 26 (as total entries) based on the data in the actual table.  I see that is because of the 'response='PMU' in the WHERE clause, but don't know how to fix it.

I am not a programmer.  Any help is appreciated.

__________
 

[requinix]

Please post your table structures and an example of the data you're working with, including the data you and do not want to include in the query.

[Barand]

Data

TABLE: work_order                  TABLE: wct;
+-----------+--------------+       +-----------+----------+----------+--------+
| wo_number | wo_desc      |       | wo_number | sequence | response | wct_id |
+-----------+--------------+       +-----------+----------+----------+--------+
|         1 | work order 1 |       |         1 |        1 | ABC      |      1 |
|         2 | work order 2 |       |         1 |        2 | DEF      |      2 |
|         3 | work order 3 |       |         1 |        3 | PMU      |      3 |
|         4 | work order 4 |       |         1 |        4 | XYZ      |      4 |
|         5 | work order 5 |       |         1 |        5 | DEF      |      5 |    <--
+-----------+--------------+       |         2 |        1 | XXX      |      6 |
                                   |         2 |        2 | PMU      |      7 |    <-- ***
                                   |         3 |        1 | ZZZ      |      8 |
                                   |         3 |        2 | YYY      |      9 |
                                   |         3 |        3 | ASE      |     10 |
                                   |         3 |        4 | PMU      |     11 |    <-- ***
                                   |         4 |        1 | PPP      |     12 |
                                   |         4 |        2 | JJJ      |     13 |
                                   |         4 |        3 | PMU      |     14 |
                                   |         4 |        4 | NNN      |     15 |    <--
                                   |         5 |        1 | AAA      |     16 |
                                   |         5 |        2 | BBB      |     17 |
                                   |         5 |        3 | CCC      |     18 |
                                   |         5 |        4 | DDD      |     19 |    <--
                                   +-----------+----------+----------+--------+

try something like

SELECT w.wo_number
     , wo_desc
     , latest.sequence
     , wct.response
FROM work_order w
	 JOIN
	 wct ON w.wo_number = wct.wo_number
            AND wct.response = 'PMU'
	 JOIN 
		 (
			SELECT wo_number
				 , MAX(sequence) as sequence
			FROM wct 
			GROUP BY wo_number
		 ) latest ON wct.wo_number = latest.wo_number
					 AND wct.sequence = latest.sequence
                     
+-----------+--------------+----------+----------+
| wo_number | wo_desc      | sequence | response |
+-----------+--------------+----------+----------+
|         2 | work order 2 |        2 | PMU      |
|         3 | work order 3 |        4 | PMU      |
+-----------+--------------+----------+----------+

 

[utspam]

I need to report wo_numbers where the last 'response' entry was PMU.  The entries may not have been made in done_datetime order so I need the last entry by sequence.  The majority of work orders will not have a PMU entry at all.

My thought was to find all wo_numbers with a PMU entry.  Get the highest number in sequence column for PMU entry (maxseq).  If a count of the entries for that wo_number (maxcnt) equaled the (maxseq), report that wo_number.  Otherwise, advance to the next wo_number.  Maybe I'm overthinking this.  There are a little over 1M entries and some of my test queries have exceeded 60S.

Query: SELECT wo_number, response, MAX(sequence) AS maxseq, COUNT (wo_number) AS maxcnt FROM wct WHERE wo_number > 550000 AND response='PMU' GROUP BY wo_number, response ORDER BY wo_number

Produces 550085 : PMU : 22 for the data below.  There are 18 entries for PMU.  The last entry is sequence 22.  This is not a wo_number that I would report.  If sequence 26 had a PMU code, I would want that reported.  I think I need the WHERE clause to compare maxseq = maxcnt.

Hope this makes sense and thanks for helping.

_________

wct structure

wo_number: LongInt, KeyField

sequence: LongInt, KeyField

employee: ShortText

response: ShortText

done_datetime: Date/Time

wct_id: AutoNumber

 

[utspam]

Barand, I'm really trying to understand your reply but I am lost on the

SELECT w.wo_number , wo_desc , latest.sequence , wct.response

as that field is not part of the wo table.  Frankly, I wasn't necessarily concerned with linking the wo table.  I appreciate your effort but you are way over my head.  Is there a way to drop the wo table reference and still come up with a 'latest' for comparison?

What exactly is 'latest ' doing with the relation at the end?

__________

[Barand]

This part of the query ...

         (
            SELECT wo_number
                 , MAX(sequence) as sequence
            FROM wct 
            GROUP BY wo_number
         ) latest

is a table subquery with alias "latest". It is essentially a dynamic temporary table called "latest" containing the latest sequence number for each wo_number .

We then join the wct table to this temporary table on (wo_number, sequence) to find the matching wct record and, in particular, its response value. It just selects those that match that latest sequence and response id PMU.

The column "latest.sequence" is just there so I could check it was pulling the expected values.

 

These next two statements of yours seem contradictory:

4 hours ago, utspam said:

Goal: List only work orders where

14 minutes ago, utspam said:

Frankly, I wasn't necessarily concerned with linking the wo table

 

[utspam]

Understood and it is working great now.  Thanks for the education.

The contradictory statements you referred to: The goal was to list the work order numbers, the employee, and date.  This list would be available on a dashboard for quick reference.  The work order number would be hyperlinked to the work order page for more details.  This works perfectly and now I understand more about JOIN and the alias subquery.

Thank you so much.

__________
 

[Barand]

Purely out of curiosity, is it running in less than 60 seconds?

[utspam]

Absolutely! It takes about 11s to write 9839 rows to my browser from a little over 1M records. 

Much appreciated.

[gizmola]

Did you try an explain on the underlying query?  It might be good to see if an additional index or 2 might help with performance.