Add a parent group and set the 'Group on' using an expression that selects the Field(s) depending on the sort value and set page break on that group.

SELECT ISNULL((
SELECT price FROM products
WHERE productname = 'bob'
),0) AS [price]

This will only work if only one or no row is found in matching

Also, do you have some proof of the COUNT(*) vs the COUNT(1) thing?  I'm not talking about someone's rhetorical "proof" either.  I'm talking about a demonstrable and repeatable bit...

We are small shop and we don't get to hire any dedicated consultant for performance tuning.  Everything has to be done by us!

Thanks everyone. Forgive me if...

I am lucky that I have a good job (Dev/DBA) that I enjoy with a good wage, but no upward career path other than management, although 36 years ago when...

getting back to the original topic - are the posted questions getting worse.?

hell yes...there was one today that was "I have query, make it faster"  - several of us...

Changed my post above accordingly.

Sorry for the delay I thought I had already posted this.

This is a variation of my solution

SELECTEvStrName,
RN,
LeadRN-RN As [PassOB],
LeadActualArriveTime-ActualArriveTime AS [Time],
LeadOdometer-Odometer AS [Miles]
FROM(
SELECTEvStrName,RN,
ActualArriveTime,ActualDepartTime,Odometer,
LEAD(RN,1,0) OVER (ORDER BY EvStrName,RN)...

• This reply was modified 5 years, 10 months ago by David Burrows. Reason: Changed NonAmb in NOT EXISTS

Especially with my code :-/

All that being said, I'm scared to death of moving from 2016 to 2019 next year and MS has done nothing to quell the ongoing fears associated with their...

OK several things. The data is different which requires rewriting the query. How is this data generated? Is it going to continually change format as this will make any solutions...

Correct (or a temp...

Not elegant but...

SELECTEVSTRNAME,RN,PASSOB,[TIME],MILES
FROM(
SELECTEVSTRNAME,RN,
LEAD(RN,1,0) OVER (ORDER BY EVSTRNAME,RN)-RN AS [PASSOB],
LEAD(ACTUALARRIVETIME,1,0) OVER (ORDER BY EVSTRNAME,RN)-ACTUALDEPARTTIME AS [TIME],
LEAD(ODOMETER,1,0) OVER (ORDER BY EVSTRNAME,RN)-ODOMETER AS [MILES]
FROM(
SELECTEVSTRNAME,RN,ACTUALARRIVETIME,ACTUALDEPARTTIME,ODOMETER
FROM#test
WHEREpassob_bit = 0 OR RN = 1
UNION...

Sorry but I do not understand your reply.

What I...

This looks like a standard pivot, for six levels thus

SELECT[No_],
MAX(CASE WHEN Num = 1 THEN [Minimum Quantity] END) AS [MinQty1],
MAX(CASE WHEN Num = 1 THEN [Maximum Quantity]...