473,320 Members | 2,164 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Comparing the previous record

benchpolo
142 100+
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
3 2831
Delerna
1,134 Expert 1GB
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
2,878 Expert 2GB
And watch out for duplicates.

-- CK
May 13 '08 #3
benchpolo
142 100+
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

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

Similar topics

10
by: george | last post by:
Can anyone help? I query a database and return a result on the column "reference". There might be 7 listings. Each row is displayed in a table, with links through to a detail page. I am working...
5
by: Derek Cooper | last post by:
I hope you can help me. I posted this in the microsoft sql server newsgroup a few days ago and got no response so I thought I'd try here. If I can provide any clarification I'll be glad to do so....
5
by: Curtis Gilchrist | last post by:
I am required to read in records from a file and store them in descending order by an customer number, which is a c-style string of length 5. I am storing these records in a linked list. My...
5
by: Steve Strik | last post by:
My Problem: I have created a database here at work that is exhibiting some very strange behaviour. Essentially the database is structured in a manner where one table is a master record table...
0
by: richardkreidl | last post by:
I have the following hash script that I use to compare two text files. 'Class Public Class FileComparison Public Class FileComparisonException Public Enum ExceptionType U 'Unknown A 'Add...
8
by: Jeff | last post by:
A client wants a press of the Enter key in a field on a continuous form to grab the value of that field from the previous record. But if they have typed a value and then hit Enter it shouldn't. ...
1
by: roveagh1 | last post by:
Hi I've been using the 2 year old link below to repeat values from previous record field into current corresponding field. It's worked fine for text but the last piece of advice was to use the same...
8
by: patriciashoe | last post by:
I have a database that features a number of numeric fields compiled during a year. Example: For each of 13 schools there is one record for each year. How do I construct a report that compares this...
11
by: jennifersalcido | last post by:
Hello All, I am working with two tables that contain inventory data: 1) INVENTORY contains item_number, description, etc. This data is relatively straight-forward, one record per item_number....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.