SQL to calculate half hour summary

[aaronlglover]

Hey All,

Does anyone have some SQL to get me started or maybe a link to a help guide that will get me started? I have a table with schedule exception detail that I need to summarize into half hour incrememnts. Here is an example of what the schedule exception detail looks like.

Exception | Start Moment | Stop Moment | Duration |
Break | 3/17/09 02:00 | 3/17/09 03:00 | 60 |
Lunch | 3/17/09 05:00 | 3/17/09 06:15 | 75 |
Break | 3/17/09 07:30 | 3/17/09 08:15 | 45 |

The Query should produce something that looks like this.

Interval | BREAK | LUNCH
02:00 | 30 | 0 |
02:30 | 30 | 0 |
05:00 | 0 | 30 |
05:30 | 0 | 30 |
06:00 | 0 | 15 |
07:30 | 30 | 0 |
08:00 | 15 | 0 |

Thanks!!

 

[kjv1611]

You know, I'm thinking that you may find at least part of your answer in the FAQ - not 100% sure, but I just looked them over. Take a look.

Microsoft: Access Queries and JET SQL FAQ Index[/LINK]

Specifically, I thought these might be at least part of what you need:
getting an incremental value
faq701-1155
or
faq701-896

Running Sum In Queries
faq701-5268

Also, try Google if not already. Here are a couple of results that look promising:
[URL unfurl="true"]http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

And this one sounded like it could help as well:

http://www.databasejournal.com/feat...orking-with-SQL-Server-DateTime-Variables.htm

Part 2 of same:

http://www.databasejournal.com/feat...ying-Dates-and-Times-in-Different-Formats.htm

Here's the Google search I used.


--

"If to err is human, then I must be some kind of human!" -Me

 

[dhookom]

I would create a table (tblTimeBuckets) of all 48 half hour buckets in a day. It would be easiest of include the start and end times of each.

Then create a crosstab. You will need to figure out the "value" since it is the minutes at either the start or the end of the bucket.

TRANSFORM First(tblSchedExcept.[Start Moment]) AS [FirstOfStart Moment]
SELECT tblTimeBuckets.FromTIme
FROM tblTimeBuckets, tblSchedExcept
WHERE (((TimeValue([Start Moment]))<[ToTime]) AND ((TimeValue([Stop Moment]))>[FromTime]))
GROUP BY tblTimeBuckets.FromTIme, tblTimeBuckets.ToTime, tblSchedExcept.[Stop Moment], TimeValue([Start Moment]), TimeValue([Stop Moment])
PIVOT tblSchedExcept.Exception;

Duane
Hook'D on Access
MS Access MVP