Need help creating a view !!!

• badblock

  Newbie

  Points: 5

  More actions

  June 5, 2006 at 8:44 am

  #113365

  Hi all!

  I have a select problem that I just can't figure out!

  I have a database that stores inspection data.

   

  Table1

  InspectionID (Key)

  SerialNo

  Value

  InspectionDate

   

  I need to create a view that gives me the values of the last two inspections for each SerialNo like this:

  SerialNo - CurrentValue - PreviousValue

   

  Can anyone help me with this problem ??

  -Peter

• Bhushan

  Default port

  Points: 1427

  More actions

  June 5, 2006 at 9:54 am

  #641765

  declare @Table table

  (

  InspectionIDINT IDENTITY(1,1),

  SerialNoINT,

  [Value]INT,

  InspectionDateDATETIME DEFAULT GETDATE()

  )

  INSERT INTO @TABLE (SerialNo, [Value] )

  VALUES ( 1, 21)

  INSERT INTO @TABLE (SerialNo, [Value], iNSPECTIONdATE)

  VALUES ( 1, 23, gETDATE()+1)

  INSERT INTO @TABLE (SerialNo, [Value], iNSPECTIONdATE)

  VALUES ( 1, 9, gETDATE()+2)

  INSERT INTO @TABLE (SerialNo, [Value], iNSPECTIONdATE)

  VALUES ( 1, 7, gETDATE()+3)

  INSERT INTO @TABLE (SerialNo, [Value], iNSPECTIONdATE)

  VALUES ( 1, 27, gETDATE()+4)

  INSERT INTO @TABLE (SerialNo, [Value] )

  VALUES ( 2, 23)

  INSERT INTO @TABLE (SerialNo, [Value], iNSPECTIONdATE)

  VALUES ( 2, 19, gETDATE()+1)

  INSERT INTO @TABLE (SerialNo, [Value], iNSPECTIONdATE)

  VALUES ( 2, 24, gETDATE()+2)

  INSERT INTO @TABLE (SerialNo, [Value], iNSPECTIONdATE)

  VALUES ( 2, 25, gETDATE()+3)

  INSERT INTO @TABLE (SerialNo, [Value], iNSPECTIONdATE)

  VALUES ( 2, 27, gETDATE()+4)

  SELECT SerialNo,[Value] CurrentValue, (select TOP 1 [Value] PrevVal

  FROM @Table WHERE SerialNo = c.Srno and InspectionDate < c.InspDate

  Order by InspectionDate DESC) PreviousValue

  from @Table b

  JOIN (

  SELECT SerialNo Srno, Max(InspectionDate) InspDate

  FROM

  @TABLE

  Group By SerialNo) c

  ON b.SerialNo = c.Srno

  and b.InspectionDate = c.InspDate