Time Diff and Max Function issue

[gusc999]

Evening,

I have been working on the query below for the past few days and I seem to be stuck. I have a couple issues hopefully one of you can help me with.


RTE LHHSTD LDR STX SELC Cls X LDR ENX Selc Ldr Dif LDR CLT Strt End End Cls Strt Cls LDR# CREW SELC# PTETIM PTTRN#

146
20080928
146 11:32 pm 11:50:48PM 12:32:51AM 1,398 12:38 am 1,380 6 1374 9704 NOCLS 5988 235048 9203070
20080928
146 11:32 pm 11:49:58PM 12:32:51AM 1,397 12:38 am 1,380 6 1374 9704 NOCLS 6176 234958 9203080
20080928
146 11:32 pm 12:06:42AM 12:32:51AM 26 12:38 am 1,380 6 1374 9704 NOCLS 6146 642 9203069

1st – The “Selc Ldr Dif “field is the Time-Diff from the “SELC Cls X” and “LDR-ENX” field and the two fields were converted in a formula to represent a time format.
The time Diff works fine until it comes across midnight where the Diff-time results are a negative number.
Is there a way to fix this??

2nd__ When I try to use the MAX function for the “SELC Cls X “ field it will only register the Max until the 11:59pm anything over that it ignores.
What will allow the MAX Function to acknowledge all time values?

MAX-FORMULA: {PIRTRAN.PTETIM} = maximum({PIRTRAN.PTETIM},{LDRHHDR3.LHHRTE})


RTE LHHSTD LDR STX SELC Cls X LDR ENX Selc Ldr Dif LDR CLT Strt End End Cls Strt Cls LDR# CREW SELC# PTETIM PTTRN#

146
20080928
146 11:32 pm 11:50:48PM 12:32:51AM 1,398 12:38 am 1,380 6 1374 9704 NOCLS 5988 235,048 9203070

147
20080928
147 11:22 pm 11:52:08PM 1:04:14AM 1,368 1:07 am 1,338 3 1335 9928 NOCLS 6426 235,208 9203084

148
20080928
148 11:03 pm 11:59:25PM 12:35:31AM 1,404 12:38 am 1,348 3 1345 9703 NOCLS 6150 235,925 9203109


SQL STATEMENT:

SELECT DISTINCT "LDRHHDR3"."LHHRTE", "LDRHHDR3"."LHHSTD",
"LDRHHDR3"."LHHSTT", "LDRHHDR3"."LHHCLT",
"LDRHHDR3"."LHHENT", "LDRHHDR3"."LHHEMP",
"PIRTRAN"."PTCREW", "PIRTRAN"."PTEMP#",
"PIRTRAN"."PTETIM", "PIRTRAN"."PTTRN#"
FROM {oj "S650832F"."PIRF"."PIRTRAN" "PIRTRAN" INNER JOIN ("S650832F"."PIRF"."LDRHHDR3" "LDRHHDR3" LEFT OUTER JOIN "S650832F"."PIRF"."LABEL" "LABEL" ON "LDRHHDR3"."LHHRTI"="LABEL"."LBRTE") ON "PIRTRAN"."PTTRN#"="LABEL"."LBTRN#"}
WHERE ("LDRHHDR3"."LHHSTD">=20080928 AND "LDRHHDR3"."LHHSTD"<=20080929) AND "LDRHHDR3"."LHHRTE" LIKE '1%'
ORDER BY "LDRHHDR3"."LHHRTE", "PIRTRAN"."PTETIM" DESC

 

[Madawc]

Please do not cross-post.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10