470,864 Members | 1,989 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,864 developers. It's quick & easy.

treat contiguous records as 1

Hi,

How can I treat multiple, contiguous records as one record?

i.e.,

tblData(
a varchar,
t datetime
)

select * from tblData
order by a,t

then, for row n, if a(n) = a(n+1) and t(n) <= t(n+1) (seconds) then
treat record n as the same as n+1, discard record n+1 and continue,
else start again with the next row.

Anyone got an idea? Just writing this I'm thinking I'm going to have
to venture into cursor territory (new for me).

TIA

Chandy

Jul 23 '05 #1
1 1527
Try this:

CREATE TABLE tblData(a VARCHAR(10), t DATETIME, PRIMARY KEY (a,t))

INSERT INTO tblData (a,t)
SELECT 'A','2005-01-01T00:00:01' UNION ALL
SELECT 'A','2005-01-01T00:00:03' UNION ALL
SELECT 'A','2005-01-01T00:00:04' UNION ALL
SELECT 'B','2005-01-01T00:00:11' UNION ALL
SELECT 'B','2005-01-01T00:00:12' UNION ALL
SELECT 'B','2005-01-01T00:00:13' UNION ALL
SELECT 'B','2005-01-01T00:00:15'

SELECT T1.a, T1.t
FROM tblData AS T1
WHERE NOT EXISTS
(SELECT *
FROM tblData AS T2
WHERE T1.a = T2.a
AND T2.t < T1.t
AND T2.t >= DATEADD(SS,-1,T1.t))

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Yulia Yegenov | last post: by
8 posts views Thread by Ravi | last post: by
5 posts views Thread by Stefan Krah | last post: by
38 posts views Thread by Peteroid | last post: by
22 posts views Thread by divya_rathore_ | last post: by
22 posts views Thread by Jack | last post: by
9 posts views Thread by jmcgill | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.