Date "overruns" (Repost)

[Al Camp]

Given the following data...
StartDateTime EndDateTime Problem Hours
1/10/06 9:00 1/12/06 11:00 Z 50.0

Is it possible, through a combination of Update and Append queries to derive the following
records...
StartDateTime EndDateTime Problem Hours
1/10/06 1/12/06 11:00 Z 13.0
1/11/06 1/12/06 11:00 Z 24.0
1/12/06 1/12/06 11:00 Z 23.0


I realize that the best way to do this would be to close out the logs at midnite each day,
and create a new outage for the next day... until the Incident is finally resolved, but I
would like to give the above method a go... if possible.

Any help ("aircode" would do...) would be appreciated.
Al Camp

 

[Duane Hookom]

First, I assume your end time is 11:00 PM not 11:00 AM as presented and the
hours on the first date should be 15, not 13.

I would create a table of all dates (tblAllDates) with a single date field
and all possible date values. Then create a small function like:

Function GetLogPerDate(pdatStart As Date, _
pdatEnd As Date, pdatDate As Date) As Double
If DateValue(pdatStart) = pdatDate Then
GetLogPerDate = DateDiff("h", pdatStart, pdatDate + 1)
End If
If DateValue(pdatEnd) = pdatDate Then
GetLogPerDate = DateDiff("h", pdatDate, pdatEnd)
End If
If DateValue(pdatStart) < pdatDate _
And DateValue(pdatEnd) > pdatDate Then
GetLogPerDate = 24
End If
End Function

You can then create a query like:
SELECT TheDate, EndDateTime, Problem,
GetLogPerDate([StartDateTime],[EndDateTime],[TheDate]) AS Hrs
FROM tblAllDates, tblTheFollowingData
WHERE TheDate Between DateValue([StartDateTime]) And
DateValue([EndDateTime]);

 

[Al Camp]

Thanks for the help Duane.
I asked for "aircode" to make it as easy as possible for you... I appreciate your laying
out your actual code.

On 3/24/06 @ 12:55 I posted a more detailed explanantion of what I'm trying to do. When I
rcvd no replies, I decided to try a much more "abbreviated" version...

Yes I did have a typo.
Given... (military time)
StartDateTime EndDateTime Problem ToatalHours
1/10/06 9:00 1/12/06 11:00 Z 50.0

I need to break out those ">24" Total Hours by creating *individual new records* records
"per day:, until TotalHours runs out.
(* = outage per day)
StartDateTime Problem Hours
1/10/06 9am-12pm Z 15.0*
1/11/06 all day Z 24.0*
1/12/06 0:01am - 11am Z 11.0*
' totals 50.0

I have 200-300 records each month (usual date range for report). The ones that don't run
past midnite can be appended to the temp table directly. Only records that "span" past
midnite will need to be broken up into individual daily outages.
All I need for my crosstab is records that show the IncidentStartDate, Problem, and
OutageHours for that date.

Thanks Duane. I'll get back to you as soon as I've had a chance to wring all this out...
Al Camp

First, I assume your end time is 11:00 PM not 11:00 AM as presented and the hours on the
first date should be 15, not 13.

I would create a table of all dates (tblAllDates) with a single date field and all
possible date values. Then create a small function like:

Function GetLogPerDate(pdatStart As Date, _
pdatEnd As Date, pdatDate As Date) As Double
If DateValue(pdatStart) = pdatDate Then
GetLogPerDate = DateDiff("h", pdatStart, pdatDate + 1)
End If
If DateValue(pdatEnd) = pdatDate Then
GetLogPerDate = DateDiff("h", pdatDate, pdatEnd)
End If
If DateValue(pdatStart) < pdatDate _
And DateValue(pdatEnd) > pdatDate Then
GetLogPerDate = 24
End If
End Function

You can then create a query like:
SELECT TheDate, EndDateTime, Problem,
GetLogPerDate([StartDateTime],[EndDateTime],[TheDate]) AS Hrs
FROM tblAllDates, tblTheFollowingData
WHERE TheDate Between DateValue([StartDateTime]) And DateValue([EndDateTime]);


--
Duane Hookom
MS Access MVP
--

Given the following data...
StartDateTime EndDateTime Problem Hours
1/10/06 9:00 1/12/06 11:00 Z 50.0

Is it possible, through a combination of Update and Append queries to derive the
following records...
StartDateTime EndDateTime Problem Hours
1/10/06 1/12/06 11:00 Z 13.0
1/11/06 1/12/06 11:00 Z 24.0
1/12/06 1/12/06 11:00 Z 23.0


I realize that the best way to do this would be to close out the logs at midnite each
day, and create a new outage for the next day... until the Incident is finally
resolved, but I would like to give the above method a go... if possible.

Any help ("aircode" would do...) would be appreciated.
Al Camp

 

[Al Camp]

Duane,
Just an update if you're interested...
I'm using DAO to open a query that delivers, via date range, all
the "outage" records, whether past midnight or not.
I'll start on record1, and examine each outage as to whether it overflows into another
day.
If not... append it directly to a tblTemptable
I so...
derive the first day/outage and append (totaloutage - that days outage)
is remaining outage > 24 then
derive the second day/outage and append
is remaining outage > 24 then
derive third day/outage and append...
etc... until Total outage hours is reduced to less than 24
derive last day of outage with remaining outage hours.
Next Record

I realize this is pretty mechanical, but I think it will work.
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

First, I assume your end time is 11:00 PM not 11:00 AM as presented and the hours on the
first date should be 15, not 13.

I would create a table of all dates (tblAllDates) with a single date field and all
possible date values. Then create a small function like:

Function GetLogPerDate(pdatStart As Date, _
pdatEnd As Date, pdatDate As Date) As Double
If DateValue(pdatStart) = pdatDate Then
GetLogPerDate = DateDiff("h", pdatStart, pdatDate + 1)
End If
If DateValue(pdatEnd) = pdatDate Then
GetLogPerDate = DateDiff("h", pdatDate, pdatEnd)
End If
If DateValue(pdatStart) < pdatDate _
And DateValue(pdatEnd) > pdatDate Then
GetLogPerDate = 24
End If
End Function

You can then create a query like:
SELECT TheDate, EndDateTime, Problem,
GetLogPerDate([StartDateTime],[EndDateTime],[TheDate]) AS Hrs
FROM tblAllDates, tblTheFollowingData
WHERE TheDate Between DateValue([StartDateTime]) And DateValue([EndDateTime]);


--
Duane Hookom
MS Access MVP
--

Given the following data...
StartDateTime EndDateTime Problem Hours
1/10/06 9:00 1/12/06 11:00 Z 50.0

Is it possible, through a combination of Update and Append queries to derive the
following records...
StartDateTime EndDateTime Problem Hours
1/10/06 1/12/06 11:00 Z 13.0
1/11/06 1/12/06 11:00 Z 24.0
1/12/06 1/12/06 11:00 Z 23.0


I realize that the best way to do this would be to close out the logs at midnite each
day, and create a new outage for the next day... until the Incident is finally
resolved, but I would like to give the above method a go... if possible.

Any help ("aircode" would do...) would be appreciated.
Al Camp

 

[Duane Hookom]

I would try to use a more "sqlized" approach but what ever works for you.

--
Duane Hookom
MS Access MVP
--

Duane,
Just an update if you're interested...
I'm using DAO to open a query that delivers, via date range, all
the "outage" records, whether past midnight or not.
I'll start on record1, and examine each outage as to whether it
overflows into another day.
If not... append it directly to a tblTemptable
I so...
derive the first day/outage and append (totaloutage - that days
outage)
is remaining outage > 24 then
derive the second day/outage and append
is remaining outage > 24 then
derive third day/outage and append...
etc... until Total outage hours is reduced to less than 24
derive last day of outage with remaining outage hours.
Next Record

I realize this is pretty mechanical, but I think it will work.
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

First, I assume your end time is 11:00 PM not 11:00 AM as presented and
the hours on the first date should be 15, not 13.

I would create a table of all dates (tblAllDates) with a single date
field and all possible date values. Then create a small function like:

Function GetLogPerDate(pdatStart As Date, _
pdatEnd As Date, pdatDate As Date) As Double
If DateValue(pdatStart) = pdatDate Then
GetLogPerDate = DateDiff("h", pdatStart, pdatDate + 1)
End If
If DateValue(pdatEnd) = pdatDate Then
GetLogPerDate = DateDiff("h", pdatDate, pdatEnd)
End If
If DateValue(pdatStart) < pdatDate _
And DateValue(pdatEnd) > pdatDate Then
GetLogPerDate = 24
End If
End Function

You can then create a query like:
SELECT TheDate, EndDateTime, Problem,
GetLogPerDate([StartDateTime],[EndDateTime],[TheDate]) AS Hrs
FROM tblAllDates, tblTheFollowingData
WHERE TheDate Between DateValue([StartDateTime]) And
DateValue([EndDateTime]);


--
Duane Hookom
MS Access MVP
--

Given the following data...
StartDateTime EndDateTime Problem Hours
1/10/06 9:00 1/12/06 11:00 Z 50.0

Is it possible, through a combination of Update and Append queries to
derive the following records...
StartDateTime EndDateTime Problem Hours
1/10/06 1/12/06 11:00 Z 13.0
1/11/06 1/12/06 11:00 Z 24.0
1/12/06 1/12/06 11:00 Z 23.0


I realize that the best way to do this would be to close out the logs at
midnite each day, and create a new outage for the next day... until the
Incident is finally resolved, but I would like to give the above method
a go... if possible.

Any help ("aircode" would do...) would be appreciated.
Al Camp