Migration Settings

CREATE TABLE MigrationZoneMapping (serverId INT, sensorId INT, zoneId VARCHAR(50))

INSERT INTO MigrationZoneMapping (serverId, sensorId, zoneId) VALUES
(ENTER_SERVER_ID_HERE, ENTER_SENSOR_ID_HERE, 'ZONE_ID_HERE'),
(ENTER_SERVER_ID_HERE, ENTER_SENSOR_ID_HERE, 'ZONE_ID_HERE'),
(ENTER_SERVER_ID_HERE, ENTER_SENSOR_ID_HERE, 'ZONE_ID_HERE')




SELECT
mzm.zoneId,
CONVERT(
VARCHAR(33),
DATEADD(hour, DATEDIFF(hour, 0, sd.ServerDate), 0),
126
) + 'Z' as recordDate,
CONVERT(INT, SUM(sd.ValueA)) as ins,
CONVERT(INT, SUM(sd.ValueB)) as outs,
'ENTER_ORG_ID_HERE' as orgId
FROM sensordata sd
INNER JOIN sensors ss ON (sd.SensorId = ss.sensorId)
INNER JOIN servers sr ON (ss.ServerId = sr.serverid)
INNER JOIN MigrationZoneMapping mzm ON (sr.serverId = mzm.serverid AND ss.sensorId = mzm.sensorId)
INNER JOIN Facility f ON (sr.ParentFacility = f.Facility)
WHERE f.ParentRegion = ENTER_PARENT_REGION_ID_HERE
GROUP BY
DATEADD(hour, DATEDIFF(hour, 0, sd.ServerDate), 0),
DATEADD(hour, (f.tzoffset / 10000), DATEADD(hour, DATEDIFF(hour, 0, sd.ServerDate), 0)), mzm.zoneId
ORDER BY recordDate;