Query Structure for Find Next

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

This one has me stumped.

I have a table which has this basic structure.

Equipment#,Status,StatusDate
xxx,stat1,2/1/03
yyy,stat2,1/3/03
xxx,stat2,1/4/03
xxx,stat1,1/3/02
yyy,stat1,2/2/03
xxx,stat1,4/2/04
yyy,stat1,2/2/04

What I want to do is to create a query to return the time
between status events (CycleTime) for a particular piece
of equipment.

So the resulting query for the example above would look
like this:
Equipment#,status,StatusDate,Next:Status Date,CycleTime
xxx, stat1, 1/3/02, 2/1/03, 393 days
xxx, stat1, 2/1/03, 4/2/03, 61 days
xxx, stat1, 4/2/03, null, null
xxx, stat2, 1/4/03, null, null
yyy, stat1, 2/2/03, 2/2/04, 365 days
yyy, stat2, 1/3/03, null, null

How would I do this? My stumbling block is trying to
figure out how to identify the "Next" status date and
return it.
 
You might try something like this:

1. Create a query (say, named "Query1") to find the Next status date using
SQL something like this:

SELECT
[Your Table].[Equipment#],
[Your Table].[Status],
[Your Table].[StatusDate],
(SELECT TOP 1
[Self].[StatusDate]
FROM
[Your Table] AS [Self]
WHERE
[Self].[Equipment#] = [Your Table].[Equipment#]
AND
[Self].[Status] = [Your Table].[Status]
ORDER BY
[Self].[StatusDate]) AS [NextStatusDate]

2. Create another query based on Query1 that calculates the CycleTime in
days using SQL something like this:

SELECT
[Query1].*,
[Query1].[NextStatusDate] - [Query1].[StatusDate] AS [CycleTime]
FROM
[Query1]
 
Todd said:
This one has me stumped.

I have a table which has this basic structure.

Equipment#,Status,StatusDate
xxx,stat1,2/1/03
yyy,stat2,1/3/03
xxx,stat2,1/4/03
xxx,stat1,1/3/02
yyy,stat1,2/2/03
xxx,stat1,4/2/04
yyy,stat1,2/2/04

What I want to do is to create a query to return the time
between status events (CycleTime) for a particular piece
of equipment.

So the resulting query for the example above would look
like this:
Equipment#,status,StatusDate,Next:Status Date,CycleTime
xxx, stat1, 1/3/02, 2/1/03, 393 days
xxx, stat1, 2/1/03, 4/2/03, 61 days
xxx, stat1, 4/2/03, null, null
xxx, stat2, 1/4/03, null, null
yyy, stat1, 2/2/03, 2/2/04, 365 days
yyy, stat2, 1/3/03, null, null

How would I do this? My stumbling block is trying to
figure out how to identify the "Next" status date and
return it.
Click to expand...

Hi Todd,

I'm sure Brian's solution is probably right on,
so with apologies to Brian for butting in,
but maybe a different way
at looking through the process...


assumed "tblStatus" as name of your table
and sorted them by "E, S, SD"

Equipment# Status StatusDate nextwouldbe
xxx stat1 1/3/2002 2/1/2003
xxx stat1 2/1/2003 4/2/2004
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004
yyy stat1 2/2/2004
yyy stat2 1/3/2003

so it was easier to see what your
"next" would be.

Started with a simple query where
brought the table in twice.

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#])
AND (t1.Status = t2.Status)
ORDER BY t1.[Equipment#],
t1.Status, t1.StatusDate, t2.StatusDate;

Equipment# Status t1StatusDate t2StatusDate CycleTime
xxx stat1 1/3/2002 1/3/2002 0
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 1/3/2002 4/2/2004 820
xxx stat1 2/1/2003 1/3/2002 -394
xxx stat1 2/1/2003 2/1/2003 0
xxx stat1 2/1/2003 4/2/2004 426
xxx stat1 4/2/2004 1/3/2002 -820
xxx stat1 4/2/2004 2/1/2003 -426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0
yyy stat1 2/2/2003 2/2/2004 365
yyy stat1 2/2/2004 2/2/2003 -365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

To that query I added one more field ( a select
subquery where find the minimum date that is
larger than t1 date)

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status A
ND t3.StatusDate>t1.StatusDate) AS NextStatusDate
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status = t2.Status)
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate, t2.StatusDate;

producing:

"E" "S" t1StatusDate t2StatusDate CycleTime NextStatusDate
xxx stat1 1/3/2002 1/3/2002 0 2/1/2003
xxx stat1 1/3/2002 2/1/2003 394 2/1/2003
xxx stat1 1/3/2002 4/2/2004 820 2/1/2003
xxx stat1 2/1/2003 1/3/2002 -394 4/2/2004
xxx stat1 2/1/2003 2/1/2003 0 4/2/2004
xxx stat1 2/1/2003 4/2/2004 426 4/2/2004
xxx stat1 4/2/2004 1/3/2002 -820
xxx stat1 4/2/2004 2/1/2003 -426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0 2/2/2004
yyy stat1 2/2/2003 2/2/2004 365 2/2/2004
yyy stat1 2/2/2004 2/2/2003 -365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

it looks like for any Equipment#/Status/tStatusDate,
it is selecting the correct "NextStatusDate."

Next, changed query to filter out only the records where
t2StatusDate = NextStatusDate
and saved as "qryStatus"

SELECT
t1.[Equipment#], t1.Status,
t1.StatusDate,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate) AS NextStatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status = t2.Status)
WHERE ((((SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate))=[t2].[StatusDate]))
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate;

qryStatus produced:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 2/1/2003 4/2/2004 426
yyy stat1 2/2/2003 2/2/2004 365

close..but one more query:

SELECT
tblStatus.[Equipment#],
tblStatus.Status,
tblStatus.StatusDate,
qryStatus.NextStatusDate,
qryStatus.CycleTime
FROM tblStatus LEFT JOIN qryStatus
ON (tblStatus.StatusDate = qryStatus.StatusDate)
AND (tblStatus.Status = qryStatus.Status)
AND (tblStatus.[Equipment#] = qryStatus.[Equipment#])
ORDER BY tblStatus.[Equipment#], tblStatus.Status, tblStatus.StatusDate;

producing:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 2/1/2003 4/2/2004 426
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004 365
yyy stat1 2/2/2004
yyy stat2 1/3/2003

Again, apologies for butting in.
Sometimes it has helped me when
posters have broken down solutions
like the above. Maybe it will help you.

Good luck,

Gary Walter
 
Also, the computation of CycleTime
assumed your dates do not have a time portion
{if they do have a time portion, one method is to
use DateValue() to compute CycleTime}

date1=#1/3/02#
date2=#2/1/03#
?date2-date1
394
date1=#1/3/02 15:00 PM#
date2=#2/1/03 1:00 AM#
?date2-date1
393.416666666664
?DateValue(date2)-DateValue(date1)
394

Gary Walter said:
Todd said:
This one has me stumped.

I have a table which has this basic structure.

Equipment#,Status,StatusDate
xxx,stat1,2/1/03
yyy,stat2,1/3/03
xxx,stat2,1/4/03
xxx,stat1,1/3/02
yyy,stat1,2/2/03
xxx,stat1,4/2/04
yyy,stat1,2/2/04

What I want to do is to create a query to return the time
between status events (CycleTime) for a particular piece
of equipment.

So the resulting query for the example above would look
like this:
Equipment#,status,StatusDate,Next:Status Date,CycleTime
xxx, stat1, 1/3/02, 2/1/03, 393 days
xxx, stat1, 2/1/03, 4/2/03, 61 days
xxx, stat1, 4/2/03, null, null
xxx, stat2, 1/4/03, null, null
yyy, stat1, 2/2/03, 2/2/04, 365 days
yyy, stat2, 1/3/03, null, null

How would I do this? My stumbling block is trying to
figure out how to identify the "Next" status date and
return it.
Click to expand...

Hi Todd,

I'm sure Brian's solution is probably right on,
so with apologies to Brian for butting in,
but maybe a different way
at looking through the process...


assumed "tblStatus" as name of your table
and sorted them by "E, S, SD"

Equipment# Status StatusDate nextwouldbe
xxx stat1 1/3/2002 2/1/2003
xxx stat1 2/1/2003 4/2/2004
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004
yyy stat1 2/2/2004
yyy stat2 1/3/2003

so it was easier to see what your
"next" would be.

Started with a simple query where
brought the table in twice.

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#])
AND (t1.Status = t2.Status)
ORDER BY t1.[Equipment#],
t1.Status, t1.StatusDate, t2.StatusDate;

Equipment# Status t1StatusDate t2StatusDate CycleTime
xxx stat1 1/3/2002 1/3/2002 0
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 1/3/2002 4/2/2004 820
xxx stat1 2/1/2003 1/3/2002 -394
xxx stat1 2/1/2003 2/1/2003 0
xxx stat1 2/1/2003 4/2/2004 426
xxx stat1 4/2/2004 1/3/2002 -820
xxx stat1 4/2/2004 2/1/2003 -426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0
yyy stat1 2/2/2003 2/2/2004 365
yyy stat1 2/2/2004 2/2/2003 -365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

To that query I added one more field ( a select
subquery where find the minimum date that is
larger than t1 date)

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status A
ND t3.StatusDate>t1.StatusDate) AS NextStatusDate
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status = t2.Status)
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate, t2.StatusDate;

producing:

"E" "S" t1StatusDate t2StatusDate CycleTime NextStatusDate
xxx stat1 1/3/2002 1/3/2002 0 2/1/2003
xxx stat1 1/3/2002 2/1/2003 394 2/1/2003
xxx stat1 1/3/2002 4/2/2004 820 2/1/2003
xxx stat1 2/1/2003 1/3/2002 -394 4/2/2004
xxx stat1 2/1/2003 2/1/2003 0 4/2/2004
xxx stat1 2/1/2003 4/2/2004 426 4/2/2004
xxx stat1 4/2/2004 1/3/2002 -820
xxx stat1 4/2/2004 2/1/2003 -426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0 2/2/2004
yyy stat1 2/2/2003 2/2/2004 365 2/2/2004
yyy stat1 2/2/2004 2/2/2003 -365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

it looks like for any Equipment#/Status/tStatusDate,
it is selecting the correct "NextStatusDate."

Next, changed query to filter out only the records where
t2StatusDate = NextStatusDate
and saved as "qryStatus"

SELECT
t1.[Equipment#], t1.Status,
t1.StatusDate,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate) AS NextStatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status = t2.Status)
WHERE ((((SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate))=[t2].[StatusDate]))
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate;

qryStatus produced:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 2/1/2003 4/2/2004 426
yyy stat1 2/2/2003 2/2/2004 365

close..but one more query:

SELECT
tblStatus.[Equipment#],
tblStatus.Status,
tblStatus.StatusDate,
qryStatus.NextStatusDate,
qryStatus.CycleTime
FROM tblStatus LEFT JOIN qryStatus
ON (tblStatus.StatusDate = qryStatus.StatusDate)
AND (tblStatus.Status = qryStatus.Status)
AND (tblStatus.[Equipment#] = qryStatus.[Equipment#])
ORDER BY tblStatus.[Equipment#], tblStatus.Status, tblStatus.StatusDate;

producing:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 2/1/2003 4/2/2004 426
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004 365
yyy stat1 2/2/2004
yyy stat2 1/3/2003

Again, apologies for butting in.
Sometimes it has helped me when
posters have broken down solutions
like the above. Maybe it will help you.

Good luck,

Gary Walter
Click to expand...
 
FANTASTIC!!!!!
THANK YOU VERY MUCH!!!!!!!

-----Original Message-----

Todd said:
This one has me stumped.

I have a table which has this basic structure.

Equipment#,Status,StatusDate
xxx,stat1,2/1/03
yyy,stat2,1/3/03
xxx,stat2,1/4/03
xxx,stat1,1/3/02
yyy,stat1,2/2/03
xxx,stat1,4/2/04
yyy,stat1,2/2/04

What I want to do is to create a query to return the time
between status events (CycleTime) for a particular piece
of equipment.

So the resulting query for the example above would look
like this:
Equipment#,status,StatusDate,Next:Status Date,CycleTime
xxx, stat1, 1/3/02, 2/1/03, 393 days
xxx, stat1, 2/1/03, 4/2/03, 61 days
xxx, stat1, 4/2/03, null, null
xxx, stat2, 1/4/03, null, null
yyy, stat1, 2/2/03, 2/2/04, 365 days
yyy, stat2, 1/3/03, null, null

How would I do this? My stumbling block is trying to
figure out how to identify the "Next" status date and
return it.
Click to expand...

Hi Todd,

I'm sure Brian's solution is probably right on,
so with apologies to Brian for butting in,
but maybe a different way
at looking through the process...


assumed "tblStatus" as name of your table
and sorted them by "E, S, SD"

Equipment# Status StatusDate nextwouldbe
xxx stat1 1/3/2002 2/1/2003
xxx stat1 2/1/2003 4/2/2004
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004
yyy stat1 2/2/2004
yyy stat2 1/3/2003

so it was easier to see what your
"next" would be.

Started with a simple query where
brought the table in twice.

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#])
AND (t1.Status = t2.Status)
ORDER BY t1.[Equipment#],
t1.Status, t1.StatusDate, t2.StatusDate;

Equipment# Status t1StatusDate t2StatusDate CycleTime
xxx stat1 1/3/2002 1/3/2002 0
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 1/3/2002 4/2/2004 820
xxx stat1 2/1/2003 1/3/2002 -394
xxx stat1 2/1/2003 2/1/2003 0
xxx stat1 2/1/2003 4/2/2004 426
xxx stat1 4/2/2004 1/3/2002 -820
xxx stat1 4/2/2004 2/1/2003 -426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0
yyy stat1 2/2/2003 2/2/2004 365
yyy stat1 2/2/2004 2/2/2003 -365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

To that query I added one more field ( a select
subquery where find the minimum date that is
larger than t1 date)

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status A
ND t3.StatusDate>t1.StatusDate) AS NextStatusDate
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status = t2.Status)
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate, t2.StatusDate;

producing:

"E" "S" t1StatusDate t2StatusDate CycleTime NextStatusDate
xxx stat1 1/3/2002 1/3/2002 0 2/1/2003
xxx stat1 1/3/2002 2/1/2003 394 2/1/2003
xxx stat1 1/3/2002 4/2/2004 820 2/1/2003
xxx stat1 2/1/2003 1/3/2002 -394 4/2/2004
xxx stat1 2/1/2003 2/1/2003 0 4/2/2004
xxx stat1 2/1/2003 4/2/2004 426 4/2/2004
xxx stat1 4/2/2004 1/3/2002 -820
xxx stat1 4/2/2004 2/1/2003 -426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0 2/2/2004
yyy stat1 2/2/2003 2/2/2004 365 2/2/2004
yyy stat1 2/2/2004 2/2/2003 -365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

it looks like for any Equipment#/Status/tStatusDate,
it is selecting the correct "NextStatusDate."

Next, changed query to filter out only the records where
t2StatusDate = NextStatusDate
and saved as "qryStatus"

SELECT
t1.[Equipment#], t1.Status,
t1.StatusDate,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate) AS NextStatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status = t2.Status)
WHERE ((((SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate))=[t2].[StatusDate]))
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate;

qryStatus produced:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 2/1/2003 4/2/2004 426
yyy stat1 2/2/2003 2/2/2004 365

close..but one more query:

SELECT
tblStatus.[Equipment#],
tblStatus.Status,
tblStatus.StatusDate,
qryStatus.NextStatusDate,
qryStatus.CycleTime
FROM tblStatus LEFT JOIN qryStatus
ON (tblStatus.StatusDate = qryStatus.StatusDate)
AND (tblStatus.Status = qryStatus.Status)
AND (tblStatus.[Equipment#] = qryStatus.[Equipment#])
ORDER BY tblStatus.[Equipment#], tblStatus.Status, tblStatus.StatusDate;

producing:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 2/1/2003 4/2/2004 426
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004 365
yyy stat1 2/2/2004
yyy stat2 1/3/2003

Again, apologies for butting in.
Sometimes it has helped me when
posters have broken down solutions
like the above. Maybe it will help you.

Good luck,

Gary Walter


.
Click to expand...
 
Gary,
I got as far as the first query, and I'm stuck again on
the "Min" subquery. I can't figure out what your t3 is,
and what the right syntax is.

I've used queries instead of tables for the source of the
data, I think this is OK.

Here is the SQL that I have so far, and note that I have
taken this from my actual database so that the generic
names I used before are changed to the actual names in the
database:
So tblStatus = BH-Freeze Date Query.
Equipment# = Vessel Used
Status Date = Freeze Date
and also, the query I did got rid of all the alternate
status, so you don't need to worry about the status
column, just freeze date and vessel used.

SQL Statement:
SELECT [BH-Freeze Date Query].[Vessel Used], [BH-Freeze
Date Query].[Freeze Date], [BH-Freeze Date Query_1].
[Freeze Date], [BH-Freeze Date Query_1.Freeze Date]-[BH-
Freeze Date Query.Freeze Date] AS CycleTime
FROM [BH-Freeze Date Query] INNER JOIN [BH-Freeze Date
Query] AS [BH-Freeze Date Query_1] ON [BH-Freeze Date
Query].[Vessel Used] = [BH-Freeze Date Query_1].[Vessel
Used]
GROUP BY [BH-Freeze Date Query].[Vessel Used], [BH-Freeze
Date Query].[Freeze Date], [BH-Freeze Date Query_1].
[Freeze Date], [BH-Freeze Date Query_1.Freeze Date]-[BH-
Freeze Date Query.Freeze Date]
ORDER BY [BH-Freeze Date Query].[Vessel Used], [BH-Freeze
Date Query].[Freeze Date], [BH-Freeze Date Query_1].
[Freeze Date];

Can you help me figure out the syntax for the "Min"
subquery?

Thanks,
Todd

-----Original Message-----

Todd said:
This one has me stumped.

I have a table which has this basic structure.

Equipment#,Status,StatusDate
xxx,stat1,2/1/03
yyy,stat2,1/3/03
xxx,stat2,1/4/03
xxx,stat1,1/3/02
yyy,stat1,2/2/03
xxx,stat1,4/2/04
yyy,stat1,2/2/04

What I want to do is to create a query to return the time
between status events (CycleTime) for a particular piece
of equipment.

So the resulting query for the example above would look
like this:
Equipment#,status,StatusDate,Next:Status Date,CycleTime
xxx, stat1, 1/3/02, 2/1/03, 393 days
xxx, stat1, 2/1/03, 4/2/03, 61 days
xxx, stat1, 4/2/03, null, null
xxx, stat2, 1/4/03, null, null
yyy, stat1, 2/2/03, 2/2/04, 365 days
yyy, stat2, 1/3/03, null, null

How would I do this? My stumbling block is trying to
figure out how to identify the "Next" status date and
return it.
Click to expand...

Hi Todd,

I'm sure Brian's solution is probably right on,
so with apologies to Brian for butting in,
but maybe a different way
at looking through the process...


assumed "tblStatus" as name of your table
and sorted them by "E, S, SD"

Equipment# Status StatusDate nextwouldbe
xxx stat1 1/3/2002 2/1/2003
xxx stat1 2/1/2003 4/2/2004
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004
yyy stat1 2/2/2004
yyy stat2 1/3/2003

so it was easier to see what your
"next" would be.

Started with a simple query where
brought the table in twice.

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#])
AND (t1.Status = t2.Status)
ORDER BY t1.[Equipment#],
t1.Status, t1.StatusDate, t2.StatusDate;

Equipment# Status t1StatusDate t2StatusDate CycleTime
xxx stat1 1/3/2002 1/3/2002 0
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 1/3/2002 4/2/2004 820
xxx stat1 2/1/2003 1/3/2002 -394
xxx stat1 2/1/2003 2/1/2003 0
xxx stat1 2/1/2003 4/2/2004 426
xxx stat1 4/2/2004 1/3/2002 -820
xxx stat1 4/2/2004 2/1/2003 -426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0
yyy stat1 2/2/2003 2/2/2004 365
yyy stat1 2/2/2004 2/2/2003 -365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

To that query I added one more field ( a select
subquery where find the minimum date that is
larger than t1 date)

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status A
ND t3.StatusDate>t1.StatusDate) AS NextStatusDate
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status = t2.Status)
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate, t2.StatusDate;

producing:

"E" "S" t1StatusDate t2StatusDate CycleTime NextStatusDate
xxx stat1 1/3/2002 1/3/2002 0 2/1/2003
xxx stat1 1/3/2002 2/1/2003 394 2/1/2003
xxx stat1 1/3/2002 4/2/2004 820 2/1/2003
xxx stat1 2/1/2003 1/3/2002 -394 4/2/2004
xxx stat1 2/1/2003 2/1/2003 0 4/2/2004
xxx stat1 2/1/2003 4/2/2004 426 4/2/2004
xxx stat1 4/2/2004 1/3/2002 -820
xxx stat1 4/2/2004 2/1/2003 -426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0 2/2/2004
yyy stat1 2/2/2003 2/2/2004 365 2/2/2004
yyy stat1 2/2/2004 2/2/2003 -365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

it looks like for any Equipment#/Status/tStatusDate,
it is selecting the correct "NextStatusDate."

Next, changed query to filter out only the records where
t2StatusDate = NextStatusDate
and saved as "qryStatus"

SELECT
t1.[Equipment#], t1.Status,
t1.StatusDate,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate) AS NextStatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status = t2.Status)
WHERE ((((SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate))=[t2].[StatusDate]))
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate;

qryStatus produced:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 2/1/2003 4/2/2004 426
yyy stat1 2/2/2003 2/2/2004 365

close..but one more query:

SELECT
tblStatus.[Equipment#],
tblStatus.Status,
tblStatus.StatusDate,
qryStatus.NextStatusDate,
qryStatus.CycleTime
FROM tblStatus LEFT JOIN qryStatus
ON (tblStatus.StatusDate = qryStatus.StatusDate)
AND (tblStatus.Status = qryStatus.Status)
AND (tblStatus.[Equipment#] = qryStatus.[Equipment#])
ORDER BY tblStatus.[Equipment#], tblStatus.Status, tblStatus.StatusDate;

producing:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 2/1/2003 4/2/2004 426
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004 365
yyy stat1 2/2/2004
yyy stat2 1/3/2003

Again, apologies for butting in.
Sometimes it has helped me when
posters have broken down solutions
like the above. Maybe it will help you.

Good luck,

Gary Walter


.
Click to expand...
 
Todd said:
Gary,
I got as far as the first query, and I'm stuck again on
the "Min" subquery. I can't figure out what your t3 is,
and what the right syntax is.

I've used queries instead of tables for the source of the
data, I think this is OK.

Here is the SQL that I have so far, and note that I have
taken this from my actual database so that the generic
names I used before are changed to the actual names in the
database:
So tblStatus = BH-Freeze Date Query.
Equipment# = Vessel Used
Status Date = Freeze Date
and also, the query I did got rid of all the alternate
status, so you don't need to worry about the status
column, just freeze date and vessel used.
Click to expand...
q1, q2, and q3 (originally t1, t2, t3)
are just aliases for the same query
(originally for the same table).

I believe this is what you wanted
(it has been a long day and I don't
have much "bandwidth" left, so I
could easily be wrong...)

SELECT
q1.[Vessel Used],
q1.[Freeze Date],
(SELECT Min(q3.[Freeze Date])
From [BH-Freeze Date Query] As q3
WHERE q3.[Vessel Used]=q1.[Vessel Used]
AND q3.[Freeze Date]>q1.[Freeze Date]) AS NextStatusDate,
[q2].[Freeze Date]-[q1].[Freeze Date] AS CycleTime
FROM [BH-Freeze Date Query] AS q1
INNER JOIN
[BH-Freeze Date Query] AS q2
ON (q1.[Vessel Used] = q2.[Vessel Used])
WHERE ((((SELECT Min(q3.[Freeze Date])
From [BH-Freeze Date Query] As q3
WHERE q3.[Vessel Used]=q1.[Vessel Used]
AND q3.[Freeze Date]>q1.[Freeze Date]))=[q2].[Freeze Date]))
ORDER BY q1.[Vessel Used], q1.[Freeze Date];


SQL Statement:
SELECT [BH-Freeze Date Query].[Vessel Used], [BH-Freeze
Date Query].[Freeze Date], [BH-Freeze Date Query_1].
[Freeze Date], [BH-Freeze Date Query_1.Freeze Date]-[BH-
Freeze Date Query.Freeze Date] AS CycleTime
FROM [BH-Freeze Date Query] INNER JOIN [BH-Freeze Date
Query] AS [BH-Freeze Date Query_1] ON [BH-Freeze Date
Query].[Vessel Used] = [BH-Freeze Date Query_1].[Vessel
Used]
GROUP BY [BH-Freeze Date Query].[Vessel Used], [BH-Freeze
Date Query].[Freeze Date], [BH-Freeze Date Query_1].
[Freeze Date], [BH-Freeze Date Query_1.Freeze Date]-[BH-
Freeze Date Query.Freeze Date]
ORDER BY [BH-Freeze Date Query].[Vessel Used], [BH-Freeze
Date Query].[Freeze Date], [BH-Freeze Date Query_1].
[Freeze Date];

Can you help me figure out the syntax for the "Min"
subquery?

Thanks,
Todd

-----Original Message-----

Todd said:
This one has me stumped.

I have a table which has this basic structure.

Equipment#,Status,StatusDate
xxx,stat1,2/1/03
yyy,stat2,1/3/03
xxx,stat2,1/4/03
xxx,stat1,1/3/02
yyy,stat1,2/2/03
xxx,stat1,4/2/04
yyy,stat1,2/2/04

What I want to do is to create a query to return the time
between status events (CycleTime) for a particular piece
of equipment.

So the resulting query for the example above would look
like this:
Equipment#,status,StatusDate,Next:Status Date,CycleTime
xxx, stat1, 1/3/02, 2/1/03, 393 days
xxx, stat1, 2/1/03, 4/2/03, 61 days
xxx, stat1, 4/2/03, null, null
xxx, stat2, 1/4/03, null, null
yyy, stat1, 2/2/03, 2/2/04, 365 days
yyy, stat2, 1/3/03, null, null

How would I do this? My stumbling block is trying to
figure out how to identify the "Next" status date and
return it.
Click to expand...

Hi Todd,

I'm sure Brian's solution is probably right on,
so with apologies to Brian for butting in,
but maybe a different way
at looking through the process...


assumed "tblStatus" as name of your table
and sorted them by "E, S, SD"

Equipment# Status StatusDate nextwouldbe
xxx stat1 1/3/2002 2/1/2003
xxx stat1 2/1/2003 4/2/2004
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004
yyy stat1 2/2/2004
yyy stat2 1/3/2003

so it was easier to see what your
"next" would be.

Started with a simple query where
brought the table in twice.

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#])
AND (t1.Status = t2.Status)
ORDER BY t1.[Equipment#],
t1.Status, t1.StatusDate, t2.StatusDate;

Equipment# Status t1StatusDate t2StatusDate CycleTime
xxx stat1 1/3/2002 1/3/2002 0
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 1/3/2002 4/2/2004 820
xxx stat1 2/1/2003 1/3/2002 -394
xxx stat1 2/1/2003 2/1/2003 0
xxx stat1 2/1/2003 4/2/2004 426
xxx stat1 4/2/2004 1/3/2002 -820
xxx stat1 4/2/2004 2/1/2003 -426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0
yyy stat1 2/2/2003 2/2/2004 365
yyy stat1 2/2/2004 2/2/2003 -365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

To that query I added one more field ( a select
subquery where find the minimum date that is
larger than t1 date)

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status A
ND t3.StatusDate>t1.StatusDate) AS NextStatusDate
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status = t2.Status)
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate, t2.StatusDate;

producing:

"E" "S" t1StatusDate t2StatusDate CycleTime NextStatusDate
xxx stat1 1/3/2002 1/3/2002 0 2/1/2003
xxx stat1 1/3/2002 2/1/2003 394 2/1/2003
xxx stat1 1/3/2002 4/2/2004 820 2/1/2003
xxx stat1 2/1/2003 1/3/2002 -394 4/2/2004
xxx stat1 2/1/2003 2/1/2003 0 4/2/2004
xxx stat1 2/1/2003 4/2/2004 426 4/2/2004
xxx stat1 4/2/2004 1/3/2002 -820
xxx stat1 4/2/2004 2/1/2003 -426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0 2/2/2004
yyy stat1 2/2/2003 2/2/2004 365 2/2/2004
yyy stat1 2/2/2004 2/2/2003 -365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

it looks like for any Equipment#/Status/tStatusDate,
it is selecting the correct "NextStatusDate."

Next, changed query to filter out only the records where
t2StatusDate = NextStatusDate
and saved as "qryStatus"

SELECT
t1.[Equipment#], t1.Status,
t1.StatusDate,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate) AS NextStatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status = t2.Status)
WHERE ((((SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate))=[t2].[StatusDate]))
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate;

qryStatus produced:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 2/1/2003 4/2/2004 426
yyy stat1 2/2/2003 2/2/2004 365

close..but one more query:

SELECT
tblStatus.[Equipment#],
tblStatus.Status,
tblStatus.StatusDate,
qryStatus.NextStatusDate,
qryStatus.CycleTime
FROM tblStatus LEFT JOIN qryStatus
ON (tblStatus.StatusDate = qryStatus.StatusDate)
AND (tblStatus.Status = qryStatus.Status)
AND (tblStatus.[Equipment#] = qryStatus.[Equipment#])
ORDER BY tblStatus.[Equipment#], tblStatus.Status, tblStatus.StatusDate;

producing:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 2/1/2003 4/2/2004 426
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004 365
yyy stat1 2/2/2004
yyy stat2 1/3/2003

Again, apologies for butting in.
Sometimes it has helped me when
posters have broken down solutions
like the above. Maybe it will help you.

Good luck,

Gary Walter


.
Click to expand...
Click to expand...
 
Gary,
I got as far as the first query, and I'm stuck again on
the "Min" subquery. I can't figure out what your t3 is,
and what the right syntax is.

I've used queries instead of tables for the source of the
data, I think this is OK.

Here is the SQL that I have so far, and note that I have
taken this from my actual database so that the generic
names I used before are changed to the actual names in the
database:
So tblStatus = BH-Freeze Date Query.
Equipment# = Vessel Used
Status Date = Freeze Date
and also, the query I did got rid of all the alternate
status, so you don't need to worry about the status
column, just freeze date and vessel used.
Click to expand...
q1, q2, and q3 (originally t1, t2, t3)
are just aliases for the same query
(originally for the same table).


SELECT
q1.[Vessel Used],
q1.[Freeze Date],
(SELECT Min(q3.[Freeze Date])
From [BH-Freeze Date Query] As q3
WHERE q3.[Vessel Used]=q1.[Vessel Used]
AND q3.[Freeze Date]>q1.[Freeze Date]) AS NextFreezeDate,
[q2].[Freeze Date]-[q1].[Freeze Date] AS CycleTime
FROM [BH-Freeze Date Query] AS q1
INNER JOIN
[BH-Freeze Date Query] AS q2
ON (q1.[Vessel Used] = q2.[Vessel Used])
WHERE ((((SELECT Min(q3.[Freeze Date])
From [BH-Freeze Date Query] As q3
WHERE q3.[Vessel Used]=q1.[Vessel Used]
AND q3.[Freeze Date]>q1.[Freeze Date]))=[q2].[Freeze Date]))
ORDER BY q1.[Vessel Used], q1.[Freeze Date];
Click to expand...
Hi Todd,

I'm going to try to write this out in my own "words"
because I remember having trouble with correlated
subqueries also.

In query designer you added your query twice
and joined the 2 instances of the query on [Vessel Used].

If you right-mouse click on the left instance and choose
Properties, you can change its alias to "q1"

Doing likewise with the right one, you can change
its alias to "q2"

If you were to run the query now, we could think
of the "step-by-step" process being something like:

-look at the first record in q1
return it and the first record in q2 where [Vessel Used] match
return it and the next record in q2 where [Vessel Used] match
{continue until no more matches}
-look at the next record in q1
return it and the first record in q2 where [Vessel Used] match
return it and the next record in q2 where [Vessel Used] match
{continue until no more matches}
-look at the next record in q1
return it and the first record in q2 where [Vessel Used] match
return it and the next record in q2 where [Vessel Used] match
{continue until no more matches}
-{continue until no more q1 records}

But we want q2 to provide only the "next" [Freeze Date]
for a given [Vessel Used] in q1 (so we can compute
Cycle Time).

The first record in q1 has a specific [Vessel Used] (say "xxx")
and a specific [Freeze Date] (say 1/3/2002)

Given those 2 *specific* values, you probably would not
have any problem writing a separate query that finds the
minimum [Freeze Date] in your original query
[BH-Freeze Date Query] for [Vessel Used]="xxx"
that is later (greater than) 1/3/2002.

Start a new query.
Add your query [BH-Freeze Date Query] once
and change its alias to "q3".
Drag and drop [Freeze Date] (twice) and [Vessel Used]
down into separate columns in the the query grid.
Click on the Sigma icon to change to a groupby query.
Change your 3 grid columns to look like:

Field: [Freeze Date] [Freeze Date] [Vessel Used]
Table: q3 q3 q3
Total: Min Where Where
Sort:
Show: checked
Criteria: >#1/3/2002# "xxx"

SELECT Min(q3.[Freeze Date])
FROM [BH-Freeze Date Query] AS q3
WHERE (((q3.[Vessel Used])="xxx")
AND ((q3.[Freeze Date])>#1/3/2002#));

You just constructed your "uncorrelated" subquery.

Back to our "step-by-step" query,
each time we look at a record in q1,
we want to compute this minimum date
using q1's [Vessel Used] and [Freeze Date].

So...we *correlate* it back substituting
q1.[Vessel Used] for "xxx"
and q1.[Freeze Date] for #1/3/2002#

In our "step-by-step" query,
we type the following in a Field row
of a separate column (all on one line):

(SELECT Min(q3.[Freeze Date])
FROM [BH-Freeze Date Query] AS q3
WHERE (((q3.[Vessel Used])=q1.[Vessel Used])
AND ((q3.[Freeze Date])>q1.[Freeze Date])))

So now, for each q1 record we look at,
we compute a Min [Freeze Date] that
we want to use in our computation of
Cycle Time (defining the only q2 record we
want to look at).

So in the Criteria row under this subquery
column in the grid, we type in

[q2].[Freeze Date]

Maybe that helps explain it better?

Good luck,

Gary Walter
 
THANK YOU, THANK YOU, THANK YOU.
I was banging my head against a wall yesterday for HOURS
trying to figure this out.
I really appreciate your help.
Your explanation is about 10 trillion times better than
anything I've gotten from any reference book or other
source. I think I finally understand the query structure
now, and I should be able to apply this to other query
problems that I have.
THANKS AGAIN.
-----Original Message-----

Gary,
I got as far as the first query, and I'm stuck again on
the "Min" subquery. I can't figure out what your t3 is,
and what the right syntax is.

I've used queries instead of tables for the source of the
data, I think this is OK.

Here is the SQL that I have so far, and note that I have
taken this from my actual database so that the generic
names I used before are changed to the actual names in the
database:
So tblStatus = BH-Freeze Date Query.
Equipment# = Vessel Used
Status Date = Freeze Date
and also, the query I did got rid of all the alternate
status, so you don't need to worry about the status
column, just freeze date and vessel used.
Click to expand...
q1, q2, and q3 (originally t1, t2, t3)
are just aliases for the same query
(originally for the same table).

I believe this is what you wanted
(it has been a long day and I don't
have much "bandwidth" left, so I
could easily be wrong...)

SELECT
q1.[Vessel Used],
q1.[Freeze Date],
(SELECT Min(q3.[Freeze Date])
From [BH-Freeze Date Query] As q3
WHERE q3.[Vessel Used]=q1.[Vessel Used]
AND q3.[Freeze Date]>q1.[Freeze Date]) AS NextStatusDate,
[q2].[Freeze Date]-[q1].[Freeze Date] AS CycleTime
FROM [BH-Freeze Date Query] AS q1
INNER JOIN
[BH-Freeze Date Query] AS q2
ON (q1.[Vessel Used] = q2.[Vessel Used])
WHERE ((((SELECT Min(q3.[Freeze Date])
From [BH-Freeze Date Query] As q3
WHERE q3.[Vessel Used]=q1.[Vessel Used]
AND q3.[Freeze Date]>q1.[Freeze Date]))=[q2].[Freeze Date]))
ORDER BY q1.[Vessel Used], q1.[Freeze Date];


SQL Statement:
SELECT [BH-Freeze Date Query].[Vessel Used], [BH-Freeze
Date Query].[Freeze Date], [BH-Freeze Date Query_1].
[Freeze Date], [BH-Freeze Date Query_1.Freeze Date]-[BH-
Freeze Date Query.Freeze Date] AS CycleTime
FROM [BH-Freeze Date Query] INNER JOIN [BH-Freeze Date
Query] AS [BH-Freeze Date Query_1] ON [BH-Freeze Date
Query].[Vessel Used] = [BH-Freeze Date Query_1].[Vessel
Used]
GROUP BY [BH-Freeze Date Query].[Vessel Used], [BH- Freeze
Date Query].[Freeze Date], [BH-Freeze Date Query_1].
[Freeze Date], [BH-Freeze Date Query_1.Freeze Date]-[BH-
Freeze Date Query.Freeze Date]
ORDER BY [BH-Freeze Date Query].[Vessel Used], [BH- Freeze
Date Query].[Freeze Date], [BH-Freeze Date Query_1].
[Freeze Date];

Can you help me figure out the syntax for the "Min"
subquery?

Thanks,
Todd

-----Original Message-----

This one has me stumped.

I have a table which has this basic structure.

Equipment#,Status,StatusDate
xxx,stat1,2/1/03
yyy,stat2,1/3/03
xxx,stat2,1/4/03
xxx,stat1,1/3/02
yyy,stat1,2/2/03
xxx,stat1,4/2/04
yyy,stat1,2/2/04

What I want to do is to create a query to return the time
between status events (CycleTime) for a particular piece
of equipment.

So the resulting query for the example above would look
like this:
Equipment#,status,StatusDate,Next:Status Date,CycleTime
xxx, stat1, 1/3/02, 2/1/03, 393 days
xxx, stat1, 2/1/03, 4/2/03, 61 days
xxx, stat1, 4/2/03, null, null
xxx, stat2, 1/4/03, null, null
yyy, stat1, 2/2/03, 2/2/04, 365 days
yyy, stat2, 1/3/03, null, null

How would I do this? My stumbling block is trying to
figure out how to identify the "Next" status date and
return it.


Hi Todd,

I'm sure Brian's solution is probably right on,
so with apologies to Brian for butting in,
but maybe a different way
at looking through the process...


assumed "tblStatus" as name of your table
and sorted them by "E, S, SD"

Equipment# Status StatusDate nextwouldbe
xxx stat1 1/3/2002 2/1/2003
xxx stat1 2/1/2003 4/2/2004
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004
yyy stat1 2/2/2004
yyy stat2 1/3/2003

so it was easier to see what your
"next" would be.

Started with a simple query where
brought the table in twice.

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#])
AND (t1.Status = t2.Status)
ORDER BY t1.[Equipment#],
t1.Status, t1.StatusDate, t2.StatusDate;

Equipment# Status t1StatusDate t2StatusDate CycleTime
xxx stat1 1/3/2002 1/3/2002 0
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 1/3/2002 4/2/2004 820
xxx stat1 2/1/2003 1/3/2002 - 394
xxx stat1 2/1/2003 2/1/2003 0
xxx stat1 2/1/2003 4/2/2004 426
xxx stat1 4/2/2004 1/3/2002 - 820
xxx stat1 4/2/2004 2/1/2003 - 426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0
yyy stat1 2/2/2003 2/2/2004 365
yyy stat1 2/2/2004 2/2/2003 - 365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

To that query I added one more field ( a select
subquery where find the minimum date that is
larger than t1 date)

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status A
ND t3.StatusDate>t1.StatusDate) AS NextStatusDate
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status = t2.Status)
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate, t2.StatusDate;

producing:

"E" "S" t1StatusDate t2StatusDate CycleTime NextStatusDate
xxx stat1 1/3/2002 1/3/2002 0 2/1/2003
xxx stat1 1/3/2002 2/1/2003 394 2/1/2003
xxx stat1 1/3/2002 4/2/2004 820 2/1/2003
xxx stat1 2/1/2003 1/3/2002 -394 4/2/2004
xxx stat1 2/1/2003 2/1/2003 0 4/2/2004
xxx stat1 2/1/2003 4/2/2004 426 4/2/2004
xxx stat1 4/2/2004 1/3/2002 -820
xxx stat1 4/2/2004 2/1/2003 -426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0 2/2/2004
yyy stat1 2/2/2003 2/2/2004 365 2/2/2004
yyy stat1 2/2/2004 2/2/2003 -365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

it looks like for any Equipment#/Status/tStatusDate,
it is selecting the correct "NextStatusDate."

Next, changed query to filter out only the records where
t2StatusDate = NextStatusDate
and saved as "qryStatus"

SELECT
t1.[Equipment#], t1.Status,
t1.StatusDate,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate) AS NextStatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status = t2.Status)
WHERE ((((SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate))=[t2].[StatusDate]))
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate;

qryStatus produced:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 2/1/2003 4/2/2004 426
yyy stat1 2/2/2003 2/2/2004 365

close..but one more query:

SELECT
tblStatus.[Equipment#],
tblStatus.Status,
tblStatus.StatusDate,
qryStatus.NextStatusDate,
qryStatus.CycleTime
FROM tblStatus LEFT JOIN qryStatus
ON (tblStatus.StatusDate = qryStatus.StatusDate)
AND (tblStatus.Status = qryStatus.Status)
AND (tblStatus.[Equipment#] = qryStatus.[Equipment#])
ORDER BY tblStatus.[Equipment#], tblStatus.Status, tblStatus.StatusDate;

producing:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 2/1/2003 4/2/2004 426
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004 365
yyy stat1 2/2/2004
yyy stat2 1/3/2003

Again, apologies for butting in.
Sometimes it has helped me when
posters have broken down solutions
like the above. Maybe it will help you.

Good luck,

Gary Walter


.
Click to expand...
Click to expand...


.
Click to expand...
 
Kudos to Gary clearing things up for you.

If it's still of interest/use to you, I realized the first query in my
original post should have been:

SELECT
[Your Table].[Equipment#],
[Your Table].[Status],
[Your Table].[StatusDate],
(SELECT TOP 1
[Self].[StatusDate]
FROM
[Your Table] AS [Self]
WHERE
[Self].[Equipment#] = [Your Table].[Equipment#]
AND
[Self].[Status] = [Your Table].[Status]
AND
[Self].[StatusDate] > [Your Table].[StatusDate]
ORDER BY
[Self].[StatusDate]) AS [NextStatusDate]

In other words, the original query was missing the

[Self].[StatusDate] > [Your Table].[StatusDate]

in the WHERE clause.

Hope your head's OK.

Todd said:
THANK YOU, THANK YOU, THANK YOU.
I was banging my head against a wall yesterday for HOURS
trying to figure this out.
I really appreciate your help.
Your explanation is about 10 trillion times better than
anything I've gotten from any reference book or other
source. I think I finally understand the query structure
now, and I should be able to apply this to other query
problems that I have.
THANKS AGAIN.
-----Original Message-----

Gary,
I got as far as the first query, and I'm stuck again on
the "Min" subquery. I can't figure out what your t3 is,
and what the right syntax is.

I've used queries instead of tables for the source of the
data, I think this is OK.

Here is the SQL that I have so far, and note that I have
taken this from my actual database so that the generic
names I used before are changed to the actual names in the
database:
So tblStatus = BH-Freeze Date Query.
Equipment# = Vessel Used
Status Date = Freeze Date
and also, the query I did got rid of all the alternate
status, so you don't need to worry about the status
column, just freeze date and vessel used.
Click to expand...
q1, q2, and q3 (originally t1, t2, t3)
are just aliases for the same query
(originally for the same table).

I believe this is what you wanted
(it has been a long day and I don't
have much "bandwidth" left, so I
could easily be wrong...)

SELECT
q1.[Vessel Used],
q1.[Freeze Date],
(SELECT Min(q3.[Freeze Date])
From [BH-Freeze Date Query] As q3
WHERE q3.[Vessel Used]=q1.[Vessel Used]
AND q3.[Freeze Date]>q1.[Freeze Date]) AS NextStatusDate,
[q2].[Freeze Date]-[q1].[Freeze Date] AS CycleTime
FROM [BH-Freeze Date Query] AS q1
INNER JOIN
[BH-Freeze Date Query] AS q2
ON (q1.[Vessel Used] = q2.[Vessel Used])
WHERE ((((SELECT Min(q3.[Freeze Date])
From [BH-Freeze Date Query] As q3
WHERE q3.[Vessel Used]=q1.[Vessel Used]
AND q3.[Freeze Date]>q1.[Freeze Date]))=[q2].[Freeze Date]))
ORDER BY q1.[Vessel Used], q1.[Freeze Date];


SQL Statement:
SELECT [BH-Freeze Date Query].[Vessel Used], [BH-Freeze
Date Query].[Freeze Date], [BH-Freeze Date Query_1].
[Freeze Date], [BH-Freeze Date Query_1.Freeze Date]-[BH-
Freeze Date Query.Freeze Date] AS CycleTime
FROM [BH-Freeze Date Query] INNER JOIN [BH-Freeze Date
Query] AS [BH-Freeze Date Query_1] ON [BH-Freeze Date
Query].[Vessel Used] = [BH-Freeze Date Query_1].[Vessel
Used]
GROUP BY [BH-Freeze Date Query].[Vessel Used], [BH- Freeze
Date Query].[Freeze Date], [BH-Freeze Date Query_1].
[Freeze Date], [BH-Freeze Date Query_1.Freeze Date]-[BH-
Freeze Date Query.Freeze Date]
ORDER BY [BH-Freeze Date Query].[Vessel Used], [BH- Freeze
Date Query].[Freeze Date], [BH-Freeze Date Query_1].
[Freeze Date];

Can you help me figure out the syntax for the "Min"
subquery?

Thanks,
Todd


-----Original Message-----

This one has me stumped.

I have a table which has this basic structure.

Equipment#,Status,StatusDate
xxx,stat1,2/1/03
yyy,stat2,1/3/03
xxx,stat2,1/4/03
xxx,stat1,1/3/02
yyy,stat1,2/2/03
xxx,stat1,4/2/04
yyy,stat1,2/2/04

What I want to do is to create a query to return the
time
between status events (CycleTime) for a particular piece
of equipment.

So the resulting query for the example above would look
like this:
Equipment#,status,StatusDate,Next:Status Date,CycleTime
xxx, stat1, 1/3/02, 2/1/03, 393 days
xxx, stat1, 2/1/03, 4/2/03, 61 days
xxx, stat1, 4/2/03, null, null
xxx, stat2, 1/4/03, null, null
yyy, stat1, 2/2/03, 2/2/04, 365 days
yyy, stat2, 1/3/03, null, null

How would I do this? My stumbling block is trying to
figure out how to identify the "Next" status date and
return it.


Hi Todd,

I'm sure Brian's solution is probably right on,
so with apologies to Brian for butting in,
but maybe a different way
at looking through the process...


assumed "tblStatus" as name of your table
and sorted them by "E, S, SD"

Equipment# Status StatusDate nextwouldbe
xxx stat1 1/3/2002 2/1/2003
xxx stat1 2/1/2003 4/2/2004
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004
yyy stat1 2/2/2004
yyy stat2 1/3/2003

so it was easier to see what your
"next" would be.

Started with a simple query where
brought the table in twice.

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#])
AND (t1.Status = t2.Status)
ORDER BY t1.[Equipment#],
t1.Status, t1.StatusDate, t2.StatusDate;

Equipment# Status t1StatusDate t2StatusDate CycleTime
xxx stat1 1/3/2002 1/3/2002 0
xxx stat1 1/3/2002 2/1/2003 394
xxx stat1 1/3/2002 4/2/2004 820
xxx stat1 2/1/2003 1/3/2002 - 394
xxx stat1 2/1/2003 2/1/2003 0
xxx stat1 2/1/2003 4/2/2004 426
xxx stat1 4/2/2004 1/3/2002 - 820
xxx stat1 4/2/2004 2/1/2003 - 426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0
yyy stat1 2/2/2003 2/2/2004 365
yyy stat1 2/2/2004 2/2/2003 - 365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

To that query I added one more field ( a select
subquery where find the minimum date that is
larger than t1 date)

SELECT
t1.[Equipment#],
t1.Status,
t1.StatusDate AS t1StatusDate,
t2.StatusDate AS t2StatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status A
ND t3.StatusDate>t1.StatusDate) AS NextStatusDate
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status =
t2.Status)
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate,
t2.StatusDate;

producing:

"E" "S" t1StatusDate t2StatusDate CycleTime
NextStatusDate
xxx stat1 1/3/2002 1/3/2002 0
2/1/2003
xxx stat1 1/3/2002 2/1/2003 394
2/1/2003
xxx stat1 1/3/2002 4/2/2004 820
2/1/2003
xxx stat1 2/1/2003 1/3/2002 -394
4/2/2004
xxx stat1 2/1/2003 2/1/2003 0
4/2/2004
xxx stat1 2/1/2003 4/2/2004 426
4/2/2004
xxx stat1 4/2/2004 1/3/2002 -820
xxx stat1 4/2/2004 2/1/2003 -426
xxx stat1 4/2/2004 4/2/2004 0
xxx stat2 1/4/2003 1/4/2003 0
yyy stat1 2/2/2003 2/2/2003 0
2/2/2004
yyy stat1 2/2/2003 2/2/2004 365
2/2/2004
yyy stat1 2/2/2004 2/2/2003 -365
yyy stat1 2/2/2004 2/2/2004 0
yyy stat2 1/3/2003 1/3/2003 0

it looks like for any Equipment#/Status/tStatusDate,
it is selecting the correct "NextStatusDate."

Next, changed query to filter out only the records where
t2StatusDate = NextStatusDate
and saved as "qryStatus"

SELECT
t1.[Equipment#], t1.Status,
t1.StatusDate,
(SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate) AS NextStatusDate,
[t2].[StatusDate]-[t1].[StatusDate] AS CycleTime
FROM tblStatus AS t1 INNER JOIN tblStatus AS t2
ON (t1.[Equipment#] = t2.[Equipment#]) AND (t1.Status =
t2.Status)
WHERE ((((SELECT Min(t3.StatusDate) From tblStatus As t3
WHERE t3.[Equipment#]=t1.[Equipment#]
AND t3.Status=t1.Status
AND t3.StatusDate>t1.StatusDate))=[t2].[StatusDate]))
ORDER BY t1.[Equipment#], t1.Status, t1.StatusDate;

qryStatus produced:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003
394
xxx stat1 2/1/2003 4/2/2004
426
yyy stat1 2/2/2003 2/2/2004
365

close..but one more query:

SELECT
tblStatus.[Equipment#],
tblStatus.Status,
tblStatus.StatusDate,
qryStatus.NextStatusDate,
qryStatus.CycleTime
FROM tblStatus LEFT JOIN qryStatus
ON (tblStatus.StatusDate = qryStatus.StatusDate)
AND (tblStatus.Status = qryStatus.Status)
AND (tblStatus.[Equipment#] = qryStatus.[Equipment#])
ORDER BY tblStatus.[Equipment#], tblStatus.Status,
tblStatus.StatusDate;

producing:

Equipment# Status StatusDate NextStatusDate CycleTime
xxx stat1 1/3/2002 2/1/2003
394
xxx stat1 2/1/2003 4/2/2004
426
xxx stat1 4/2/2004
xxx stat2 1/4/2003
yyy stat1 2/2/2003 2/2/2004
365
yyy stat1 2/2/2004
yyy stat2 1/3/2003

Again, apologies for butting in.
Sometimes it has helped me when
posters have broken down solutions
like the above. Maybe it will help you.

Good luck,

Gary Walter


.
Click to expand...


.
Click to expand...
Click to expand...
 
Todd said:
THANK YOU
Click to expand...

Hi Todd,

One final thing that I should have mentioned.

We put the correlation subquery in the SELECT
clause just to show what we were getting as we
constructed the query. And in the end set a criteria
of it being equal to q2.FreezeDate to get the
NextFreezeDate for our calc.

But...we could just as easily brought
(and typically would bring) q2.FreezeDate
down to the grid,
set its *alias* as "NextFreezeDate," then used the
correlation subquery as the *criteria* for this field.

One advantage is that using a group by/totals subquery
in the SELECT clause can make your query be
non-updateable. But sticking it in the WHERE clause
you still get an updateble query.

That wasn't important in this case, but I thought
I should mention it.

Good luck,

Gary Walter
 
Back
Top