Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can I select the minimum time in complicated query?

Status
Not open for further replies.
capronton

capronton

MIS
Jun 28, 2013
159
US
In select expert query below I only want the minimum "time" record.

{day_type_vs.description} = {?ServiceType} and
{incident_log.incident_log_id} Mod 2 = 0 and
({timepoint_name.tp_lname} in {?TP_ID} or " ALL" in {?TP_ID}) and
{incident_log.current_route_id} = {?CurrentRoute} and
{incident_log.incident_date_time} = {?IncidentDate} and
Minimum ({incident_log.incident_date_time})

Example of records below, where I want to pull in only the minumum time record and filter out the 2nd record listed below. Any ideas how I might select only the minimum time in the query above?

111134646 9/21/15 13:00:43 13:00 5002 600268 OnTime WKDAY 801001 801 801 0 SOUTHBOU 19 11556 7321 ST EDWARDS
111135740 9/21/15 13:04:43 13:00 5002 600268 OnTime WKDAY 801001 801 801 0 SOUTHBOU 19 11556 7321 ST EDWARDS

Thanks for any assistance.
 
Sort by date Sort by votes
I wanted to provide a little bit more information. Basically, whenever column 4 (schTime) duplicates itself, I want to filter out the row and only pull in the row with with minimum (incTime) column 3.
Please see records in bold font column 4 for duplicate "schTimes"

incLogID tDate incTime schTime bus rsaStatus tpAdjustment incName service cRte deviation direction seq timepoint lat long
111134646 9/21/15 13:00:43 13:00 5002 OnTime Early BUNCH-N WKDAY 801 0 SOUTHBOU 19 ST EDWARDS 30.2289 -97.7608
111135740 9/21/15 13:04:43 13:00 5002 OnTime OnTime NORMAL-N WKDAY 801 0 SOUTHBOU 19 ST EDWARDS 30.2289 -97.7608
111136060 9/21/15 13:05:43 13:05 5002 OnTime OnTime NORMAL-N WKDAY 801 0 SOUTHBOU 20 SOUTH CONGRESS STATION BAY K 30.2227 -97.7665
111136898 9/21/15 13:08:43 13:10 5002 Early Early BUNCH-E WKDAY 801 2 SOUTHBOU 21 LITTLE TEXAS 30.2034 -97.7754
111137494 9/21/15 13:10:43 13:15 5002 Early Early BUNCH-E WKDAY 801 5 SOUTHBOU 22 PLEASANT HILL 30.1942 -97.7785
111143904 9/21/15 13:31:43 13:30 5002 OnTime Early BUNCH-N WKDAY 801 -1 NORTHBOU 1 SOUTH PARK MEADOWS 30.1629 -97.7903
111146734 9/21/15 13:40:44 13:37 5002 OnTime Early BUNCH-N WKDAY 801 -3 NORTHBOU 2 PLEASANT HILL 30.1934 -97.7773
111147340 9/21/15 13:42:44 13:40 5002 OnTime Early BUNCH-N WKDAY 801 -1 NORTHBOU 3 LITTLE TEXAS 30.2021 -97.7755
111149460 9/21/15 13:49:44 13:47 5002 OnTime Early BUNCH-N WKDAY 801 -1 NORTHBOU 4 SOUTH CONGRESS STATION BAY J 30.2229 -97.7663
111150384 9/21/15 13:52:44 13:52 5002 OnTime Early BUNCH-N WKDAY 801 0 NORTHBOU 5 ST EDWARDS 30.2306 -97.7595
111151264 9/21/15 13:55:44 13:55 5002 OnTime Early BUNCH-N WKDAY 801 0 NORTHBOU 6 OLTORF STATION 30.2396 -97.7528
111151622 9/21/15 13:56:44 13:58 5002 Early Early BUNCH-E WKDAY 801 2 NORTHBOU 7 SOCO 30.2488 -97.7498
111153160 9/21/15 14:01:44 14:02 5002 Early Early BUNCH-E WKDAY 801 1 NORTHBOU 8 AUDITORIUM SHORES 30.2583 -97.7479
111154346 9/21/15 14:05:44 14:05 5002 OnTime Early BUNCH-N WKDAY 801 0 NORTHBOU 9 REPUBLIC SQUARE 30.2662 -97.7461
111154902 9/21/15 14:07:44 14:08 5002 Early Early BUNCH-E WKDAY 801 1 NORTHBOU 10 AUSTIN HISTORY CENTER 30.2704 -97.7444
111155478 9/21/15 14:09:44 14:10 5002 Early Early BUNCH-E WKDAY 801 1 NORTHBOU 11 CAPITOL STATION 30.2756 -97.7426
111156442 9/21/15 14:12:44 14:12 5002 OnTime Early BUNCH-N WKDAY 801 0 NORTHBOU 12 MUSEUM STATION 30.2793 -97.7412
111157642 9/21/15 14:16:44 14:16 5002 OnTime Early BUNCH-N WKDAY 801 0 NORTHBOU 13 UT WEST MALL 30.2861 -97.7416
111158236 9/21/15 14:18:44 14:16 5002 OnTime OnTime NORMAL-N WKDAY 801 0 NORTHBOU 13 UT WEST MALL 30.2861 -97.7416
111158510 9/21/15 14:19:44 14:19 5002 OnTime Early BUNCH-N WKDAY 801 0 NORTHBOU 14 UT DEAN KEATON 30.2893 -97.7414
111158842 9/21/15 14:20:44 14:19 5002 OnTime OnTime NORMAL-N WKDAY 801 0 NORTHBOU 14 UT DEAN KEATON 30.2893 -97.7414
111159148 9/21/15 14:21:44 14:19 5002 OnTime Early BUNCH-N WKDAY 801 0 NORTHBOU 14 UT DEAN KEATON 30.2893 -97.7414
111159768 9/21/15 14:23:44 14:23 5002 OnTime Early BUNCH-N WKDAY 801 0 NORTHBOU 15 HYDE PARK 30.3044 -97.7370
111160714 9/21/15 14:26:44 14:26 5002 OnTime Early BUNCH-N WKDAY 801 0 NORTHBOU 16 TRIANGLE STATION 30.3148 -97.7323
111161954 9/21/15 14:30:44 14:30 5002 OnTime Early BUNCH-N WKDAY 801 0 NORTHBOU 17 BRENTWOOD STATION 30.3263 -97.7260
111162798 9/21/15 14:33:44 14:30 5002 OnTime OnTime NORMAL-N WKDAY 801 0 NORTHBOU 17 BRENTWOOD STATION 30.3263 -97.7260
111163102 9/21/15 14:34:44 14:30 5002 OnTime Early BUNCH-N WKDAY 801 0 NORTHBOU 17 BRENTWOOD STATION 30.3263 -97.7260
111163386 9/21/15 14:35:44 14:34 5002 OnTime Early BUNCH-N WKDAY 801 0 NORTHBOU 18 CRESTVIEW 30.3369 -97.7193
111165160 9/21/15 14:41:44 14:41 5002 OnTime Early BUNCH-N WKDAY 801 0 NORTHBOU 19 NORTH LAMAR TRANSIT CENTER 30.3493 -97.7117
111166704 9/21/15 14:46:44 14:47 5002 Early Early BUNCH-E WKDAY 801 1 NORTHBOU 20 RUNDBERG 30.3626 -97.6971
111166944 9/21/15 14:47:44 14:50 5002 Early Early BUNCH-E WKDAY 801 3 NORTHBOU 21 MASTERSON 30.3721 -97.6918
111167254 9/21/15 14:48:44 14:50 5002 Early OnTime NORMAL-E WKDAY 801 3 NORTHBOU 21 MASTERSON 30.3721 -97.6918
 
Upvote 0 Downvote
Or how can I suppress the rows in bold, except for first bold record. May be simple, but I'm missing it for some reason.

Thanks again.
 
Upvote 0 Downvote
The quick and dirty way is to group by schTime, hide the details line, put all the data in the Group Footer.
 
Upvote 0 Downvote
Thanks kray4660.

How would I suppress the whole row if there are duplicate "schTime" rows?
 
Upvote 0 Downvote
If you grouped by schTime, then there would not be duplicate rows (detail row suppressed, fields in GF).
 
Upvote 0 Downvote
Try the following:

Create a formula
@GroupFormula
[tt]{tDate}&" "&{schTime}[/tt]

Group on the formula, @GroupFormula (ascending order)
Sort on incTime (descending order)

Copy all the fields from Details to GroupFooter
Suppress Details

Hope this helps.




 
Upvote 0 Downvote
Thanks kray4660 and BettyJ.

I decided to suppress the duplicate records. My runningTotals were still giving me all the records, however I was able to modify them to just give me non-suppressed records.

Appears to be working fine now.

Thanks again for all your help.
 
Upvote 0 Downvote
Yes just as a reminder to everyone. Just because you suppress records, it does not remove them (they are just hiding). :)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

john432
  • Locked
  • Question
Select Expert
Replies
2
Views
145
john432
john432
mmck1964
  • Locked
  • Question
Using "OR" to select data
Replies
4
Views
390
mmck1964
mmck1964
Cpreston
  • Locked
  • Question
Only Select certain rows 1
Replies
7
Views
380
Cpreston
Cpreston
SarahSCPDAdmin
  • Locked
  • Question
Can I format data in a memo field? 1
Replies
5
Views
274
SarahSCPDAdmin
SarahSCPDAdmin
POrlowski
  • Locked
  • Question
Calculating Sick Time
Replies
7
Views
301
POrlowski
POrlowski

Part and Inventory Search

Sponsor

Back
Top