Question Re: SQL Command to select MAX(date) & Max(time) in Technology Q&A

SQL Command to select MAX(date) & Max(time)

Former Member — Tue, 23 Mar 2010 23:36:30 GMT

My am having a really hard time getting a sql command to return data with the max(date) & max(time) combination. I think I need a sub query but can not figure it out. I have looked all over the internet but still don't understand it. Below is my command which returns the max date & max time but they are not from the same row of data. Please help.

SELECT "labor"."order-no", "labor"."oper-no", MAX("labor"."end-date") AS myDate, Max("labor"."end-time") AS myTime

FROM "E940LIVE"."PUB"."tm-log" "labor"

WHERE "labor"."order-no"='73153-bc' AND "labor"."company"='01'

GROUP BY "labor"."order-no", "labor"."oper-no"

Re: SQL Command to select MAX(date) & Max(time)

Former Member — Wed, 24 Mar 2010 07:16:36 GMT

Wayne,

You'll actually want to contaminate the date and time together to get a true date time value. Not sure what type of database you are using so I can only guess at syntax. But you can try something like this...


SELECT 
"labor"."order-no", 
"labor"."oper-no",
MAX(CAST("labor"."end-date" + "labor"."end-time" AS DateTime)) AS myDateTime
FROM "E940LIVE"."PUB"."tm-log" "labor"
GROUP BY "labor"."order-no", "labor"."oper-no"

HTH,

Jason

Re: SQL Command to select MAX(date) & Max(time)

Former Member — Wed, 24 Mar 2010 12:17:53 GMT

Thanks Jason,

My data base is Progress OpenEdge. I tried your proposal before making my post and had problems with that as well. Part of my problem lies in that the time field is a integer representing the number of seconds, 9:09:01AM is 32,941. I was able to get the timestamp combination with this code.

SELECT "labor"."order-no", "labor"."oper-no", TO_TIMESTAMP(({fn CONVERT("labor"."end-date" ,SQL_VARCHAR)}) + ' ' +

(RTRIM(({fn CONVERT({fn CONVERT("labor"."end-time"/3600,SQL_INTEGER)},SQL_VARCHAR)}':'),' ')RTRIM(LTRIM(({fn CONVERT(({fn CONVERT(("labor"."end-time"/3600 - {fn CONVERT("labor"."end-time"/3600,SQL_INTEGER)})*60,SQL_INTEGER)}),SQL_VARCHAR)}+':'),' '),' ') +

LTRIM(({fn CONVERT( ({fn CONVERT( (("labor"."end-time"/3600 - {fn CONVERT("labor"."end-time"/3600,SQL_INTEGER)} )60 - {fn CONVERT( ("labor"."end-time"/3600 - {fn CONVERT("labor"."end-time"/3600,SQL_INTEGER)} )60 ,SQL_INTEGER)}) *60 ,SQL_INTEGER)}) ,SQL_VARCHAR)}), ' ') ) ) AS myTime

FROM "E940LIVE"."PUB"."tm-log" "labor"

WHERE "labor"."order-no"='73153-bc' AND "labor"."company"='01'

GROUP BY "labor"."order-no", "labor"."oper-no", myTime

But when I put the max function around TO_TIMESTAMP like this MAX(TO_TIMSTAMP **** ) AS myTime I got error:

Aggregate function not allowed here (7642) [Database Vendor Code: -20023]

Wayne

Re: SQL Command to select MAX(date) & Max(time)

Former Member — Wed, 24 Mar 2010 13:34:41 GMT

Wayne,

I'm not familiar w Progress OpenEdge or it's available functions, but if the time field is simply an INT showing the number of seconds past midnight... You should be able to simply add the seconds to date field using a DateAdd function.


DateAdd(ss,"labor"."end-time", "labor"."end-date") AS myDateTime

You'll need check the syntax for OpenEdge...

This also assumes that the end-date field is an actual DateTime field type. If not, you may need to cast or convert the end-date field as a data type that will allow for the time to be included.

HTH,

Jason

Re: SQL Command to select MAX(date) & Max(time)

Former Member — Wed, 24 Mar 2010 18:03:03 GMT

Progress does not support the DATEADD function. Waht if I forget the number is time and look at it just as a number. Here is my data and what I have tried (again I don't understand the multiple select concept yet).

Data

oper-no end-date end-time

20 2/2/2010 41,975

30 2/3/2010 45,906

30 2/16/2010 32,941

40 2/4/2010 46,099

40 2/4/2010 50,227

40 2/4/2010 59,466

40 2/4/2010 62,024

40 2/16/2010 43,838

60 2/17/2010 32,679

90 2/25/2010 35,270

SQL Command

SELECT a."oper-no", a."end-time", a."end-date"

FROM "E940LIVE"."PUB"."tm-log" a, (SELECT "end-time", max("end-date") AS max_date FROM "E940LIVE"."PUB"."tm-log" WHERE "order-no"='73153-bc' AND "company"='01' GROUP BY "end-date", "end-time") b

WHERE a."end-time" = b."end-time" AND a."end-date" = b.max_date AND a."order-no"='73153-bc' AND a."company"='01'

Result

oper-no end-date end-time

20 2/2/2010 41,975

30 2/3/2010 45,906

40 2/4/2010 50,227

40 2/4/2010 46,099

40 2/4/2010 59,466

40 2/4/2010 62,024

30 2/16/2010 32,941

40 2/16/2010 43,838

60 2/17/2010 32,679

90 2/25/2010 35,270

Desired Result

oper-no end-date end-time

20 2/2/2010 41,975

30 2/16/2010 32,941

40 2/16/2010 43,838

60 2/17/2010 32,679

90 2/25/2010 35,270

Thanks for any and all help!

Wayne

Re: SQL Command to select MAX(date) & Max(time)

Former Member — Thu, 25 Mar 2010 19:56:56 GMT

See what this does for you...


SELECT
a."oper-no",
a."end-date",
MAX(a."end-time") AS 
FROM "tm-log" AS a
INNER JOIN (
	SELECT 
	"oper-no",
	MAX("end-date") AS MaxEndDate,
	FROM "tm-log"
	WHERE a."order-no"='73153-bc' AND a."company"='01'
	GROUP BY "oper-no") AS d 
ON a."oper-no" = d."oper-no" AND a."end-date" = d.MaxEndDate
GROUP BY a."oper-no", a."end-date"

The subquery will filter the the records down to only those on the "last date for each oper-no.

Using an INNER join will force that filtering onto the outer query which can then be further filtered to find the largest number value in the end-time, for each oper-no.

I don't have any similar data to test on but it should work.

Jason

Re: SQL Command to select MAX(date) & Max(time)

Former Member — Fri, 26 Mar 2010 14:48:18 GMT

Your code did not work as submitted, it gave me a syntax error (probably a progress thing). I finally got some support from progress and got code that works. It still does not create a finial datestamp, but is returning the fields from the correct rows.

SELECT "order-no", "oper-no","end-date","end-time"

FROM

"PUB"."tm-log"

WHERE

"end-date" IN (SELECT

MAX("end-date")

FROM

"PUB"."tm-log"

WHERE "order-no"='73153-bc' AND "company"='01'

GROUP BY

"oper-no")

AND

"end-time" IN (SELECT

MAX("end-time")

FROM "PUB"."tm-log"

WHERE "order-no"='73153-bc' AND "company"='01'

GROUP BY

"oper-no",

"end-date")

Thanks for all your help

Wayne

Re: SQL Command to select MAX(date) & Max(time)

Former Member — Fri, 26 Mar 2010 15:39:08 GMT

yea, I see where you would catch an error in the code I supplied... I copied the WHERE clause directly from your previous post and forgot to remove the "a." aliases. Oops. Sorry.

Glad you got it working. As long as you are getting the correct rows back from the database, you can easily convert the date & time to a single DateTime stamp... CR DOES have the DateAdd function.


DateAdd("s", {Command.end-time}, {Command.end-date})

Jason