Flagging Rows Where A Value Changed From Previous Row

I have a query that is tracking production. Whenever a unit is produced I capture the timestamp, part number, delta time, and what the cycle time should have been, as well as othe

Solution 1:

As noted by SQLHound, if you're using SQL Server 2012 or another database that supports the LAG function then you can use this in your SQL query.

A SQL Server example:

SELECT
  partnumber,
  somevalue,
  CASEWHENLAG(partnumber, 1) OVER (ORDERBY partnumber) <> partnumber THEN1ELSE0ENDAS PartNumberChangeOver
FROM ntest;

see it on SQL Fiddle

If you're source database doesn't support LAG, then can you tell us what database/version it is so we can try to provide an alternative solution.

Update: Additional example using a CTE, for SQL Server versions prior to 2012:

WITH ntestCTE
AS (SELECT
  rownum = ROW_NUMBER() OVER (ORDERBY ntest.partnumber),
  ntest.partnumber,
  ntest.somevalue
FROM ntest)
SELECT
  ntestCTE.partnumber,
  ntestCTE.somevalue,
  CASEWHEN ntestCTE.partnumber <> prev.partnumber THEN1ELSE0ENDAS PartNumberChangeOver
FROM ntestCTE
LEFT JOIN ntestCTE prev
  ON prev.rownum = ntestCTE.rownum - 1

See it on SQL Fiddle