Max Function and Time Diff 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]

If Crystal, the DateDiff function will handle date-time values for different days. It's not clear what you're doing but it looks like SQL.

It always helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.


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

 

[gusc999]

I'm not using SQL, I copied and pasted the SQL statement from Crystal XI Database>Show SQL Query. The time field is in a numberic 24 hr format. I use this formula below so it would change it to a time format so I could use the Time Diff formula.

Local NumberVar ConvertTime := {PIRTRAN.PTETIM};

Local NumberVar MyHours;
Local NumberVar MyMinutes;
Local NumberVar MySeconds;

MyHours := ConvertTime \ 10000;
ConvertTime := ConvertTime mod 10000;
MyMinutes := ConvertTime \ 100;
MySeconds := ConvertTime mod 100;

Time(MyHours,MyMinutes,MySeconds)
Name: {@tek6Diff}
Formula: DateDiff("n",{@Selc-Ldr-End},{@tek3})

 

[CR85user]

This has been overcomplicated with additional unrelated information provided.

It is difficult to get the balance right between not giving enough info and giving too much!

Could I ask you to provide the following:

1) The contents of the following formulas (including any nested formulas)

{@tek3}
{@Selc-Ldr-End}
{@tek6Diff}

2) Please list any relevant date fields.

3) You mentioned that the time was a numeric string format - Could you provide a copy of the data in its original format? (I know you have already converted this, but I would like to see the original)


The basic method is to provide a check in the time diff formula to compare start and end dates so as to provide an accurate difference.

Alternatively combine date and time fields so as to provide a datetime diff as opposed to a time diff only.

If you can give the info listed above then we should be able to resolve this easily.

Your max is currently picking out the highest time. Obviously 22:00 will always be the max in comparison to 02:00.

If you were to do a max based on maximum({time},{date}) then you would give it the basis on which to select correctly.

'J

CR8.5 / CRXI - Discovering the impossible