are you sure ...?

if I understand ...to run the procedure with EXECUTE AS 'sa' I have to give a user a grant to impersonate 'sa'.

Am I granting him the...

Hi Thom,

Feel daft asking, but instead of the values you manually inserted here:

FROM (VALUES(CONVERT(datetime,'2020-11-20T00:00:00.000'), 900),

(CONVERT(datetime,'2020-11-21T00:00:00.000'), 1700))V(D,T))

How do i get it to point at my actual table and get the...

If you give the USER (LOGIN actually here) permission to impersonate the sa, that is certainly a security risk, yes. Then would be able to then use EXECUTE AS LOGIN...

Because that exists in yoru data.... There is no element called "System" not "State", you have one called "System.State" and are trying to...

Cross post of Stack Overflow as well, for future readers.

I can't keep on giving answers on moving goal posts. I have shown how you would do this, I will leave it to you to implement.

JSON_VALUE(v.value, '$.fields.System.State.newValue') will not work...

That's exactly what my solution does treat the data as... D is defined as a datetime,...

Because System.State is in value.fields, you're looking for value."System.ID", which doesn't exist. System.State is also a further JSON value. You can short cut to value.fields in the OPENJSON call, which...

I would personally convert your int representation of a Time (which i really recommend changing, there's a time datatype specifically for storing times) to a time. Then get the difference...

Then I don't understand your point here. You suggested that "Cross Apply was overkilling",...

CROSS APPLY has nothing to do with the performance there; are you actually suggesting that OPENJSON is slow?

By "show...

Why do you think it's "overkilling"?

If, however, you have valid JSON, then simply delimit identify the column names in your WITH clause:

DECLARE @JSON nvarchar(MAX) = N'{
    "Count":56,
  ...

The JSON you've given us isn't even valid. If we take the time to format it properly, there's plenty of problems.

{
    "count": 56,
 ...

@AdjustMode -

it should Calculate the days between the dates as 0 Exclusive or 1 Inclusive of the (first day only).

but actually it excludes for both first day and end...