P.S. previous post was deleted, I hope this time it will reach more people.

I have huge history dataset and I need to assign value (ResponsibleName - from JSON format column) to a new 'Responsible' column based on Service Date for all rows associate with Client.

Each Client can have unique Responsible person (JSON column) for the specific date range:

1. If Codes Column contains Supervisor, then assign this Employee to only associated row as Responsible.
2. And finally, if Codes Column contains Employee, than assign associated Responsible person from the JSON column (for specific date range) to Responsible

I don't have issues with the first step, however I can't find a solution to implement the 2nd (last) statement.

Original History Table Joined with Client Table:

SELECT h.Id,
       h.ServiceDate
       h.ClientId,
       cl.ClientName,
       h.EmployeeName,
       cl.ResponsibleJSON,
       h.Codes
FROM History AS h
JOIN ClientTable AS cl
   ON (h.ClientId = cl.ClientId)

Output of that table:

I've created the table with all JSON data for all Clients:

SELECT  c.ClientId,
        c.ClientFullName,
        c.ResponsibleJSON,
        JSON_VALUE(X.VALUE,'$.ResponsibleName') AS ResponsibleName,
        JSON_VALUE(X.VALUE,'$.ResponsibleStartDate') AS ResponsibleStartDate,
        ISNULL(JSON_VALUE(X.VALUE,'$.ResponsibleEndDate'), '2999-12-31') AS ResponsibleEndDate

FROM ClientTable AS c
CROSS APPLY OPENJSON(c.ResponsibleJSON) AS X
ORDER BY c.ClientFullName, ResponsibleStartDate

And now I need to combine them somehow to produce column below:

Desired Output:

Code I need help with you can find below. I don't know if I can OUTER APPLY in CASE statement AND I think JOIN should be ON ClientId and check if ServiceDate between ResponsibleStartDate AND ResponsibleEndDate.

SELECT h.Id,
       h.ServiceDate
       h.ClientId,
       cl.ClientName,
       h.EmployeeName,
       cl.ResponsibleJSON,
       h.Codes,

       CASE
          WHEN h.Codes LIKE '%Supervisor%' THEN h.EmployeeName --- 1st statement
          WHEN --- here should be the second statement. I don't know how to implement it.
       END AS Responsible

FROM History AS h
JOIN ClientTable AS cl
   ON (h.ClientId = cl.ClientId)

CodePudding user response：

It seems you only want to lookup the JSON when the Codes column does not contain Supervisor. The following query should do what you want:

SELECT
  c.Id,
  c.ServiceDate,
  c.ClientId,
  c.ClientName,
  c.EmployeeName,
  c.Codes,
  Supervisor =
      CASE WHEN c.Codes LIKE '%Supervisor%' THEN c.EmployeeName
      ELSE (
          SELECT TOP (1)
            j.ResponsibleName
          FROM OPENJSON(c.ResponsibleJSON)
            WITH (
              ResponsibleName nvarchar(100),
              ResponsibleStartDate date,
              ResponsibleEndDate date
            ) j
          WHERE c.ServiceDate >= j.ResponsibleStartDate
            AND (j.ResponsibleEndDate IS NULL OR c.ServiceDate <= j.ResponsibleEndDate)
      )
      END
FROM ClientTable c;

db<>fiddle