Help with SQL query

[brsterner]

All - I have what I think, for someone with any level of SQL skills, should be a relatively routine problem to solve.

Right now I have a table called MWDHistory which relates a well to an MWD value effective from a specific day forward until updated again (typically monthly).

The table is laid out as such:

WellID Effective Date MWD
1 01/01/2007 809
1 03/01/2007 700
1 07/01/2007 625

What I need to do is create a select query to return a dataset that carries each effective MWD value forward between one effective date and the subsequent update without the need to store these as separate rows in the MWD history table.

Ideally the output would look like this...

WellID Effective Date MWD
1 01/01/2007 809
1 01/02/2007 809
1 01/02/2007 809
.
.
1 03/01/2007 700
1 03/02/2007 700

I'm doing this so that I can pull the data into an Excel Pivot table and sum the daily MWD value, grouping by Month.

WellID Jan Feb Mar Apr
1 303456 345800 346800 456022
2
3
4

Is there an easy way to do this using a SQL statement?

Thanks in advance any help you can offer!

Regards,
Brian

 

[maxhugen]

I'm guessing that you want the MWD value for each well, at the last date you recorded an MWD for that well?

If so, you can use a MAX grouping query, maybe something like:

[tt]SELECT WellID, Max([Effective Date]) AS LastEffectiveDate, First(MWD) AS LastMWD
FROM MWDHistory
GROUP BY WellID;[/tt]


Max Hugen
Australia

 

[brsterner]

Thanks for your reply Max, what I'm basically trying to do is return a result set that gives the effective MWD value, by day, between a range of dates. An MWD value is typically updated once per month per well, so those values carry over each day between updates. Problem is the update dates are not always consistent. So if I want to sum the aggregate MWD for a group of wells each day for say a 6 month period I need to know the effective MWD for each well on each day. However, I'm only storing rows for each update to MWD per well, not all dates in between.

I hope I'm explaining this well enough. Ultimately I want to pull this dataset into an Excel pivot table where I can use the grouping function to look at weekly, monthy, and even quarterly numbers.

Can you suggest a way to produce the result set as I described above?

Thanks so much for your help, there's no way I could do this on my own!

Cheers,
Brian

 

[maxhugen]

G'day Brian

Right, think I follow. In effect, we need to create rows for the days in between MWD dates... it's a little bit similar to this post:

http://www.tek-tips.com/viewthread.cfm?qid=1387092

Essentially, you'd use a function that loops through the records in MWDHistory, adding entries into a temp table, including entries for days between. You can then use the temp table to output to Excel.

Am I on the right track?

Max Hugen
Australia

 

[brsterner]

Max...you're the man. Its not pretty but I've nearly got it working. Just need to figure out how to incorporate post-July 1 values (between last updated and today's date) - but that's the least of my worries. I'm 95% there and all thanks to you. Your help potentially saved me days of time. Its a great think you do monitoring these forums helping folks like me through these problems - many, many thanks.

I've posted my code below for your reference.

Again, Thank YOU!

Brian


Public Function CreateHistory()
On Error GoTo err_CreateHistory
Dim db As DAO.Database
Dim rsHistory As DAO.Recordset
Dim rsTemp As DAO.Recordset
Dim DayToAdd As Date
Dim NextDate As Date
Dim NextWell As Integer
Dim s$

' set a reference to the database
Set db = CurrentDb

' clear the temp table
s = "DELETE * FROM HistoryTemp"
db.Execute s

' get the mwd history data
' rsHistory.Sort = "WellID, Date ASC"
Set rsHistory = db.OpenRecordset("SELECT * FROM MWDHistory ORDER BY WellID, Date")

' open the temp table
Set rsTemp = db.OpenRecordset("HistoryTemp")

' loop through the course records
While Not rsHistory.EOF

DayToAdd = rsHistory!Date
rsHistory.MoveNext
NextDate = rsHistory.Fields("Date")
NextWell = rsHistory.Fields("WellID")
rsHistory.MovePrevious

While FormatDateTime(DayToAdd, vbShortDate) < FormatDateTime(NextDate, vbShortDate) And NextWell = rsHistory.Fields("WellID")
' add a record for every day of the history
rsTemp.AddNew
rsTemp!WellID = rsHistory!WellID
rsTemp!gasMWD = rsHistory!MWDGas
rsTemp!oilMWD = rsHistory!MWDOil
rsTemp!Date = DayToAdd
rsTemp.Update
DayToAdd = DayToAdd + 1
Wend
rsHistory.MoveNext
Wend

' close the recordsets
rsTemp.Close
rsHistory.Close

exit_CreateHistory:
Set db = Nothing: Set rsHistory = Nothing: Set rsTemp = Nothing
Exit Function
err_CreateHistory:
MsgBox Err & ": " & Err.Description
Resume exit_CreateHistory
End Function

 

[maxhugen]

Good job, Brian!

Re creating records from the last date in the history to today's date (or any other), how about setting a var to the date, and checking for the last record? eg:

[blue]Dim LastDate as date
Dim NumRecords&, RecCount&[/blue]

    [blue]LastDate = Date()[/blue] [green]' or =cDate("7/1/2007") etc[/green]

    <...more code...>

    ' get the mwd history data
    Set rsHistory = db.OpenRecordset("SELECT * FROM MWDHistory ORDER BY WellID, Date")
        [blue]rsHistory.MoveLast
        NumRecords=rsHistory.RecordCount
        rsHistory.MoveFirst[/blue]

        <...more code...>

        ' loop through the history records
        While Not rsHistory.EOF
            [blue]RecCount=RecCount+1[/blue]

            <...more code...>

            [blue]if recCount < NumRecords[/blue]

                <...existing loop code...>

            [blue]else
                While FormatDateTime(DayToAdd, vbShortDate) <= FormatDateTime([b]LastDate[/b], vbShortDate) And NextWell = rsHistory.Fields("WellID")
                    ' add a record for every day from last date 
                    ' in the history to LastDate
                    rsTemp.AddNew
                        rsTemp!WellID = rsHistory!WellID
                        rsTemp!gasMWD = rsHistory!MWDGas
                        rsTemp!oilMWD = rsHistory!MWDOil
                        rsTemp!Date = DayToAdd
                    rsTemp.Update
                    DayToAdd = DayToAdd + 1
                Wend
            end if[/blue]
            rsHistory.MoveNext

            <...rest of code...>

Max Hugen
Australia