1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
|
SELECT u.UserID,
b.BuildingID,
f.Name AS NameFloors,
r.Name AS Rooms,
d.DeviceID,
e.EquipmentID,
AVG(c.value) as consomation
FROM dbo.Users u
LEFT JOIN dbo.Buildings b On u.UserID=b.UserId
LEFT JOIN dbo.Floors f On b.BuildingID=f.BuildingID
LEFT JOIN dbo.Rooms r On f.FloorID=r.FloorID
LEFT JOIN dbo.Equipments e On r.RoomID=e.RoomID
LEFT JOIN dbo.EquipmentProperties ep On e.EquipmentID = ep.EquipmentID
LEFT JOIN dbo.PropertiesInstances p On ep.EquipmentPropertiesID = p.EquipmentPropertiesID
LEFT JOIN dbo.Consumptions c On p.PropertiesInstanceID=c.PropertiesInstanceID
left JOIN dbo.EquipmentDevices ed On e.EquipmentID = ed.EquipmentID
left JOIN dbo.UserDevices ud On ud.UserDeviceID = ed.UserDeviceID
left JOIN dbo.Devices d On ud.DeviceID = d.DeviceID
WHERE p.ProprietesName =2
AND (e.ZwaveType=0 or e.ZwaveType=1)
AND c.Unit=2
AND convert (varchar(MAX) , dateadd(d, -180, getdate() ) , 103 )=convert (varchar(MAX) , c.date , 103)
GROUP BY u.UserID,
b.BuildingID,
f.Name,
r.Name,
d.DeviceID,
e.EquipmentID |
Partager