treat contiguous records as 1

[chandy]

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

[David Portas]

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
--