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 1 year, 5 months 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...

;WITH CTE AS
(
    SELECT DISTINCT CustomerCode
      FROM myTable
)
SELECT B.*
  FROM CTE A
 CROSS APPLY(SELECT TOP(12) *
 ...

Nested CASE expressions are very hard to read, because it quickly becomes difficult to tell exactly where you are in the nesting.  Here is an example that uses a single...

00000 is an integer, so you are explicitly converting your value to char, but then implicitly converting it back to integer when you add it to 00000.  You want to...

LEAD is a windowed function - what makes you think it is going to...