Please create sample data in a consumable format using the {;} Code button to include a script to create a TEMP table and insert data into that temp table.

Drew

Yes, there was an error in my code.  I was thinking that...

Couldn't it be as straightforward as this??

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

I used a calculated field to combine these into a single query.

WITH Stop_Fl AS
(
    SELECT *
        ...

This gives the exact same results as Jonathan's but only reads the near and next term data tables once each.

SELECT FP.UNDERLYING_SYMBOL,
    ...

• This reply was modified 9 months, 1 week ago by  drew.allen.

The problem with your question, is that you've not given enough data to rule out potential incorrect queries.  For example, SELECT * FROM dbo.history returns the correct results for your...

You need multiple OPENJSON functions.

select j.WorkId, r.[Busket Rule], r.Exit_Window, r.Startegy, r.Start_Date
from openjson(@json)
with (
WorkId int '$.WorkId',
OverrideRules NVARCHAR(MAX) '$.UpdateAttributes.OverrideRules' AS JSON
) j
CROSS APPLY OPENJSON(j.OverrideRules)
WITH (
[Busket Rule] varchar(200) '$."Busket Rule"',
Exit_Window...

This gives the exact same results as Jonathan's but only reads the near and next term data tables once each.

SELECT FP.UNDERLYING_SYMBOL,
      ...

One more thing.  There is no reason to use an explicit CAST here, and using an implicit CAST makes it much easier to read.

/* Explicit CAST ...

Please clean up your code before posting.  There are several issues with the code for your test data.

1. You used PERMANENT tables rather than TEMP tables.

   1. The easier you make...

The problem is that you haven't defined "total up" and "total down".

Is it a change on the field "up"?  If so, your data is incomplete.  A value can be up,...

This query produces the exact same results as your query with far fewer reads.

WITH Order_Statuses AS
(
SELECT h.order_number
 , h.order_date
 , q.[label]
 , q.orderstatus_src
 , q.orderstatus_tgt
 , h.order_status
...

For future reference, many people are wary of opening random files from the Internet.  If you want help, please post a script to create a TEMP table and insert the...

You can't generally define aliases INSIDE expressions.

Drew

PS: You're dates don't match.

Yes, that's a bit shorter and generally more efficient. I'm wondering if there were a lot more that 12 items per customer and the right indexes on the table...