Initial thoughts (although I fully expect the heavy hitters on this forum to come up with better suggestions i.e. my hero Jeff Moden), is that you could look at doing...

I used temp tables so you can just see the iterations of the data.  This will match your expected results.

DROP TABLE IF EXISTS #DateRanges
SELECT *,
  ...

• This reply was modified 2 months, 3 weeks ago by SoCal_DBD.

I think this is what you are looking for.

-- For each test_date, determine the last date to count in a 10 date range
; WITH DateAnalysis AS
(SELECT *,
...

• This reply was modified 2 months, 4 weeks ago by SoCal_DBD.
• This reply was modified 2 months, 4 weeks ago by SoCal_DBD.

You can also use the scalar function msdb.dbo.agent_datetime:

SELECT JobName = j.name,
       RunDateTime = msdb.dbo.agent_datetime(jh.run_date, jh.run_time),
      ...

This should get you pointed in the right direction.  Fully expect the heavy hitters to come up with some other great solutions.

-- Create a sample table
DROP TABLE...

I have a plan. I'm learning Spanish. I'm retiring in 18 months and plan to spend a year in Europe with a base in Spain. I'm training to live...

Scott's code is definitely more concise.  Here is another method leveraging LEAD

; WITH TaskData AS
(SELECT *,
        NextTask = LEAD(TaskName, 1,...

I love a good reason to use any of the widow functions.  Thank you to Kaj for some sample data.  I can't tell you how many times I've had to...

Awww man!!  Keep coming west Jeff!  Would love to see you make it to Orange County, CA one of these days.

Oh... Scrubs was SO good.  Very witty and brilliant.

Most watched does not equate to accurate.  They did have to pay out nearly $800 million due to false claims.

Please consider learning standard SQL. We don't have an IFF() construct; that was spreadsheets and perhaps some nonrelational languages. Take the time to write everything out as a CASE...

Couldn't it be as straightforward as this??

SELECT t.[pick your columns]
FROM SomeTable t
INNER JOIN (SELECT [ID#]
            FROM...

This is the output I get.  (In fact I did a copy/paste from my post).

It's Friday afternoon and this was a nice small example.  Here's my offering:

-- Create sample data
DROP TABLE IF EXISTS #Example
CREATE TABLE #Example
    (DayNum INT,
...