Selecting first six months

  • Thread starter Thread starter Matthew Ellis
  • Start date Start date
M

Matthew Ellis

I have a database where I have thousands of wells with daily data for each month of the year. I need to run a query which will pull the first six months of data for each well. Each well could be started on any month of the year so I can't just select month 1 through six. How should I set this up. It is already setup with a lookup table for the months (not my design) and each month is given its corresponding number (1-12). I was thinking I could somehow use this table, but I am not sure how........... Any help is appreciated. Thanks

Matthew Ellis
 
Matthew,

ithout knoing your table structure, it is a little difficult to give you a definative answer. However, I'll try to address your question with the minimal data I have.

"pull the first six months of data for each well". Are you talking about the most recent six months, or the first six months after the well started producing?

What data do you want to "Pull". Do you want to do an aggregation, or do you want all of the data for each well during the "first six months"?

I'm going to assume that what you want is all data from your table for the first six months after the well began production. The query might look like:

SELECT T.*
FROM yourTable T
INNER JOIN (SELECT Well_Num, Min(Prod_Date) as FirstDate
FROM yourTable
GROUP BY Well_Num) as T1
ON T.Well_Num = T1.Well_Num
WHERE T.Prod_Date BETWEEN T.FirstDate
AND DateAdd("m", 6, T1.FirstDate)
I have a database where I have thousands of wells with daily data for each month of the year. I need to run a query which will pull the first six months of data for each well. Each well could be started on any month of the year so I can't just select month 1 through six. How should I set this up. It is already setup with a lookup table for the months (not my design) and each month is given its corresponding number (1-12). I was thinking I could somehow use this table, but I am not sure how........... Any help is appreciated. Thanks

Matthew Ellis
 
It is already setup with a lookup table for the months (not my design) and
each month is given its corresponding number (1-12). I was thinking I could
somehow use this table, but I am not sure how...........

If the month is stored in your table just as this number, without the year,
then the answer is very simple: YOU CAN'T. If there is no way to tell whether
month 1 follows month 12 immediately, or precedes it with ten months in
between, then you simply don't have the information in your table to allow
this to be done!

If you have the year, then you could use the DateSerial() function to create
a date/time value based on the year and the month:

DateSerial([yearfield], [monthfield], 1)

will be the first day of that month. A query

BETWEEN Min(DateSerial([yearfield], [monthfield], 1)) AND DateAdd("m", 6,
Min([Dateserial([yearfield], [monthfield], 1)))

should give you the date range you want.

John W. Vinson/MVP
 
I have tried this and quite a few variations and it keeps telling me that
"The expression you entered has a function containing the wrong number of
arguments" Any help is appreciated.

Matthew Ellis
 
Matthew Ellis said:
I have tried this and quite a few variations and it keeps telling me that
"The expression you entered has a function containing the wrong number of
arguments" Any help is appreciated.
Click to expand...

Please open your Query in SQL view and post the actual SQL expression here.
It sounds like you might have a parenthesis in the wrong place (quite
possibly because of an error on my part!)

John W. Vinson/MVP
 
Back
Top