Calculate elapsed days

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PD - 13 Nov 2007 17:03 GMT

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

Signature

PRD

Reply to this Message

John Spencer - 13 Nov 2007 17:37 GMT

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

Signature

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;
>
[quoted text clipped - 13 lines]
> Thanks a bunch
> PD

Reply to this Message

PD - 13 Nov 2007 17:46 GMT

Thanks John,

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

Signature

PRD

> One method would be to use a correlated subquery.
>
[quoted text clipped - 23 lines]
> > Thanks a bunch
> > PD

Reply to this Message

PD - 13 Nov 2007 21:03 GMT

John,

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

Thanks,

Signature

PRD

> One method would be to use a correlated subquery.
>
[quoted text clipped - 23 lines]
> > Thanks a bunch
> > PD

Reply to this Message

John Spencer - 14 Nov 2007 12:55 GMT

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

Signature

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

> John,
>
[quoted text clipped - 32 lines]
>> > Thanks a bunch
>> > PD

Reply to this Message

PD - 14 Nov 2007 17:09 GMT

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.

Signature

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
[quoted text clipped - 56 lines]
> >> > Thanks a bunch
> >> > PD

Reply to this Message

John Spencer - 14 Nov 2007 17:25 GMT

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

Signature

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:
[quoted text clipped - 71 lines]
>> >> > Thanks a bunch
>> >> > PD

Reply to this Message

Dale Fye - 15 Nov 2007 00:22 GMT

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
[quoted text clipped - 89 lines]
>>> >> > Thanks a bunch
>>> >> > PD

Reply to this Message

Dale Fye - 13 Nov 2007 17:43 GMT

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

Signature

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;
>
[quoted text clipped - 12 lines]
> Thanks a bunch
> PD

Reply to this Message

PD - 13 Nov 2007 21:01 GMT

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,

Signature

PRD

> 1. Create a query (qry_NextDate) that looks like:
>
[quoted text clipped - 36 lines]
> > Thanks a bunch
> > PD

Reply to this Message

Dale Fye - 13 Nov 2007 21:28 GMT

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

Signature

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

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

> Thanks Dale,
>
[quoted text clipped - 44 lines]
> > > Thanks a bunch
> > > PD

Reply to this Message

PD - 13 Nov 2007 21:39 GMT

Dale,

I want to capture all replacements of each item number.

Thanks,

Signature

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
[quoted text clipped - 60 lines]
> > > > Thanks a bunch
> > > > PD

Reply to this Message

Dale Fye - 15 Nov 2007 00:13 GMT

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

> Dale,
>
[quoted text clipped - 75 lines]
>> > > > Thanks a bunch
>> > > > PD

Reply to this Message