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 33 34 35 36 37 38 39 40
|
SELECT u.UserID, b.BuildingID,f.Name AS NameFloors,
r.Name AS Rooms,
e.EquipmentID,
AVG(c.value) AS Temerpartue
FROM
dbo.Consumptions c,
dbo.PropertiesInstances p,
dbo.Buildings b,
dbo.Users u,
dbo.EquipmentProperties ep,
dbo.Equipments e,
dbo.Floors f,
dbo.Rooms r
WHERE
u.UserID=b.UserId
AND
b.BuildingID=f.BuildingID
AND
f.FloorID=r.FloorID
AND
r.RoomID=e.RoomID
AND
e.EquipmentID=ep.EquipmentID
AND
ep.EquipmentPropertiesID=p.EquipmentPropertiesID
AND
p.PropertiesInstanceID=c.PropertiesInstanceID
AND p.ProprietesName =6 AND e.ZwaveType=3
AND convert (varchar(MAX) , dateadd(d, -112, getdate() ) , 103 ) = convert (varchar(MAX) , c.date , 103)
GROUP BY u.UserID,
b.BuildingID,
f.Name,
r.Name,
e.EquipmentID |
Partager