Comparing the previous record

[benchpolo]

Data
MEMBERID OPTCODE LASTCHANGEDATE
79988302FD1 878 2008-05-10 00:58:17.283
79888302FD1 546 2008-05-10 00:58:17.020
88888302FD1 999 2008-05-10 00:58:17.020
69988302FD1 878 2008-05-10 00:58:17.283
68888302FD1 546 2008-05-10 00:58:17.020


Question?
I have a data set in SQL that I need to compare the previous record if it was changed based on MEMBERID if the OPTCODE has changed.

For example, memberid 79988302FD1's OPTCODE has changed, I need to be able to compare the previous record againts the current one.

Please advise.

Thanks.

[Delerna]

[code=sql]
SELECT a.MemberID,a.OP Code,a.LastChan geDate,
b.MemberID,b.OP Code,b.LastChan geDate,
( SELECT Max(LastChangeD ate)
FROM YourTable c
WHERE c.LastChangeDat e<a.LastChangeD ate
) as PrevDate
FROM YourTable a
LEFT JOIN YourTable b on a.MemberID=b.Me mberID
and b.LastChangeDat e=PrevDate

[/code]

Haven't tested that but it will be something like that.

[ck9663]

And watch out for duplicates.

-- CK

[benchpolo]

For verification purposes, below is what I wrote.

select mc.membid as MEMBERID
, mc.company_id as COMPANYID
, mh.opt as CURROPTCD
, (select TOP 1 a.opt from memb_hphists a
where a.memb_keyid=mc .memb_keyid
and a.lastchangedat e<mh.lastchange date
and a.ez_dbname=mh. ez_dbname order by a.memb_keyid, a.lastchangedat e desc) as PREVOPTCD
, mh.createby as USERID
, u.username as USERNAME
, mh.lastchangeda te as LASTCHANGEDATE
from memb_company mc
inner join users u on mc.createby=u.i d and mc.ez_dbname = u.ez_dbname
inner join memb_hphists mh on mc.memb_keyid=m h.memb_keyid and mc.ez_dbname=mh .ez_dbname
where convert(varchar ,mh.lastchanged ate,101) = DATEADD(d, - 2, DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))
and mc.ez_dbname <> 'ARCHP'
and mh.currhist = 'C'
and mh.opt <> (select TOP 1 a.opt from memb_hphists a
where a.memb_keyid=mc .memb_keyid
and a.lastchangedat e<mh.lastchange date
and a.ez_dbname=mh. ez_dbname
order by a.memb_keyid, a.lastchangedat e desc)