Jump to content

Problem using Max and Count


utspam

Recommended Posts

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.

__________
 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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: AutoNumberwo_550085.jpg.930b836d550bb52988f75e0f04ed3962.jpg

 

Link to comment
Share on other sites

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?

__________

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

__________
 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.