You could add a constraint to the table to ensure that one of the values is null and one is not null:

ALTER TABLE DieselTransaction
  ADD CONSTRAINT...

You can do it in a more straight forward way using OFFSET:

DECLARE @PAGE INT = 1;

WHILE (@PAGE <= 5)
    BEGIN
    ...

I would find having "-- (Values)" between the insert and select statements very annoying.

For me having more new lines and some decent indentation would make it a lot more maintainable.

Why don't you want to make supplier and machine column accept null?

Why can't you use CTEs?[/quote]

I want to compile a view into a third party system that does not support CTEs. If it's not possible to accomplish without CTEs I...

I...

Drew showed me his wonderful code to do this same thing on another thread.  I did a deep dive on it and the concept is wonderfully simple and it...

Here is a comparison of the two methods:

/* Windowed Aggregate (MAX)  */Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead...

Here is a comparison of the two methods:

/* Windowed Aggregate (MAX)  */Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0,...

• This reply was modified 1 week, 5 days ago by  Jonathan AC Roberts. Reason: added execution plan

Instead of using LAG you can use OUTER APPLY. I've used both in this query so you can see the difference.

CREATE OR ALTER VIEW TestView AS
 ...

Well, if you call this fnTally with a start value of zero (0), you get two zeros (0). You can eliminate:

SELECT N = 0 WHERE @StartN = 0 UNION...

With a minor change to the function you could increase the functionality so that instead of specifying a bit for Zero or One for the starting value you could specify...

• This reply was modified 2 weeks, 3 days ago by  Jonathan AC Roberts. Reason: correction

Yes, lots of code and it's not my style of writing code so I haven't done much with it.

Yes, that would be a good enhancement to...