on 03-23-2010 11:36 PM
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"
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
61 | |
9 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.