Previous opr date calculation help

C

Cam

Hello,

I have the following query that I need to calculate the number of days at
each operation. Unfortunately, the data calculation is from a previous
operation date. I don't know how to achieve this.

Sample date:
Order# Part# Opr ProdStrtDate CompDate
456000 A3633 10 2/10/09 2/13/09
456000 A3633 20 2/10/09 2/14/09
456000 A3633 30 2/10/09 2/16/09
456000 A3633 40 2/10/09 2/20/09
456000 A3633 50 2/10/09 2/22/09

Result need:
Order# Part# Opr ProdStrtDate CompDate DaysComp
456000 A3633 10 2/10/09 2/13/09 3
(2/13-2/10)
456000 A3633 20 2/10/09 2/14/09 1
(2/14-2/13)
456000 A3633 30 2/10/09 2/16/09 2
(2/16-2/14)
456000 A3633 40 2/10/09 2/20/09 4
(2/20-2/16)
456000 A3633 50 2/10/09 2/22/09 2
(2/22-2/20)

where the opr 10 is compDate minus ProdStrtDate from opr 10 only. The rest
of the calculation is from previous records. Thank you for any help.
 
M

Marshall Barton

Cam said:
I have the following query that I need to calculate the number of days at
each operation. Unfortunately, the data calculation is from a previous
operation date. I don't know how to achieve this.

Sample date:
Order# Part# Opr ProdStrtDate CompDate
456000 A3633 10 2/10/09 2/13/09
456000 A3633 20 2/10/09 2/14/09
456000 A3633 30 2/10/09 2/16/09
456000 A3633 40 2/10/09 2/20/09
456000 A3633 50 2/10/09 2/22/09

Result need:
Order# Part# Opr ProdStrtDate CompDate DaysComp
456000 A3633 10 2/10/09 2/13/09 3
(2/13-2/10)
456000 A3633 20 2/10/09 2/14/09 1
(2/14-2/13)
456000 A3633 30 2/10/09 2/16/09 2
(2/16-2/14)
456000 A3633 40 2/10/09 2/20/09 4
(2/20-2/16)
456000 A3633 50 2/10/09 2/22/09 2
(2/22-2/20)

where the opr 10 is compDate minus ProdStrtDate from opr 10 only. The rest
of the calculation is from previous records.
Click to expand...


SELECT T.[Order#], T.[Part#], T.Opr, T.[ProdStrtDate],
T.CompDate,
DateDiff("d", Nz((SELECT Max(X.CompDate)
FROM table As X
WHERE X.CompDate < T.CompDate),
T.ProdStrtDate), T.CompDate)
As DaysComp
FROM table As T
ORDER BY T.CompDate
 
C

Cam

Thank you, Marshall.

I just checked several Order# at the opr and it is returning a lot of zero,
it be a decimal point numeric value. I forgot to mention, but not sure if it
is the problem, the date fields are in Date/Time stamp like 2/12/2008 8:18:52
AM.

Marshall Barton said:
Cam said:
I have the following query that I need to calculate the number of days at
each operation. Unfortunately, the data calculation is from a previous
operation date. I don't know how to achieve this.

Sample date:
Order# Part# Opr ProdStrtDate CompDate
456000 A3633 10 2/10/09 2/13/09
456000 A3633 20 2/10/09 2/14/09
456000 A3633 30 2/10/09 2/16/09
456000 A3633 40 2/10/09 2/20/09
456000 A3633 50 2/10/09 2/22/09

Result need:
Order# Part# Opr ProdStrtDate CompDate DaysComp
456000 A3633 10 2/10/09 2/13/09 3
(2/13-2/10)
456000 A3633 20 2/10/09 2/14/09 1
(2/14-2/13)
456000 A3633 30 2/10/09 2/16/09 2
(2/16-2/14)
456000 A3633 40 2/10/09 2/20/09 4
(2/20-2/16)
456000 A3633 50 2/10/09 2/22/09 2
(2/22-2/20)

where the opr 10 is compDate minus ProdStrtDate from opr 10 only. The rest
of the calculation is from previous records.
Click to expand...


SELECT T.[Order#], T.[Part#], T.Opr, T.[ProdStrtDate],
T.CompDate,
DateDiff("d", Nz((SELECT Max(X.CompDate)
FROM table As X
WHERE X.CompDate < T.CompDate),
T.ProdStrtDate), T.CompDate)
As DaysComp
FROM table As T
ORDER BY T.CompDate
Click to expand...
 
M

Marshall Barton

If you want the differences to be based on the order number,
change the where clause to:

WHERE X.CompDate < T.CompDate And X.[Order#] = T.[Order#]

I do not understand what you are driving at when you say:
"it be a decimal point numeric value"
--
Marsh
MVP [MS Access]

I just checked several Order# at the opr and it is returning a lot of zero,
it be a decimal point numeric value. I forgot to mention, but not sure if it
is the problem, the date fields are in Date/Time stamp like 2/12/2008 8:18:52
AM.

Marshall Barton said:
Cam said:
I have the following query that I need to calculate the number of days at
each operation. Unfortunately, the data calculation is from a previous
operation date. I don't know how to achieve this.

Sample date:
Order# Part# Opr ProdStrtDate CompDate
456000 A3633 10 2/10/09 2/13/09
456000 A3633 20 2/10/09 2/14/09
456000 A3633 30 2/10/09 2/16/09
456000 A3633 40 2/10/09 2/20/09
456000 A3633 50 2/10/09 2/22/09

Result need:
Order# Part# Opr ProdStrtDate CompDate DaysComp
456000 A3633 10 2/10/09 2/13/09 3
(2/13-2/10)
456000 A3633 20 2/10/09 2/14/09 1
(2/14-2/13)
456000 A3633 30 2/10/09 2/16/09 2
(2/16-2/14)
456000 A3633 40 2/10/09 2/20/09 4
(2/20-2/16)
456000 A3633 50 2/10/09 2/22/09 2
(2/22-2/20)

where the opr 10 is compDate minus ProdStrtDate from opr 10 only. The rest
of the calculation is from previous records.
Click to expand...


SELECT T.[Order#], T.[Part#], T.Opr, T.[ProdStrtDate],
T.CompDate,
DateDiff("d", Nz((SELECT Max(X.CompDate)
FROM table As X
WHERE X.CompDate < T.CompDate),
T.ProdStrtDate), T.CompDate)
As DaysComp
FROM table As T
ORDER BY T.CompDate
Click to expand...
Click to expand...
 
C

Cam

Marshall,

I replaced the where condition and it keep giving me missing operator. This
is the code I have.

SELECT T.ProdOrd, T.Item, T.OperNumb, T.ProdStartDate, T.CompDate,
T.OprCompDate, DateDiff("d",Nz((SELECT Max(X.OprCompDate)
FROM ProdOrdClosedOpr As X
WHERE X.OprCompDate < T.OprCompDate AND X.[ProdOrd] = T.[ProdOrd] AS DaysComp
FROM ProdOrdClosedOpr AS T
ORDER BY T.ProdOrd DESC , T.OprCompDate;

I renamed the fields to actual name.
Also, when I said decimal I meant to say the number of days calculation
should return decimal point cause it is the taking the different of Date/Time
from current date to previous operation date in Date/Time.

Marshall Barton said:
If you want the differences to be based on the order number,
change the where clause to:

WHERE X.CompDate < T.CompDate And X.[Order#] = T.[Order#]

I do not understand what you are driving at when you say:
"it be a decimal point numeric value"
--
Marsh
MVP [MS Access]

I just checked several Order# at the opr and it is returning a lot of zero,
it be a decimal point numeric value. I forgot to mention, but not sure if it
is the problem, the date fields are in Date/Time stamp like 2/12/2008 8:18:52
AM.

Marshall Barton said:
Cam wrote:
I have the following query that I need to calculate the number of days at
each operation. Unfortunately, the data calculation is from a previous
operation date. I don't know how to achieve this.

Sample date:
Order# Part# Opr ProdStrtDate CompDate
456000 A3633 10 2/10/09 2/13/09
456000 A3633 20 2/10/09 2/14/09
456000 A3633 30 2/10/09 2/16/09
456000 A3633 40 2/10/09 2/20/09
456000 A3633 50 2/10/09 2/22/09

Result need:
Order# Part# Opr ProdStrtDate CompDate DaysComp
456000 A3633 10 2/10/09 2/13/09 3
(2/13-2/10)
456000 A3633 20 2/10/09 2/14/09 1
(2/14-2/13)
456000 A3633 30 2/10/09 2/16/09 2
(2/16-2/14)
456000 A3633 40 2/10/09 2/20/09 4
(2/20-2/16)
456000 A3633 50 2/10/09 2/22/09 2
(2/22-2/20)

where the opr 10 is compDate minus ProdStrtDate from opr 10 only. The rest
of the calculation is from previous records.


SELECT T.[Order#], T.[Part#], T.Opr, T.[ProdStrtDate],
T.CompDate,
DateDiff("d", Nz((SELECT Max(X.CompDate)
FROM table As X
WHERE X.CompDate < T.CompDate),
T.ProdStrtDate), T.CompDate)
As DaysComp
FROM table As T
ORDER BY T.CompDate
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

Cam said:
I replaced the where condition and it keep giving me missing operator. This
is the code I have.

SELECT T.ProdOrd, T.Item, T.OperNumb, T.ProdStartDate, T.CompDate,
T.OprCompDate, DateDiff("d",Nz((SELECT Max(X.OprCompDate)
FROM ProdOrdClosedOpr As X
WHERE X.OprCompDate < T.OprCompDate AND X.[ProdOrd] = T.[ProdOrd] AS DaysComp
FROM ProdOrdClosedOpr AS T
ORDER BY T.ProdOrd DESC , T.OprCompDate;

I renamed the fields to actual name.
Also, when I said decimal I meant to say the number of days calculation
should return decimal point cause it is the taking the different of Date/Time
from current date to previous operation date in Date/Time.
Click to expand...


It looks like you lost part of the subquery when you edited
the where clause.

To get the fractional part of a day, I think I would use
this:

SELECT T.ProdOrd, T.Item, T.OperNumb, T.ProdStartDate,
T.CompDate, T.OprCompDate,
DateDiff("n", Nz((SELECT Max(X.OprCompDate)
FROM ProdOrdClosedOpr As X
WHERE X.OprCompDate < T.OprCompDate
AND X.ProdOrd = T.ProdOrd),
T.ProdStrtDate), T.CompDate) / (24*60) AS DaysComp
FROM ProdOrdClosedOpr AS T
ORDER BY T.ProdOrd DESC , T.OprCompDate
 
C

Cam

Marshall,

Thanks for the help. Your last code somewhat worked except one thing. When
there are sequential operNumb with the exact date/time in OprCompDate, it
gave the wrong result. I gave the sample below of the results and should be
table. Any idea?

Returned result:
Order# Part# Opr ProdStrtDate OprCompDate
DaysComp
456000 A3633 10 2/23/09 1:51 PM 2/24/09 12:50 PM
0.96
456000 A3633 20 2/23/09 1:51 PM 2/26/09 6:31 PM
2.24
456000 A3633 30 2/23/09 1:51 PM 3/7/09 12:35 AM
0.00
456000 A3633 40 2/23/09 1:51 PM 3/7/09 12:35 AM
0.00
456000 A3633 50 2/23/09 1:51 PM 3/7/09 12:35 AM
8.25
456000 A3633 60 2/23/09 1:51 PM 3/7/09 12:36 AM
0.00
456000 A3633 70 2/23/09 1:51 PM 3/10/09 12:08 PM
3.48
456000 A3633 80 2/23/09 1:51 PM 3/12/09 7:31 AM
1.81
456000 A3633 85 2/23/09 1:51 PM 3/17/09 1:11 PM
5.23
456000 A3633 90 2/23/09 1:51 PM 3/17/09 1:11 PM
5.23
456000 A3633 100 2/23/09 1:51 PM 3/17/09 1:11 PM
5.23
456000 A3633 110 2/23/09 1:51 PM 3/18/09 1:12 PM
1.00

Should be Results:
Order# Part# Opr ProdStrtDate OprCompDate
DaysComp
456000 A3633 10 2/23/09 1:51 PM 2/24/09 12:50 PM 0.96
456000 A3633 20 2/23/09 1:51 PM 2/26/09 6:31 PM 2.24
456000 A3633 30 2/23/09 1:51 PM 3/7/09 12:35 AM 8.25
456000 A3633 40 2/23/09 1:51 PM 3/7/09 12:35 AM 0.00
456000 A3633 50 2/23/09 1:51 PM 3/7/09 12:35 AM 0.00
456000 A3633 60 2/23/09 1:51 PM 3/7/09 12:36 AM 0.00
456000 A3633 70 2/23/09 1:51 PM 3/10/09 12:08 PM 3.48
456000 A3633 80 2/23/09 1:51 PM 3/12/09 7:31 AM
1.81
456000 A3633 85 2/23/09 1:51 PM 3/17/09 1:11 PM
5.23
456000 A3633 90 2/23/09 1:51 PM 3/17/09 1:11 PM
0.00
456000 A3633 100 2/23/09 1:51 PM 3/17/09 1:11 PM 0.00
456000 A3633 110 2/23/09 1:51 PM 3/18/09 1:12 PM 1.00


Marshall Barton said:
Cam said:
I replaced the where condition and it keep giving me missing operator. This
is the code I have.

SELECT T.ProdOrd, T.Item, T.OperNumb, T.ProdStartDate, T.CompDate,
T.OprCompDate, DateDiff("d",Nz((SELECT Max(X.OprCompDate)
FROM ProdOrdClosedOpr As X
WHERE X.OprCompDate < T.OprCompDate AND X.[ProdOrd] = T.[ProdOrd] AS DaysComp
FROM ProdOrdClosedOpr AS T
ORDER BY T.ProdOrd DESC , T.OprCompDate;

I renamed the fields to actual name.
Also, when I said decimal I meant to say the number of days calculation
should return decimal point cause it is the taking the different of Date/Time
from current date to previous operation date in Date/Time.
Click to expand...


It looks like you lost part of the subquery when you edited
the where clause.

To get the fractional part of a day, I think I would use
this:

SELECT T.ProdOrd, T.Item, T.OperNumb, T.ProdStartDate,
T.CompDate, T.OprCompDate,
DateDiff("n", Nz((SELECT Max(X.OprCompDate)
FROM ProdOrdClosedOpr As X
WHERE X.OprCompDate < T.OprCompDate
AND X.ProdOrd = T.ProdOrd),
T.ProdStrtDate), T.CompDate) / (24*60) AS DaysComp
FROM ProdOrdClosedOpr AS T
ORDER BY T.ProdOrd DESC , T.OprCompDate
Click to expand...
 
M

Marshall Barton

I suspect that what you are seeing is caused by OPR 40's
compdate being a few second's earlier than OPR 30

Try changing the OprCompDate field's Format property to
m/d/yy hh:snn:ss so you can see a more detailed version of
the dates.

Regardless if that does or does not explain what's
happening, let's try changing the query to:

SELECT T.ProdOrd, T.Item, T.OperNumb, T.ProdStartDate,
T.CompDate, T.OprCompDate,
DateDiff("n", Nz((SELECT Max(X.OprCompDate)
FROM ProdOrdClosedOpr As X
WHERE X.OprCompDate < T.OprCompDate
AND X.ProdOrd = T.ProdOrd
AND X.OperNumb < T.OperNumb),
T.ProdStrtDate), T.CompDate) / (24*60) AS DaysComp
FROM ProdOrdClosedOpr AS T
ORDER BY T.ProdOrd DESC, T.OperNumb
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date calculation from previous record? 5
Help please - Date from previous record calculation 4
time_report_date-wise_employee-wise 6
Pls help for Simple Calculation 1
Find then highlight in yellow 6
VBA Programming help 2
Need help with VBA code 0
help filtering for unique records - urgent :) 3

Top