To save others time:
CREATE TABLE #Sample
  (RowID int,
  [Date] date,
  [Value] int,
  NextValue int,
  InSequence int);
GO
INSERT...

The query is going to be pretty "simple", going with an inner join is the right way to go. You're probably looking at a query something along the lines of:

This might get you started, but without knowing your end game, I have no idea what to aim for:
SELECT X.N.value('@label','nvarchar(50)') AS [Type],
       C.T.value('@label','nvarchar(50)') AS...

What you seem to be talking about there is more ofa data warehouse point of view, rather than a how you would store "normal" data. Data warehouses and "normal" data...

That's because dates are technically stored as numbers ,a value of 0 is 1900-01-01, 1 is 1900-01-02. Having a value of 20170516 would be 20,170,516 days after 1900-01-01. Considering that...

Unfortunately, the int type isn't really designed to be used as a date field. When you do, you end up having problems with this. Personally, I would add a column...

Without seeing the full original data, this is a guess, but I would imagine the problem is your first CASE statement, not your second, with regards to the expression:

Firstly, I'm actually a little more concerned about your WHERE statement of:
CONVERT(VARCHAR, GETDATE()-1, 101)
Why are you converting your date to a varchar? Is your field ompOrderDate also a...

What are you trying to achieve here? The reason you're getting 3 rows is because there are 3 rows of data.

You have a GROUP BY in your query...

As Grant explained, you can't shrink a database without first deleting data. If all of your data is "required" (perhaps for audit, historic purposes) then you have little or no...

Eating too much spam is not good for you.

SQL already runs top down already, so I'm not sure what your point is here. If you have 3 SP's, each one will be executed in a top down order....