Group data by time slot

I would like to prepare a SQL such that I can perform query on a table
and count the number of record per time slot. The time slot is 2
seconds each, but the beginning time of each time slot is not fix, it
is determined by the create_date of the record, and this is the
example:
Raw Data:
seq cat create_date
-- -- --
151 A 2006-01-25 14:14:20.827
152 B 2006-01-25 14:14:20.983
161 A 2006-01-25 14:14:22.390
162 B 2006-01-25 14:14:22.543
171 A 2006-01-25 14:14:23.997
172 B 2006-01-25 14:14:24.153
181 A 2006-01-25 14:14:25.560
182 B 2006-01-25 14:14:25.717
191 A 2006-01-25 14:14:27.123
192 B 2006-01-25 14:14:27.280
Output:
seq cat create_date count
-- -- -- --
151 A 2006-01-25 14:14:20.827 2 -- contain 151, 161
171 A 2006-01-25 14:14:23.997 2 -- contain 171, 181
191 A 2006-01-25 14:14:27.123 1 -- contain 191
152 B 2006-01-25 14:14:20.983 2 -- contain 152, 162
172 B 2006-01-25 14:14:24.153 2 -- contain 172, 182
192 B 2006-01-25 14:14:27.280 1 -- contain 192
Any idea on how the SQL should write?
I just think of a SQL to get the intermediate grouping on which time
slot the record belongs to, but no idea on how to eliminate the
duplicated entry which is already included in another time slot....
The unfinished SQL:
SELECT m.cat, m.create_date AS 'gp_create_date', m.seq, n.seq,
n.create_date, n.random_string
FROM bbs m
LEFT OUTER JOIN bbs n ON n.cat = m.cat
AND n.create_date > m.create_date AND
datediff(ss, m.create_date, n.create_date) < 2
ORDER BY m.create_dateTry this
CREATE TABLE #Temp(seq int, cat CHAR(1), cdate datetime)
INSERT INTO #Temp
SELECT
151, 'A', '2006-01-25 14:14:20.827' UNION ALL
SELECT 152, 'B', '2006-01-25 14:14:20.983' UNION ALL
SELECT 161, 'A', '2006-01-25 14:14:22.390' UNION ALL
SELECT 162, 'B', '2006-01-25 14:14:22.543' UNION ALL
SELECT 171, 'A', '2006-01-25 14:14:23.997' UNION ALL
SELECT 172, 'B', '2006-01-25 14:14:24.153' UNION ALL
SELECT 181, 'A', '2006-01-25 14:14:25.560' UNION ALL
SELECT 182, 'B', '2006-01-25 14:14:25.717' UNION ALL
SELECT 191, 'A', '2006-01-25 14:14:27.123' UNION ALL
SELECT 192, 'B', '2006-01-25 14:14:27.280'
SELECT MIN(seq) as seq, cat, min(cdate) as mindate,max(cdate) as maxdate,
count(*) as cnt
FROM #Temp
GROUP BY cat, YEAR(cdate), Month(cdate), day(cdate),
DATEPART(hh,cdate),DATEPART(mi,cdate),
CASE WHEN DATEPART(s,cdate) %2 = 0
THEN DATEPART(s,cdate)
ELSE DATEPART(s,cdate) - 1
END
DROP tABLE #Temp
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"John Shum" <eurostar@.gmail.com> wrote in message
news:1138177022.986492.77030@.o13g2000cwo.googlegroups.com...
>I would like to prepare a SQL such that I can perform query on a table
> and count the number of record per time slot. The time slot is 2
> seconds each, but the beginning time of each time slot is not fix, it
> is determined by the create_date of the record, and this is the
> example:
> Raw Data:
> seq cat create_date
> -- -- --
> 151 A 2006-01-25 14:14:20.827
> 152 B 2006-01-25 14:14:20.983
> 161 A 2006-01-25 14:14:22.390
> 162 B 2006-01-25 14:14:22.543
> 171 A 2006-01-25 14:14:23.997
> 172 B 2006-01-25 14:14:24.153
> 181 A 2006-01-25 14:14:25.560
> 182 B 2006-01-25 14:14:25.717
> 191 A 2006-01-25 14:14:27.123
> 192 B 2006-01-25 14:14:27.280
> Output:
> seq cat create_date count
> -- -- -- --
> 151 A 2006-01-25 14:14:20.827 2 -- contain 151, 161
> 171 A 2006-01-25 14:14:23.997 2 -- contain 171, 181
> 191 A 2006-01-25 14:14:27.123 1 -- contain 191
> 152 B 2006-01-25 14:14:20.983 2 -- contain 152, 162
> 172 B 2006-01-25 14:14:24.153 2 -- contain 172, 182
> 192 B 2006-01-25 14:14:27.280 1 -- contain 192
> Any idea on how the SQL should write?
> I just think of a SQL to get the intermediate grouping on which time
> slot the record belongs to, but no idea on how to eliminate the
> duplicated entry which is already included in another time slot....
> The unfinished SQL:
> SELECT m.cat, m.create_date AS 'gp_create_date', m.seq, n.seq,
> n.create_date, n.random_string
> FROM bbs m
> LEFT OUTER JOIN bbs n ON n.cat = m.cat
> AND n.create_date > m.create_date AND
> datediff(ss, m.create_date, n.create_date) < 2
> ORDER BY m.create_date
>