"Simultaneity"

[Maria Teresa Chupacabra]

Sorry; not sure that's even a real word, but it expresses what I'd like to
get at.

Suppose you run a trucking company. You keep track in tables with fields
showing each truck, with time and date fields showing the beginning and end
of each "Run" for each truck. You'd like to determine, by 15-minute
intervals, how many trucks are trucking at the same time, 24x7. I suspect
that a simple query can't do this, but I can't think of another way to do
it.

Thanks in advance.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'd have to create a table that holds the 15 min intervals and then
compare the truck run times against the 15min table. E.g.:

Create the table:

CREATE TABLE Clock (
[minute] DateTime NOT NULL PRIMARY KEY ,
)

Then fill the clock w/ times (full dates & times if you want to cover
more than one day. If you want to cover just one day all you have to
enter is the 15 min intervals of 24 hrs). I'll just do a day.

INSERT INTO Clock ([minute]) VALUES (#00:15#)
INSERT INTO Clock ([minute]) VALUES (#00:30#)
INSERT INTO Clock ([minute]) VALUES (#00:45#)
INSERT INTO Clock ([minute]) VALUES (#01:00#)
....
INSERT INTO Clock ([minute]) VALUES (#23:45#)
INSERT INTO Clock ([minute]) VALUES (#00:00#)

Then create a view, or query, to get truck times:

SELECT C.[minute], T.truck_id
FROM Clock As C INNER JOIN TruckRuns As T
ON C.[minute] BETWEEN T.run_start And T.run_end
ORDER BY C.[minute], T.truck_id

This supposes the truck run times are in minutes only. If the run_start
& run_end are date & time. Use the TimePart() function to get just the
time: BETWEEN TimePart(T.run_start) And TimePart(T.run_end). If you
fill the Clock table w/ dates & times you won't have to use the
TimePart() function, which shud make the query run faster.

Because of the JOIN expression you won't be able to see this query in
Design view, only in SQL view.

The output will be:

minute truck_id
00:00 22
00:00 33
00:00 01
00:15 25
00:30 100
01:15 33

There will be breaks in time, like the 00:30 to 01:15, if there aren't
any trucks running on those times (00:45, 01:00).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAdTHIechKqOuFEgEQK/AgCeKRiEbnXmpZjhFjG9GI06pvvXsVIAoLXG
BM6VbzzBI8DDABRHjbjpesib
=caqc
-----END PGP SIGNATURE-----

 

[Michel Walsh]

Hi,

SELECT COUNT(*),
DateAdd("n", 15*(DateDiff("n", #1-1-2000#,
DateTimeStampFieldNameHere)\15), #1-1-2000#) As ts

FROM myTable

GROUP BY DateAdd("n", 15*(DateDiff("n", #1-1-2000#,
DateTimeStampFieldNameHere)\15), #1-1-2000#)



which counts the number of minutes from January the First of 2000, divides
that by 15, take the integer part, then multiply it back by 15. That makes
our 15 minutes slices. Add that value as number of minutes from the said
January the First of 2000 gives us back a date+time (at 15 minutes
intervals). We can GOUP BY on such a computed expression, and use that to
simply COUNT our number of records that fall with the same value of
date+time at each 15 minutes interval.



Hoping it may help,
Vanderghast, Access MVP