compare 2 rows next each other

• scottichrosaviakosmos

  SSCommitted

  Points: 1889

  More actions

  February 17, 2014 at 6:17 pm

  #289633

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

  union

  select 100,10,'live','yes',getdate()-1

  union

  select 100,10,'live','no',getdate()-2

  union

  select 100,10,'nonlive','yes',getdate()

  union

  select 101,9,'live','no',getdate()

  union

  select 101,8,'live','yes',getdate()-2

  union

  select 101,11,'nonlive','yes',getdate()

  i want to get an output on below conditions:

  1. only live data

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

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 17, 2014 at 6:35 pm

  #1689697

  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 CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Jeff Moden

  SSC Guru

  Points: 1004318

  More actions

  February 18, 2014 at 3:04 pm

  #1690075

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)