Complicated Date-based Query

  • Thread starter Thread starter KERRY J
  • Start date Start date
K

KERRY J

I have a table (tblWell-Daily) that contains the daily production
volumes for a group of wells. The important fields are:
wellID (primary key)
proddate (primary key)
volume

What I would like to do is determine the number of days that a well (if
any) has been off production (ie. volume=0 ) based on the last date of
input.

The query contains parameter and expression...
LastInput: [Enter Date of Last Data Input mm/dd/yy):]
NumDays: DateDiff("y",[proddate],[LastInput])

Example data would be...
wellID___proddate______volume
well01___11/15/03________0
well01___11/14/03________0
well01___11/13/03______100
well02___11/15/03_______50
...

The query would return wellID (in this case well01), volume (from last
date of prod; in this case 100) and NumDays (in this case 2). The last
date of input in this case would be 11/15/03. If a well is producing on
11/15/03 (volume>0) such as well02, the well would be excluded from the
query results.

I have struggled with this one for several days without achieving the
desired results. My guess is that the query would include a subquery to
handle aggregate functions (ie. Max([proddate]) where volume>0). Thanks
in advance for any help!
 
Hi,


We need to rank the data by well, then it is a matter to get the minimum
rank for which we have a non zero volume. Example,


RANK wellID___proddate______volume
1 well01___11/15/03________0
2 well01___11/14/03________0
3 well01___11/13/03______100
1 well02___11/15/03_______50


with the rank computed as the prodDate value decrease, and computer "by
well". Finding the minimum rank where volume <> 0:

SELECT min(rank) As MinOfRank, wellID
FROM above
WHERE volume <> 0
GROUP BY wellID


would supply

MinOfRANK wellID
3 well01
1 well02



And then, an inner join with that query and the initial "above" will give
back the whole records:

3 well01 11/13/03 100
1 well02 11/15/03 50


and you can compute the number of non production either by rank-1, either by
DateDiff.

So, everything is simple, if we can get our hand on the query "above". Here
a possible way to do it:


SELECT COUNT(b.*) As Rank, a.wellID, a.prodDate, SUM(a.volume)
FROM myTable As a INNER JOIN myTable As b
ON a.wellID=b.wellID AND a.prodDate <= b.prodDate
GROUP BY a.wellID, a.prodDate


and another one:


SELECT a.*, (SELECT COUNT(*) FROM myTable As b
WHERE b.WellID=a.WellID and a.prodDate<=b.prodDate)
As Rank
FROM myTable As a



use the first one if there is a possibility that a given well got two
records for a day (and in that case, the sum of the volumes of these records
registered the same day have to be considered). Save the query as under the
name... above. From that point, I assume you can handle the problem.


Hoping it may help,
Vanderghast, Access MVP


KERRY J said:
I have a table (tblWell-Daily) that contains the daily production
volumes for a group of wells. The important fields are:
wellID (primary key)
proddate (primary key)
volume

What I would like to do is determine the number of days that a well (if
any) has been off production (ie. volume=0 ) based on the last date of
input.

The query contains parameter and expression...
LastInput: [Enter Date of Last Data Input mm/dd/yy):]
NumDays: DateDiff("y",[proddate],[LastInput])

Example data would be...
wellID___proddate______volume
well01___11/15/03________0
well01___11/14/03________0
well01___11/13/03______100
well02___11/15/03_______50
..

The query would return wellID (in this case well01), volume (from last
date of prod; in this case 100) and NumDays (in this case 2). The last
date of input in this case would be 11/15/03. If a well is producing on
11/15/03 (volume>0) such as well02, the well would be excluded from the
query results.

I have struggled with this one for several days without achieving the
desired results. My guess is that the query would include a subquery to
handle aggregate functions (ie. Max([proddate]) where volume>0). Thanks
in advance for any help!
Click to expand...
 
Back
Top