Calculate elapsed days

[Guest]

Okay, the data I import is structure as so;

SERIAL# ITEM NUMBER WORK DATE
45612 P123456 12/7/06
45612 P123456 3/25/07
78913 P45215 1/5/04
78913 P45215 2/8/05

etc...

These are service records sorted by serial number, item number, and work
date. I need to calculate the elapsed days between replacements of like item
numbers for each serial number.

Thanks a bunch
PD

 

[John Spencer]

One method would be to use a correlated subquery.

SELECT [Serial#], [Item Number], [Work Date]
, DateDiff("d", (SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = TA.[Serial#]
AND TB.[Work Date] < TA.[Work Date]
) , [Work Date]) as ElapsedDays
FROM [YourTable] as TA

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

1. Create a query (qry_NextDate) that looks like:

SELECT Serial#, ItemNumber, Work_Date, _
(SELECT Min(Work_Date)
FROM yourTable T2
WHERE T2.Serial# = T1.Serial#
AND T2.ItemNumber = T1.ItemNumber
AND T2.Work_Date > T1.Work_Date) as NextDate
FROM yourTable T1

2. Then, use this query as the source of another query that computes the
elapsed time (use the DateDiff( ) function) . The only issue you will need
to deal with is that for the most recent date for each serial#/item number
combination, the value in NextDate will be Null, so you will need to account
for this. It might look like

SELECT Serial#, ItemNumber, Work_Date, NextDate, Datediff("d", [Work_Date],
[NextDate]
FROM qry_NextDate
WHERE NOT ISNULL(NextDate)

HTH
Dale

 

[Guest]

Thanks John,

I am not familiar with subqueries, I will learn and try.
--
PRD

One method would be to use a correlated subquery.

SELECT [Serial#], [Item Number], [Work Date]
, DateDiff("d", (SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = TA.[Serial#]
AND TB.[Work Date] < TA.[Work Date]
) , [Work Date]) as ElapsedDays
FROM [YourTable] as TA

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay, the data I import is structure as so;

SERIAL# ITEM NUMBER WORK DATE
45612 P123456 12/7/06
45612 P123456 3/25/07
78913 P45215 1/5/04
78913 P45215 2/8/05

etc...

These are service records sorted by serial number, item number, and work
date. I need to calculate the elapsed days between replacements of like
item
numbers for each serial number.

Thanks a bunch
PD

 

[Guest]

Thanks Dale,

This worked great, but I can have a growing number of replacements of a
particular item number. I know I only listed 2 instances of a replacement in
my original question. Is there a modification you can suggest?

Thanks again,
--
PRD

1. Create a query (qry_NextDate) that looks like:

SELECT Serial#, ItemNumber, Work_Date, _
(SELECT Min(Work_Date)
FROM yourTable T2
WHERE T2.Serial# = T1.Serial#
AND T2.ItemNumber = T1.ItemNumber
AND T2.Work_Date > T1.Work_Date) as NextDate
FROM yourTable T1

2. Then, use this query as the source of another query that computes the
elapsed time (use the DateDiff( ) function) . The only issue you will need
to deal with is that for the most recent date for each serial#/item number
combination, the value in NextDate will be Null, so you will need to account
for this. It might look like

SELECT Serial#, ItemNumber, Work_Date, NextDate, Datediff("d", [Work_Date],
[NextDate]
FROM qry_NextDate
WHERE NOT ISNULL(NextDate)

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

Okay, the data I import is structure as so;

SERIAL# ITEM NUMBER WORK DATE
45612 P123456 12/7/06
45612 P123456 3/25/07
78913 P45215 1/5/04
78913 P45215 2/8/05

etc...

These are service records sorted by serial number, item number, and work
date. I need to calculate the elapsed days between replacements of like item
numbers for each serial number.

Thanks a bunch
PD

 

[Guest]

John,

I have not been able to figure out how to insert the statement as a sub
query, any suggestions?

Thanks,
--
PRD

One method would be to use a correlated subquery.

SELECT [Serial#], [Item Number], [Work Date]
, DateDiff("d", (SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = TA.[Serial#]
AND TB.[Work Date] < TA.[Work Date]
) , [Work Date]) as ElapsedDays
FROM [YourTable] as TA

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay, the data I import is structure as so;

SERIAL# ITEM NUMBER WORK DATE
45612 P123456 12/7/06
45612 P123456 3/25/07
78913 P45215 1/5/04
78913 P45215 2/8/05

etc...

These are service records sorted by serial number, item number, and work
date. I need to calculate the elapsed days between replacements of like
item
numbers for each serial number.

Thanks a bunch
PD

 

[Guest]

Are you asking how to limit the results of the second query to give you only
the most recent two replacements (and the replacement interval) for a
particular serial #/Item number? If so, try:

SELECT Serial#, ItemNumber, Work_Date, NextDate, _
Datediff("d", [Work_Date], [NextDate]) as RepInterval
FROM qry_NextDate
WHERE NOT ISNULL(NextDate)
AND [NextDate] = (SELECT MAX([Work_Date])
FROM qry_NextDate q1
WHERE qry_NextDate.Serial# = q1.Serial#
AND qry_NextDate.ItemNumber = q1.ItemNumber)

HTH
Dale





--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

Thanks Dale,

This worked great, but I can have a growing number of replacements of a
particular item number. I know I only listed 2 instances of a replacement in
my original question. Is there a modification you can suggest?

Thanks again,
--
PRD

1. Create a query (qry_NextDate) that looks like:

SELECT Serial#, ItemNumber, Work_Date, _
(SELECT Min(Work_Date)
FROM yourTable T2
WHERE T2.Serial# = T1.Serial#
AND T2.ItemNumber = T1.ItemNumber
AND T2.Work_Date > T1.Work_Date) as NextDate
FROM yourTable T1

2. Then, use this query as the source of another query that computes the
elapsed time (use the DateDiff( ) function) . The only issue you will need
to deal with is that for the most recent date for each serial#/item number
combination, the value in NextDate will be Null, so you will need to account
for this. It might look like

SELECT Serial#, ItemNumber, Work_Date, NextDate, Datediff("d", [Work_Date],
[NextDate]
FROM qry_NextDate
WHERE NOT ISNULL(NextDate)

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

Okay, the data I import is structure as so;

SERIAL# ITEM NUMBER WORK DATE
45612 P123456 12/7/06
45612 P123456 3/25/07
78913 P45215 1/5/04
78913 P45215 2/8/05

etc...

These are service records sorted by serial number, item number, and work
date. I need to calculate the elapsed days between replacements of like item
numbers for each serial number.

Thanks a bunch
PD

 

[Guest]

Dale,

I want to capture all replacements of each item number.

Thanks,
--
PRD

Are you asking how to limit the results of the second query to give you only
the most recent two replacements (and the replacement interval) for a
particular serial #/Item number? If so, try:

SELECT Serial#, ItemNumber, Work_Date, NextDate, _
Datediff("d", [Work_Date], [NextDate]) as RepInterval
FROM qry_NextDate
WHERE NOT ISNULL(NextDate)
AND [NextDate] = (SELECT MAX([Work_Date])
FROM qry_NextDate q1
WHERE qry_NextDate.Serial# = q1.Serial#
AND qry_NextDate.ItemNumber = q1.ItemNumber)

HTH
Dale





--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

Thanks Dale,

This worked great, but I can have a growing number of replacements of a
particular item number. I know I only listed 2 instances of a replacement in
my original question. Is there a modification you can suggest?

Thanks again,
--
PRD

1. Create a query (qry_NextDate) that looks like:

SELECT Serial#, ItemNumber, Work_Date, _
(SELECT Min(Work_Date)
FROM yourTable T2
WHERE T2.Serial# = T1.Serial#
AND T2.ItemNumber = T1.ItemNumber
AND T2.Work_Date > T1.Work_Date) as NextDate
FROM yourTable T1

2. Then, use this query as the source of another query that computes the
elapsed time (use the DateDiff( ) function) . The only issue you will need
to deal with is that for the most recent date for each serial#/item number
combination, the value in NextDate will be Null, so you will need to account
for this. It might look like

SELECT Serial#, ItemNumber, Work_Date, NextDate, Datediff("d", [Work_Date],
[NextDate]
FROM qry_NextDate
WHERE NOT ISNULL(NextDate)

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


:

Okay, the data I import is structure as so;

SERIAL# ITEM NUMBER WORK DATE
45612 P123456 12/7/06
45612 P123456 3/25/07
78913 P45215 1/5/04
78913 P45215 2/8/05

etc...

These are service records sorted by serial number, item number, and work
date. I need to calculate the elapsed days between replacements of like item
numbers for each serial number.

Thanks a bunch
PD

 

[John Spencer]

The query string I posted was an entire query. The subquery would have been
just this portion, which you should enter into a new field

(SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = [YourTable].[Serial#]
AND TB.[Work Date] < [YourTable].[Work Date] )

If you wanted to use the entire query as posted, you would need to edit it
to use your tablename and field names and then paste it into a NEW query.
-- New Query
-- Add no tables
-- Select View SQL
-- Paste the SQL statement into the window
-- Attempt to run the statement

SELECT [Serial#], [Item Number], [Work Date]
, DateDiff("d", (SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = TA.[Serial#]
AND TB.[Work Date] < TA.[Work Date]
) , [Work Date]) as ElapsedDays
FROM [YourTable] as TA

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

I have not been able to figure out how to insert the statement as a sub
query, any suggestions?

Thanks,
--
PRD

One method would be to use a correlated subquery.

SELECT [Serial#], [Item Number], [Work Date]
, DateDiff("d", (SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = TA.[Serial#]
AND TB.[Work Date] < TA.[Work Date]
) , [Work Date]) as ElapsedDays
FROM [YourTable] as TA

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay, the data I import is structure as so;

SERIAL# ITEM NUMBER WORK DATE
45612 P123456 12/7/06
45612 P123456 3/25/07
78913 P45215 1/5/04
78913 P45215 2/8/05

etc...

These are service records sorted by serial number, item number, and
work
date. I need to calculate the elapsed days between replacements of
like
item
numbers for each serial number.

Thanks a bunch
PD

 

[Guest]

Thanks John, I did as you suggested, but I get a Syntax error with the
DateDiff function. I created a new query, no tables and edited as below:

-----
SELECT [SERIAL_NUM], [ITEM_NUM], [work_dt]
, DateDiff("d", (SELECT Max [work_dt] as Prior
FROM [Ripper_development] As TB
WHERE TB.[SERIAL_NUM] = TA.[SERIAL_NUM]
AND TB.[work_dt] < TA.[work_dt]) , [work_dt]) as ElapsedDays
FROM [Ripper_development] as TA

Any ideas? I have tried numerous variations with no luck.
--
PRD

The query string I posted was an entire query. The subquery would have been
just this portion, which you should enter into a new field

(SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = [YourTable].[Serial#]
AND TB.[Work Date] < [YourTable].[Work Date] )

If you wanted to use the entire query as posted, you would need to edit it
to use your tablename and field names and then paste it into a NEW query.
-- New Query
-- Add no tables
-- Select View SQL
-- Paste the SQL statement into the window
-- Attempt to run the statement

SELECT [Serial#], [Item Number], [Work Date]
, DateDiff("d", (SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = TA.[Serial#]
AND TB.[Work Date] < TA.[Work Date]
) , [Work Date]) as ElapsedDays
FROM [YourTable] as TA

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

I have not been able to figure out how to insert the statement as a sub
query, any suggestions?

Thanks,
--
PRD

One method would be to use a correlated subquery.

SELECT [Serial#], [Item Number], [Work Date]
, DateDiff("d", (SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = TA.[Serial#]
AND TB.[Work Date] < TA.[Work Date]
) , [Work Date]) as ElapsedDays
FROM [YourTable] as TA

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay, the data I import is structure as so;

SERIAL# ITEM NUMBER WORK DATE
45612 P123456 12/7/06
45612 P123456 3/25/07
78913 P45215 1/5/04
78913 P45215 2/8/05

etc...

These are service records sorted by serial number, item number, and
work
date. I need to calculate the elapsed days between replacements of
like
item
numbers for each serial number.

Thanks a bunch
PD

 

[John Spencer]

One you surrounded work_dt with SQUARE brackets instead of parentheses.
Also since you fields have well-structured names you can delete almost all
the other sets of brackets.

Try this variation.

SELECT SERIAL_NUM
, ITEM_NUM
, work_dt
, DateDiff("d", (SELECT Max (work_dt)
FROM Ripper_development As TB
WHERE TB.SERIAL_NUM = TA.SERIAL_NUM
AND TB.work_dt < TA.work_dt) , work_dt) as ElapsedDays
FROM Ripper_development as TA

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks John, I did as you suggested, but I get a Syntax error with the
DateDiff function. I created a new query, no tables and edited as below:

-----
SELECT [SERIAL_NUM], [ITEM_NUM], [work_dt]
, DateDiff("d", (SELECT Max [work_dt] as Prior
FROM [Ripper_development] As TB
WHERE TB.[SERIAL_NUM] = TA.[SERIAL_NUM]
AND TB.[work_dt] < TA.[work_dt]) , [work_dt]) as ElapsedDays
FROM [Ripper_development] as TA

Any ideas? I have tried numerous variations with no luck.
--
PRD

The query string I posted was an entire query. The subquery would have
been
just this portion, which you should enter into a new field

(SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = [YourTable].[Serial#]
AND TB.[Work Date] < [YourTable].[Work Date] )

If you wanted to use the entire query as posted, you would need to edit
it
to use your tablename and field names and then paste it into a NEW query.
-- New Query
-- Add no tables
-- Select View SQL
-- Paste the SQL statement into the window
-- Attempt to run the statement

SELECT [Serial#], [Item Number], [Work Date]
, DateDiff("d", (SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = TA.[Serial#]
AND TB.[Work Date] < TA.[Work Date]
) , [Work Date]) as ElapsedDays
FROM [YourTable] as TA

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

I have not been able to figure out how to insert the statement as a sub
query, any suggestions?

Thanks,
--
PRD


:

One method would be to use a correlated subquery.

SELECT [Serial#], [Item Number], [Work Date]
, DateDiff("d", (SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = TA.[Serial#]
AND TB.[Work Date] < TA.[Work Date]
) , [Work Date]) as ElapsedDays
FROM [YourTable] as TA

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay, the data I import is structure as so;

SERIAL# ITEM NUMBER WORK DATE
45612 P123456 12/7/06
45612 P123456 3/25/07
78913 P45215 1/5/04
78913 P45215 2/8/05

etc...

These are service records sorted by serial number, item number, and
work
date. I need to calculate the elapsed days between replacements of
like
item
numbers for each serial number.

Thanks a bunch
PD

 

[Dale Fye]

Then the 2nd query of my original post should give you that.

Dale,

I want to capture all replacements of each item number.

Thanks,
--
PRD

Are you asking how to limit the results of the second query to give you
only
the most recent two replacements (and the replacement interval) for a
particular serial #/Item number? If so, try:

SELECT Serial#, ItemNumber, Work_Date, NextDate, _
Datediff("d", [Work_Date], [NextDate]) as RepInterval
FROM qry_NextDate
WHERE NOT ISNULL(NextDate)
AND [NextDate] = (SELECT MAX([Work_Date])
FROM qry_NextDate q1
WHERE qry_NextDate.Serial# = q1.Serial#
AND qry_NextDate.ItemNumber = q1.ItemNumber)

HTH
Dale





--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

Thanks Dale,

This worked great, but I can have a growing number of replacements of a
particular item number. I know I only listed 2 instances of a
replacement in
my original question. Is there a modification you can suggest?

Thanks again,
--
PRD


:

1. Create a query (qry_NextDate) that looks like:

SELECT Serial#, ItemNumber, Work_Date, _
(SELECT Min(Work_Date)
FROM yourTable T2
WHERE T2.Serial# = T1.Serial#
AND T2.ItemNumber = T1.ItemNumber
AND T2.Work_Date > T1.Work_Date) as NextDate
FROM yourTable T1

2. Then, use this query as the source of another query that computes
the
elapsed time (use the DateDiff( ) function) . The only issue you
will need
to deal with is that for the most recent date for each serial#/item
number
combination, the value in NextDate will be Null, so you will need to
account
for this. It might look like

SELECT Serial#, ItemNumber, Work_Date, NextDate, Datediff("d",
[Work_Date],
[NextDate]
FROM qry_NextDate
WHERE NOT ISNULL(NextDate)

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


:

Okay, the data I import is structure as so;

SERIAL# ITEM NUMBER WORK DATE
45612 P123456 12/7/06
45612 P123456 3/25/07
78913 P45215 1/5/04
78913 P45215 2/8/05

etc...

These are service records sorted by serial number, item number, and
work
date. I need to calculate the elapsed days between replacements of
like item
numbers for each serial number.

Thanks a bunch
PD

 

[Dale Fye]

John,

In the OP, PD mentioned that he wanted to compute the days between
replacements of particular items (ItemNumber) by Serial#, so your query
needs to reference the ItemNumber field as well.

Dale

I think the problem is that for the first replacement date for any given
serial#/

One you surrounded work_dt with SQUARE brackets instead of parentheses.
Also since you fields have well-structured names you can delete almost all
the other sets of brackets.

Try this variation.

SELECT SERIAL_NUM
, ITEM_NUM
, work_dt
, DateDiff("d", (SELECT Max (work_dt)
FROM Ripper_development As TB
WHERE TB.SERIAL_NUM = TA.SERIAL_NUM
AND TB.work_dt < TA.work_dt) , work_dt) as ElapsedDays
FROM Ripper_development as TA

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Thanks John, I did as you suggested, but I get a Syntax error with the
DateDiff function. I created a new query, no tables and edited as
below:

-----
SELECT [SERIAL_NUM], [ITEM_NUM], [work_dt]
, DateDiff("d", (SELECT Max [work_dt] as Prior
FROM [Ripper_development] As TB
WHERE TB.[SERIAL_NUM] = TA.[SERIAL_NUM]
AND TB.[work_dt] < TA.[work_dt]) , [work_dt]) as ElapsedDays
FROM [Ripper_development] as TA

Any ideas? I have tried numerous variations with no luck.
--
PRD

The query string I posted was an entire query. The subquery would have
been
just this portion, which you should enter into a new field

(SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = [YourTable].[Serial#]
AND TB.[Work Date] < [YourTable].[Work Date] )

If you wanted to use the entire query as posted, you would need to edit
it
to use your tablename and field names and then paste it into a NEW
query.
-- New Query
-- Add no tables
-- Select View SQL
-- Paste the SQL statement into the window
-- Attempt to run the statement

SELECT [Serial#], [Item Number], [Work Date]
, DateDiff("d", (SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = TA.[Serial#]
AND TB.[Work Date] < TA.[Work Date]
) , [Work Date]) as ElapsedDays
FROM [YourTable] as TA

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

I have not been able to figure out how to insert the statement as a
sub
query, any suggestions?

Thanks,
--
PRD


:

One method would be to use a correlated subquery.

SELECT [Serial#], [Item Number], [Work Date]
, DateDiff("d", (SELECT Max[Work Date] as Prior
FROM [YourTable] As TB
WHERE TB.[Serial#] = TA.[Serial#]
AND TB.[Work Date] < TA.[Work Date]
) , [Work Date]) as ElapsedDays
FROM [YourTable] as TA

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay, the data I import is structure as so;

SERIAL# ITEM NUMBER WORK DATE
45612 P123456 12/7/06
45612 P123456 3/25/07
78913 P45215 1/5/04
78913 P45215 2/8/05

etc...

These are service records sorted by serial number, item number, and
work
date. I need to calculate the elapsed days between replacements of
like
item
numbers for each serial number.

Thanks a bunch
PD