Hello @Ørjan Vatsøy ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is to get the output in the format called out here , please do let us know if its not accurate.
At this time I see that you are using an UNION and so you are getting the NULL which I is not ehat you need . I do not have the fill data , nut I think You can use a JOIN . The key is to get the modelID on which we can join . On looking the model name it looked like the last 2 or three characters where different , so i used to substring function to get the actual name of the model
For out
substring(CustomerSensorValue.ArrayValue.model_id,0,(len(CustomerSensorValue.ArrayValue.model_id) - 3 )) as ModelNo_withOut_out
for in
substring(CustomerSensorValue.ArrayValue.model_id,0,(len(CustomerSensorValue.ArrayValue.model_id) - 2 )) as ModelNo_withOut_In
the below Query worked for me
WITH
Input1_P AS ( SELECT
CustomerSensorValue.ArrayValue.model_id AS modelId_Out,
substring(CustomerSensorValue.ArrayValue.model_id,0,(len(CustomerSensorValue.ArrayValue.model_id) - 3 )) as ModelNo_withOut_out ,
SensorData.ArrayValue.v AS realOut_Out
FROM json AS i PARTITION BY partitionId
CROSS APPLY GetArrayElements(i.gatewayData) AS CustomerSensorValue
CROSS APPLY GetArrayElements(CustomerSensorValue.ArrayValue.vqts) AS SensorData
WHERE CustomerSensorValue.ArrayValue.model_id LIKE '%Out%'
) ,
Input2_P AS (
SELECT
CustomerSensorValue.ArrayValue.model_id AS modelId_In,
substring(CustomerSensorValue.ArrayValue.model_id,0,(len(CustomerSensorValue.ArrayValue.model_id) - 2 )) as ModelNo_withOut_In ,
SensorData.ArrayValue.v AS realOut_In
FROM json AS i PARTITION BY partitionId
CROSS APPLY GetArrayElements(i.gatewayData) AS CustomerSensorValue
CROSS APPLY GetArrayElements(CustomerSensorValue.ArrayValue.vqts) AS SensorData
WHERE CustomerSensorValue.ArrayValue.model_id LIKE '%In%'
) ,
Outgoing AS (
SELECT Input1_P.realOut_Out, Input1_P.modelId_Out
,Input2_P.modelId_In,Input2_P.realOut_In
FROM Input1_P
JOIN Input2_P
On Input1_P.ModelNo_withOut_out = Input2_P.ModelNo_withOut_In
AND DATEDIFF(minute,Input1_P,Input2_P) BETWEEN 0 AND 15)
SELECT *
FROM Outgoing
Please do let me if you have any queries.
Thanks
Himanshu
- Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
- Want a reminder to come back and check responses? Here is how to subscribe to a notification
- If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators