Correlated subquery Date Calculation help

[Kevin Saccullo]

Having read all the post in this group and reading all the KB articles I
still can’t get the expected query results.

What I’m trying to calculate is the change over time between different
molds. Among other things each record in the table has a field for “First
Good Part†and the “Last Good Partâ€. What I need is the “First Good Part†of
one record minus the “Last Good Part†of the prior record. Neither field is
a Primary key. They are however part of an index in the table to prevent
duplicate times for a specific press, working shift and date. The records
are not input in any specific order, so using the autonumber index is not an
option.


FirstGoodPart LastGoodPart
06:00 07:00
07:09 09:48
10:01 12:53
13:03 15:30

Desired output
FirstGoodPart LastGoodPart ChangeOer
06:00 07:00 “null†(First run of the shift)
07:09 09:48 9
10:01 12:53 13
13:03 15:30 10

Closest SQL I have that makes sense but still doesn’t work (yes I copied
most of this from other post).

SELECT T.LastGoodPart, (SELECT DateDiff("n",NZ(MAX
(T1.LastGoodPart),T.FirstGoodPart), T.FirstGoodPart)
FROM TblMoldRun T1
WHERE T1.LastGoodPart < T.FirstGoodPart) AS [Interval]
FROM TblMoldRun AS T
ORDER BY T.LastGoodPart;

Any and all help welcome

 

[Barry Gilbert]

I think you need to use a select statement to get the Max to work:
Select T.FirstGoodPart, T.LastGoodPart, (SELECT(DateDiff("n", NZ(SELECT
MAX(T1.LastGoodRun FROM tblMoldRun T1 WHERE T1.LastGoodPart
<T.FirstGoodPart), T.FirstGoodPart) As [Interval]) FROM tblModRun AS T Order
By T.LastGoodPart;

HTH,
Barry

 

[Kevin Saccullo]

Thanks. After making a test table I found more filtering was required.
Added the two AND conditions to the WHERE clause.

WHERE T.fWhoWhenRecoredNumber = T1.fWhoWhenRecoredNumber AND T.Process =
T1.Process AND T1.LastGoodPart < T.FirstGoodPart)

I think you need to use a select statement to get the Max to work:
Select T.FirstGoodPart, T.LastGoodPart, (SELECT(DateDiff("n", NZ(SELECT
MAX(T1.LastGoodRun FROM tblMoldRun T1 WHERE T1.LastGoodPart
<T.FirstGoodPart), T.FirstGoodPart) As [Interval]) FROM tblModRun AS T Order
By T.LastGoodPart;

HTH,
Barry

Having read all the post in this group and reading all the KB articles I
still can’t get the expected query results.

What I’m trying to calculate is the change over time between different
molds. Among other things each record in the table has a field for “First
Good Part†and the “Last Good Partâ€. What I need is the “First Good Part†of
one record minus the “Last Good Part†of the prior record. Neither field is
a Primary key. They are however part of an index in the table to prevent
duplicate times for a specific press, working shift and date. The records
are not input in any specific order, so using the autonumber index is not an
option.


FirstGoodPart LastGoodPart
06:00 07:00
07:09 09:48
10:01 12:53
13:03 15:30

Desired output
FirstGoodPart LastGoodPart ChangeOer
06:00 07:00 “null†(First run of the shift)
07:09 09:48 9
10:01 12:53 13
13:03 15:30 10

Closest SQL I have that makes sense but still doesn’t work (yes I copied
most of this from other post).

SELECT T.LastGoodPart, (SELECT DateDiff("n",NZ(MAX
(T1.LastGoodPart),T.FirstGoodPart), T.FirstGoodPart)
FROM TblMoldRun T1
WHERE T1.LastGoodPart < T.FirstGoodPart) AS [Interval]
FROM TblMoldRun AS T
ORDER BY T.LastGoodPart;

Any and all help welcome