根据服务日期从JSON格式列中指定正确的负责人

Question

p.S.之前的帖子被删除了，我希望这次能接触到更多的人

我有庞大的历史数据集，我需要根据与客户端关联的所有行的服务日期，将值(ResponsibleName-来自JSON格式列)分配给一个新的"Responsible"列。

每个客户端可以有唯一的负责人(JSON列)用于特定日期范围：

1. 如果"代码"列包含"主管"，则将该员工仅分配到关联行作为"负责人">
2. 最后，如果Codes列包含Employee，则将JSON列(针对特定日期范围)中的相关责任人分配给Responsible

我对第一步没有问题，但我找不到实现第二步(最后一步)语句的解决方案。

原始历史记录表与客户端表连接：

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)

该表的输出：

客户端名称员工名称响应JSON123John SmithChris EvansJohn SmithTom Holland"，"ResponsibleStartDate"："2020-06-01"，"ResponsibleEndDate"：null｝]<123>John SmithChris EvansJohn SmithThomas AndersonJohn SmithTom HollandJohn SmithThomas Anderson2020-06-22John SmithElon Mask2020-07-01John SmithTom Holland"，"ResponsibleStartDate"："2020-06-01"，"ResponsibleEndDate"：null｝]

	服务日期	客户端Id	代码
1	22020-05-06	[{"可响应名称"："Kevin Costner"，"可响应开始日期"："2019-02-14"，{"可响应名称"："Tom Cruise"，"可响应开始日期"："2020-06-01"，"响应结束日期"：null}]	员工，办公室
20-05-08	123	远程主管
3	2020-05-11	[{quot；可响应名称"："Kevin Costner"，"可响应开始日期"："2019-02-14"，"应响应结束日期"："，"ResponsibleStartDate"："2020-06-01"，"ResponsibleEndDate"：null｝]	办公室员工
4	2020-05-15	123	[{quot；可响应名称"："Kevin Costner"，"可响应开始日期"："2019-02-14"，"响应结束日期"：《2020-05-31》}，{"可响应名称"："Tom Cruise"，"可响应开始日期"："2020-06-01"，"响应结束日期"：null}]	员工，办公室
5	2020-06-10	123	[{quot；可响应名称"：凯文·科斯特纳；，"可响应开始日期"："2019-02-14"，"可应答结束日期"："，"ResponsibleStartDate"："2020-06-01"，"ResponsibleEndDate"：null｝]	办公室主管
6	2020-06-17	123	[{quot；可响应名称"："Kevin Costner"，"可响应开始日期"："2019-02-14"，"响应结束日期"：《2020-05-31》}，{"可响应名称"："Tom Cruise"，"可响应开始日期"："2020-06-01"，"响应结束日期"：null}]	员工，远程
7	123	[{quot；可响应名称"："Kevin Costner"，"可响应开始日期"："2019-02-14"，"响应结束日期"："Cruise"，"ResponsibleStartDate"："2020-06-01"，"ResponsibleEndDate"：null}]	远程员工
8	123	远程主管

您似乎只想在Codes列不包含Supervisor时查找JSON。以下查询应该符合您的要求：

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<gt；小提琴