Skip to content

Instantly share code, notes, and snippets.

@sjwaight

sjwaight/asa-query.sql

Last active January 11, 2018 07:41
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save sjwaight/c20f38b739df224656d84ea778f1b7b0 to your computer and use it in GitHub Desktop.
Save sjwaight/c20f38b739df224656d84ea778f1b7b0 to your computer and use it in GitHub Desktop.
Download ZIP
Azure Stream Analytics Query showing how we can filter data based on arrays in reference data.
Raw
asa-query.sql
SELECT
RD.DispenserEventId AS EventId,
RD.DispenserNumber,
RD.LocationId,
RD.EventTimeUTC AS Timestamp
INTO
ehpartnerdata
FROM
ehrawdispenserfeed RD
JOIN siteandispenserlist SDL ON RD.LocationId = SDL.LocationId
CROSS APPLY GetArrayElements(SDL.PartnerDispensers) As SiteDispenser
WHERE
RD.EventType = 2
AND
SiteDispenser.ArrayValue = RD.DispenserNumber
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment