Jump to content

utspam

New Members
  • Content Count

    7
  • Joined

  • Last visited

Community Reputation

0 Neutral

About utspam

  • Rank
    Newbie

Profile Information

  • Gender
    Not Telling
  1. Absolutely! It takes about 11s to write 9839 rows to my browser from a little over 1M records. Much appreciated.
  2. 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. __________
  3. 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? __________
  4. 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
  5. 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. __________
×
×
  • 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.