partition function with times

[Rien]

I'm counting occurences of events within certain time frames. For now
i'm working with Excel (sumproduct), but I'm trying to switch to
access.

I've learned about the partition function,

SELECT DISTINCT Partition((86399*[times]),0,83699,3600) AS timeunits,
Count('times') AS verdeling
FROM tabletimes
GROUP BY Partition((86399*[times]),0,83699,3600);

This is what i'm using so far.

but it has a few drawbacks:
-it only works with integers (not very useful working with times)
-it leaves gaps where there are no events

Can these drawbacks be overcome?

 

[Stefan Hoffmann]

hi Rien,

SELECT DISTINCT Partition((86399*[times]),0,83699,3600) AS timeunits,
Count('times') AS verdeling
FROM tabletimes
GROUP BY Partition((86399*[times]),0,83699,3600);

Shouldn't that be 86400*[times]. Or better as you need a hour resolution:

Partition((24*[times]),0,23,1)

but it has a few drawbacks:
-it only works with integers (not very useful working with times)

Not really, as you have to pick the resolution in time for your
partition function anyway.

-it leaves gaps where there are no events

No data, no result.

Can these drawbacks be overcome?

I wouldn't call them drawbacks anyway.


mfG
--> stefan <--

 

[KARL DEWEY]

-> -it leaves gaps where there are no events
You should be able to build a table of times and left join in your query.
SELECT DISTINCT Partition((86399*[Mytabletimes].[times]),0,83699,3600) AS
timeunits,
Count('times') AS verdeling
FROM Mytabletimes LEFT JOIN tabletimes ON
Partition((86399*[Mytabletimes].[times]),0,83699,3600) =
Partition((86399*[tabletimes].[times]),0,83699,3600)
GROUP BY Partition((86399*[MYtabletimes].[times]),0,83699,3600);

--
Build a little, test a little.

hi Rien,

SELECT DISTINCT Partition((86399*[times]),0,83699,3600) AS timeunits,
Count('times') AS verdeling
FROM tabletimes
GROUP BY Partition((86399*[times]),0,83699,3600);

Shouldn't that be 86400*[times]. Or better as you need a hour resolution:

Partition((24*[times]),0,23,1)

but it has a few drawbacks:
-it only works with integers (not very useful working with times)

Not really, as you have to pick the resolution in time for your
partition function anyway.

-it leaves gaps where there are no events

No data, no result.

Can these drawbacks be overcome?

I wouldn't call them drawbacks anyway.


mfG
--> stefan <--