Skip to content

Instantly share code, notes, and snippets.

@1nstinct

1nstinct/1111

Created January 17, 2018 11:36
Show Gist options
  • Save 1nstinct/f58c2c3ee1d63341c4af8ab7177920a2 to your computer and use it in GitHub Desktop.
Save 1nstinct/f58c2c3ee1d63341c4af8ab7177920a2 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
1111
SELECT
(SELECT
TOP 1 u.*
FROM
actionDetail d
LEFT JOIN actionLog l ON l.actionId = d.actionId
LEFT JOIN users u ON l.userId = u.userId
WHERE d.actionId = @actionId
FOR JSON PATH) AS userData,
(SELECT
TOP 1 dz.*
FROM
actionDetail d
LEFT JOIN actionLog l ON l.actionId = d.actionId
LEFT JOIN dropzones dz ON l.dropzoneId = dz.dropzoneId
WHERE d.actionId = @actionId FOR JSON PATH) AS dropzoneData,
(SELECT
TOP 1 d.time
FROM
actionDetail d
LEFT JOIN actionLog l ON l.actionId = d.actionId
WHERE d.actionId = @actionId
ORDER BY d.time ASC) AS actionTime,
(SELECT DATEDIFF(ss, a1.time, a2.time)
FROM
actionMoments a1
LEFT JOIN actionMoments a2 ON a1.actionId = a2.actionId AND a2.actionMomentTypeId = 5
WHERE a1.actionId = @actionId AND a1.actionMomentTypeId = 3) AS freefallTime,
(SELECT
speedMaxV,
distanceH,
distanceV
FROM actionMoments
WHERE
actionMomentTypeId = 4
AND actionId = @actionId
FOR JSON PATH) AS jumpDetails,
(SELECT DATEDIFF(ss, a1.time, a2.time)
FROM
actionMoments a1
LEFT JOIN actionMoments a2 ON a1.actionId = a2.actionId AND a2.actionMomentTypeId = 6
WHERE a1.actionId = @actionId AND a1.actionMomentTypeId = 5) AS deploymentTime,
(SELECT d.baroAlti
FROM
actionDetail d
LEFT JOIN actionMoments m ON m.startActionDetailId = d.actionDetailId
WHERE m.actionId = @actionId AND m.actionMomentTypeId = 5
FOR JSON PATH) AS deploymentDetails,
(SELECT DATEDIFF(ss, a1.time, a2.time)
FROM
actionMoments a1
LEFT JOIN actionMoments a2 ON a1.actionId = a2.actionId AND a2.actionMomentTypeId = 7
WHERE a1.actionId = @actionId AND a1.actionMomentTypeId = 6) AS underCanopyTime,
(SELECT
distanceH,
speedMaxH,
speedMaxV
FROM actionMoments
WHERE
actionMomentTypeId = 6
AND actionId = @actionId
FOR JSON PATH) AS underCanopyDetails,
(SELECT
time
FROM actionMoments
WHERE
actionMomentTypeId = 7
AND actionId = @actionId
FOR JSON PATH) AS landingDetails,
(SELECT time
FROM actionMoments
WHERE actionId = @actionId AND actionMomentTypeId = 1) AS planeTakeOff,
(SELECT DATEDIFF(ss, a1.time, a2.time)
FROM
actionMoments a1
LEFT JOIN actionMoments a2 ON a1.actionId = a2.actionId AND a2.actionMomentTypeId = 3
WHERE a1.actionId = @actionId AND a1.actionMomentTypeId = 1) AS planeTotalTime,
(SELECT altitude
FROM actionMoments
WHERE actionId = @actionId AND actionMomentTypeId = 3) AS planeExitHeight,
(SELECT gpsSpeed
FROM
actionDetail d
LEFT JOIN actionMoments m ON m.startActionDetailId = d.actionDetailId
WHERE m.actionId = @actionId AND m.actionMomentTypeId = 3) AS planeExitGroundSpeed,
(SELECT DISTINCT d.lat AS y, d.lon AS x
FROM
actionDetail d
WHERE
d.actionDetailId >= (SELECT startActionDetailId
FROM actionMoments
WHERE actionId = @actionId AND actionMomentTypeId = 4)
AND d.actionDetailId < (SELECT startActionDetailId
FROM actionMoments
WHERE actionId = @actionId AND actionMomentTypeId = 5)
FOR JSON PATH) AS freeFallLatLon,
(SELECT DISTINCT ROUND(d.lat, 3) AS y, ROUND(d.lon, 3) AS x
FROM
actionDetail d
WHERE
d.actionDetailId >= (SELECT startActionDetailId
FROM actionMoments
WHERE actionId = @actionId AND actionMomentTypeId = 6)
AND d.actionDetailId < (SELECT startActionDetailId
FROM actionMoments
WHERE actionId = @actionId AND actionMomentTypeId = 7)
FOR JSON PATH) AS canopyLatLon
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment