Query by Time-Slices

Dear NG,
I want to query a table like this:
Customer | Money | Time
A 12 12:01
B 17 12:03
C 25 12:06
D 14 12:09
E 10 12:11
...
My target is to have the SUM of Money Transfers by TIME SLICE, eg.
12:00 - 12:05: 29
12:05 - 12:10: 39
12:10 - 12:15: 10
...
How can I query this?
Thank you very much
RudiI am not sure what format your "Time" column is stored, but perhaps this
will get you started:
SELECT SUM(SomeColumn) AS SomeColumn, 'TheTimeSlice' = CASE WHEN TheTime
BETWEEN '12:00' AND '12:05' THEN '12:00 - 12:05' WHEN TheTime BETWEEN
'12:06' AND '12:10' THEN '12:06 - 12:10' END
FROM YourTable
GROUP BY CASE WHEN TheTime BETWEEN '12:00' AND '12:05' THEN '12:00 - 12:05'
WHEN TheTime BETWEEN '12:06' AND '12:10' THEN '12:06 - 12:10' END
Keith Kratochvil
<rudolf.ball@.asfinag.at> wrote in message
news:1141651834.317186.293110@.i39g2000cwa.googlegroups.com...
> Dear NG,
> I want to query a table like this:
> Customer | Money | Time
> A 12 12:01
> B 17 12:03
> C 25 12:06
> D 14 12:09
> E 10 12:11
> ...
> My target is to have the SUM of Money Transfers by TIME SLICE, eg.
> 12:00 - 12:05: 29
> 12:05 - 12:10: 39
> 12:10 - 12:15: 10
> ...
> How can I query this?
> Thank you very much
> Rudi
>|||Thank you very much,
but if I want to split a DAY into 5-MIN-SLICES, how can I do this:
CASE?
Thank you
Rudi|||Please post table DDLs so that others can understand the datatypes, keys,
constraints etc. What is the datatype of the time column? Also time and
money are reserved words in t-SQL.
Your sample data is not very clear. Do you need rows for all 5 minute slice
irrespective of whether a transfer occured or not? If a transfer occured
exactly on 12:05 ( AM/PM ) , do you want to account for it in the 12:00 -
12:05 group or 12:05 - 12:10 group?
As a general response, for such problems consider integer division on the
expression that returns the number of minutes from the starting point. For
instance, in this case it would be: DATEDIFF( minute, 0, time_ ) / 5. You
can use this expression in your GROUP BY clause to return the resultset you
want.
Anith|||Yes you can, by relying on integer division.
CREATE TABLE Test(Customer char(1),Money int,Time smalldatetime)
INSERT INTO Test VALUES ('A',12,'12:01')
INSERT INTO Test VALUES ('B',17,'12:03')
INSERT INTO Test VALUES ('C',25,'12:06')
INSERT INTO Test VALUES ('D',14,'12:09')
INSERT INTO Test VALUES ('E',10,'12:11')
SELECT CONVERT(char(5),DATEADD(minute,
MIN(DATEDIFF(minute,'12:00',Time)/5), '12:00'),108)
, CONVERT(char(5),DATEADD(minute,
MIN(DATEDIFF(minute,'12:00',Time)/5), '12:05'),108)
, SUM("Money")
FROM Test
GROUP BY DATEDIFF(minute,'12:00',"Time")/5
DROP TABLE Test
I guess you have something to study now :-)
Gert-Jan
rudolf.ball@.asfinag.at wrote:
> Dear NG,
> I want to query a table like this:
> Customer | Money | Time
> A 12 12:01
> B 17 12:03
> C 25 12:06
> D 14 12:09
> E 10 12:11
> ...
> My target is to have the SUM of Money Transfers by TIME SLICE, eg.
> 12:00 - 12:05: 29
> 12:05 - 12:10: 39
> 12:10 - 12:15: 10
> ...
> How can I query this?
> Thank you very much
> Rudi|||With SQL 2005, you could create a mapping from time to some integer domain
(say, convert to minutes in the day and divide by 5), then use the AVG(col)
OVER (PARTITION BY integerslice) functionality that was added. This avoids
having to write all of the slices out in a big case statement.
good luck.
Conor
<rudolf.ball@.asfinag.at> wrote in message
news:1141657033.464616.225810@.j33g2000cwa.googlegroups.com...
> Thank you very much,
> but if I want to split a DAY into 5-MIN-SLICES, how can I do this:
> CASE?
> Thank you
> Rudi
>