Re: Calculate days between dates in a query
- From: Dan <Dan@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 29 Feb 2008 16:09:25 -0800
Allen,
I'm using Access 2002 SP3. The index field is [Well#]. I tried adding the
code you suggested but the query doesn't return any values for NumDays when I
do.
Dan
"Allen Browne" wrote:
Wow. I have no idea why JET would be considering a date value to be less.
than itself.
If there were 2 different fields that displayed the same dates, I could
understand that one could be fractionally different to the other due to
floating point inaccuracy, but any individual floating point value must be
equal to (not less than) itself. What version of Access is this? And what
service pack (see Help | About)?
If your table has a primary key, you might be able to work around the
problem by explicitly excluding the same record in the subquery's criteria:
(SELECT Max([All Daily Production Data].[DATE-TIME])
AS PriorDateTime
FROM [All Daily Production Data] AS Dup
WHERE ([All Daily Production Data].[Well#] = Dup.[Well#])
AND (Dup.[DATE-TIME] <
[All Daily Production Data].[DATE-TIME])
AND (Dup.ID <> [All Daily Production Data].ID)) AS PriorDT
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dan" <Dan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0013AC0A-9CFA-4787-9F18-EDFCCB02742A@xxxxxxxxxxxxxxxx
The [Date-Time] field is a Date/Time data type. I tried your query and
got
these results;
WELL# WELL NAME DATE-TIME STATUS STCODE PriorDT
11826-9 A Little Tr1 #9 3/8/2007 8:32:00 AM P
11826-9 A Little Tr1 #9 4/2/2007 3:16:00 PM P 4/2/2007 3:16:00 PM
11826-9 A Little Tr1 #9 6/4/2007 8:37:00 AM N FL 6/4/2007 8:37:00 AM
11826-9 A Little Tr1 #9 8/8/2007 9:22:00 AM P 8/8/2007 9:22:00 AM
11852-1 A Little Tr2 #1 11/8/2005 10:26:00 AM P
11852-1 A Little Tr2 #1 3/20/2006 1:29:00 PM P 3/20/2006 1:29:00 PM
11852-1 A Little Tr2 #1 5/15/2006 10:41:00 AM P 5/15/2006 10:41:00 AM
11852-1 A Little Tr2 #1 6/24/2006 8:38:00 AM N BP 6/24/2006 8:38:00 AM
11852-1 A Little Tr2 #1 6/26/2006 2:45:00 PM P 6/26/2006 2:45:00 PM
It's not returning the prior date, it's returning the current date which I
guess is why I'm getting zeros in my query.
"Allen Browne" wrote:
That sort of thing looks right.
We are assuming here that the field named [DATE-TIME] is actually a
Date/Time field (not a Text field).
Perhaps you could try this, and see if it is returning the correct date:
SELECT [All Daily Production Data].[WELL#],
[Base Wells].[WELL NAME],
[All Daily Production Data].[DATE-TIME],
[All Daily Production Data].STATUS,
[All Daily Production Data].STCODE,
(SELECT Max([All Daily Production Data].[DATE-TIME])
AS PriorDateTime
FROM [All Daily Production Data] AS Dup
WHERE [All Daily Production Data].[Well#] = Dup.[Well#]
AND Dup.[DATE-TIME] <
[All Daily Production Data].[DATE-TIME]) AS PriorDT
FROM [Base Wells] LEFT JOIN [All Daily Production Data]
ON [Base Wells].[WELL#]=[All Daily Production Data].[WELL#]
WHERE (([All Daily Production Data].STATUS IN ("P", "N"))
AND ([Base Wells].CLASS = "W"))
ORDER BY [Base Wells].[WELL NAME],
[All Daily Production Data].[DATE-TIME];
Once you get that working, you can use DateDiff() to calculate the number
of
days.
And once you get that working, you can refine it with your special
requirements to identify the last record for each well and treat it
differently.
"Dan" <Dan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:066DBDC8-6B84-48E6-8DA3-4A95C0EBBB6E@xxxxxxxxxxxxxxxx
Allen,
I tried implementing a subquery as the following;
SELECT [All Daily Production Data].[WELL#], [Base Wells].[WELL NAME],
[All
Daily Production Data].[DATE-TIME], [All Daily Production Data].STATUS,
[All
Daily Production Data].STCODE, [DATE-TIME]-(SELECT Max([All Daily
Production
Data].[DATE-TIME]) FROM [All Daily Production Data] AS Dup WHERE [All
Daily
Production Data].[Well#] = Dup.[Well#] AND Dup.[DATE-TIME] < [All Daily
Production Data].[DATE-TIME]) AS NumDays
FROM [Base Wells] LEFT JOIN [All Daily Production Data] ON [Base
Wells].[WELL#]=[All Daily Production Data].[WELL#]
WHERE ((([All Daily Production Data].STATUS)="P" Or ([All Daily
Production
Data].STATUS)="N") AND (([Base Wells].CLASS)="W"))
ORDER BY [Base Wells].[WELL NAME], [All Daily Production
Data].[DATE-TIME];
But, something is wrong in my NumDays calculation. The number of days
between records is not correct. The output looks like this;
WELL# WELL NAME DATE-TIME STATUS STCODE NumDays
11826-9 A Little Tr1 #9 3/8/2007 8:32:00 AM P
11826-9 A Little Tr1 #9 4/2/2007 3:16:00 PM P 0
11826-9 A Little Tr1 #9 6/4/2007 8:37:00 AM N FL 0
11826-9 A Little Tr1 #9 8/8/2007 9:22:00 AM P 0
11852-1 A Little Tr2 #1 11/8/2005 10:26:00 AM P
11852-1 A Little Tr2 #1 3/20/2006 1:29:00 PM P 0
11852-1 A Little Tr2 #1 5/15/2006 10:41:00 AM P 0
11852-1 A Little Tr2 #1 6/24/2006 8:38:00 AM N BP 0
11852-1 A Little Tr2 #1 6/26/2006 2:45:00 PM P 0
11852-2 A Little Tr2 #2 11/10/2005 11:58:00 AM P
11852-2 A Little Tr2 #2 11/11/2005 10:32:00 AM P 0
11852-2 A Little Tr2 #2 3/20/2006 1:29:00 PM P 0
11852-2 A Little Tr2 #2 5/15/2006 10:41:00 AM P 0
Also, I realized that for the last record for each well I need to show
the
number of days since the date of that record and the current date.
Can you help me out again? Thanks.
"Allen Browne" wrote:
See:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord
The subquery will need to return the Max([All Daily Production
Data].[DATE])
where it is the same well and a lesser date.
For this to work reliably you might need an index that prevents 2
readings
from the same well on the same date.
BTW, DATE is a reserved word and could cause problems. Use square
brackets
around the name here and it should work; in other contexts Access
might
misunderstand and use the system date instead of the field if you
don't
rename it.
"Dan" <Dan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:450166A5-A7D0-4F56-A7FC-333DD75CEA28@xxxxxxxxxxxxxxxx
I have a query that lists dates when an oil well was either put into
production or taken off production. There are several wells in the
database
and the query retrieves all the wells. The SQL for the query is as
follows;
SELECT [Base Wells].[WELL NAME], [All Daily Production Data].DATE,
[All
Daily Production Data].STATUS, [All Daily Production Data].STCODE
FROM [Base Wells] LEFT JOIN [All Daily Production Data] ON [Base
Wells].[WELL#] = [All Daily Production Data].[WELL#]
WHERE ((([All Daily Production Data].STATUS)="P" Or ([All Daily
Production
Data].STATUS)="N") AND (([Base Wells].CLASS)="W"))
ORDER BY [Base Wells].[WELL NAME], [All Daily Production Data].DATE;
I would like to add a field to the query that calculates the number
of days between each record for each well. The calculation would
have to recognize when the 'Well Name' has changed so it would not
use the Date from the previous record.
Is this possible? Thanks in advance for anyones help.
- Follow-Ups:
- Re: Calculate days between dates in a query
- From: Allen Browne
- Re: Calculate days between dates in a query
- Prev by Date: Re: Need Help with Criteria Expression
- Next by Date: Group only by certain day
- Previous by thread: Re: Need Help with Criteria Expression
- Next by thread: Re: Calculate days between dates in a query
- Index(es):
Relevant Pages
|
