By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,357 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Comparing the previous record

benchpolo
100+
P: 142
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.
May 12 '08 #1
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
Expand|Select|Wrap|Line Numbers
  1. SELECT a.MemberID,a.OPCode,a.LastChangeDate,
  2.          b.MemberID,b.OPCode,b.LastChangeDate,
  3.          (   SELECT Max(LastChangeDate) 
  4.              FROM YourTable c 
  5.              WHERE c.LastChangeDate<a.LastChangeDate
  6.          ) as PrevDate
  7. FROM YourTable a
  8. LEFT JOIN YourTable b on a.MemberID=b.MemberID 
  9.   and b.LastChangeDate=PrevDate
  10.  
  11.  
Haven't tested that but it will be something like that.
May 13 '08 #2

ck9663
Expert 2.5K+
P: 2,878
And watch out for duplicates.

-- CK
May 13 '08 #3

benchpolo
100+
P: 142
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.lastchangedate<mh.lastchangedate
and a.ez_dbname=mh.ez_dbname order by a.memb_keyid, a.lastchangedate desc) as PREVOPTCD
, mh.createby as USERID
, u.username as USERNAME
, mh.lastchangedate as LASTCHANGEDATE
from memb_company mc
inner join users u on mc.createby=u.id and mc.ez_dbname = u.ez_dbname
inner join memb_hphists mh on mc.memb_keyid=mh.memb_keyid and mc.ez_dbname=mh.ez_dbname
where convert(varchar,mh.lastchangedate,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.lastchangedate<mh.lastchangedate
and a.ez_dbname=mh.ez_dbname
order by a.memb_keyid, a.lastchangedate desc)
May 14 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.