Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 compare 2 rows next each other Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, February 17, 2014 6:17 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, September 16, 2016 6:26 AM Points: 120, Visits: 652
 create table dbo.abc(id varchar(10), version int, stat varchar(10),typo varchar(10),enterdate datetime)insert into abc(id,version,stat,typo,enterdate)select 100,10,'live','yes',getdate()unionselect 100,10,'live','yes',getdate()-1unionselect 100,10,'live','no',getdate()-2unionselect 100,10,'nonlive','yes',getdate()unionselect 101,9,'live','no',getdate()unionselect 101,8,'live','yes',getdate()-2unionselect 101,11,'nonlive','yes',getdate()i want to get an output on below conditions:1. only live data2. find the current day id and compare with it recent previous live date. a. if the currunt and previous stat is different then show data. b. if current date and immediate previous live stat is same (eg. both are yes or both are no) ten do not show ay data for the that id.this query will be run daily to check currunt day data with recent previous data.
Post #1542324
 Posted Monday, February 17, 2014 6:35 PM
 SSC-Insane Group: General Forum Members Last Login: Monday, November 21, 2016 11:03 AM Points: 20,009, Visits: 18,255
 try this and see if it works`SELECT * FROM abc a1 INNER JOIN abc a2 ON a1.id = a2.id AND a1.enterdate = a2.enterdate+1 AND a1.stat = a2.stat WHERE a1.stat = 'Live' AND a1.typo <> a2.typo; ` Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNRPosting Performance Based Questions - Gail Shaw
Post #1542327
 Posted Tuesday, February 18, 2014 3:04 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:08 PM Points: 42,059, Visits: 39,442
 Post withdrawn. I thought this was going to turn out to be a massive double triangular join but a second look-see says it probably won't. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." Helpful Links:How to post code problemsHow to post performance problems
Post #1542770

 Permissions