Querying Dates

[Martin]

Hello,

I am looking for some advice on how to tackle querying some data.

I have two tables, table one with around 10,000 records per month and
columns that give me (for each record) a start date, start time, end date and
end time.

Table two details a percentage for each hour and day of the week (so 24
hours * 7 days = 168 records). This has three columns, the day of the week
(Mon to Sun), the hour (00:00 to 23:00) and a percentage.

What I want to do is sum the total of the percentage column in table 2 where
the day of the week and the hour of the week fall within the start date /
start time and end date / end time.

Any help would be greatly appreciated.

Martin

 

[KARL DEWEY]

If table two only has day of the week, there can be 4 - 5 Mondays in a month,
how will you know which Monday to use?

 

[John Spencer]

Sounds as if you need a non-equi join and at the same time you need to do some
data manipulation on the fields That are being used in the join.

The first thing I would do would be to add another column and store the Day of
week number Sun=1 to Sat=7 for each record. Then I would add another column
that stores just the NUMBER of the hour.

How do you handle partial hours? Ignore them, round them to the nearest whole
hour, or use partial hours to get partial credit.

Do you have records that overlap days? I assume so since you are recording a
start date and end date

Do you have records that encompass more than one week - event starts on Friday
and runs until Tuesday of the following week? If not the problem becomes a bit
easier to solve.

Do you have records that encompass multiple weeks? Jan 1 to Jan 31 for instance.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Martin]

Thanks for the reply John.

Yes I do have records that could start on a Friday and end on the Tuesday of
the next week. Table one with the data of start date/time and end date/time
is something I am sent and cannot change however the second table with the
percentages is something I have created to try to help overcome the problem
but this can be changed if that helps?

 

[John Spencer]

I wish you had answered all the questions, but that should be enough for me to
start thinking about a solution. I would like to do this using a query, but
it may be necessary to create a VBA function. It would certainly be easier to
use VBA to solve this, but the speed might not be acceptable. I will try to
get back to you by Sunday. I am a little stretched for time at this point.





John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

I haven't come up with a good way to do this with just a query. I've put
together a function and tested it minimally. This assumes that your lookup
table has a weekday number (1 to 7) and an hour number (0 to 23).

Also this gives full credit for partial hours.


'================= Minimally tested code follows ================
Function fCalcSum(dStart, tStart, dEnd, tEnd)
Dim dblResult As Double
Dim StrSQL As String
Static WeekTotal As Double

If WeekTotal = 0 Then
WeekTotal = dSum("TheValue", "ValuesTable")
End If

If IsDate(dStart) And IsDate(tStart) _
And IsDate(dEnd) And IsDate(tEnd) Then

If Weekday(dStart) <= Weekday(dEnd) Then
StrSQL = " SELECT SUM(TheValue)" & _
" FROM ValuesTable" & _
" WHERE DayNumber*100 + HourNumber >= " & _
Weekday(dStart) * 100 + Hour(tStart) & _
" AND DayNumber*100 + HourNumber <= " & _
Weekday(dEnd) * 100 + Hour(tEnd)
ElseIf Weekday(dStart) > Weekday(dEnd) Then
StrSQL = " SELECT SUM(TheValue)" & _
" FROM ValuesTable" & _
" WHERE DayNumber*100 + HourNumber <= " & _
Weekday(dEnd) * 100 + Hour(tEnd) & _
" OR DayNumber*100 + HourNumber >= " & _
Weekday(dStart) * 100 + Hour(tStart)
End If
'Get the hours that are not entire weeks
dblResult = Nz(CurrentDb().OpenRecordset(StrSQL).Fields(0), 0)

'Get hours for entire week(s)
dblResult = dblResult + (DateDiff("d", dStart, dEnd) \ 7) * WeekTotal

'Adjust the total for counting the same hour twice in the above SQL
dblResult = dblResult - 1
'Return the resulting calculation
fCalcSum = dblResult
Else
fCalcSum = Null
End If

End Function


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County