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
|
WITH CTE AS (
SELECT
Calculated_Site as Site,
Calculated_Salle as Salle,
-- Recherche du nom du TDHQ
SUBSTRING(intouch_comment,charindex ('-T',intouch_comment)+1,charindex (' - ',intouch_comment) - charindex ('-T',intouch_comment)) as TDHQ,
-- Recherche de l'intensité par phase ou de la puissance TDHQ
replace(right(intouch_comment,2),'le','P') as Parametre,
round(value,2) as Valeur
FROM geteb.dbo.t_ConfigInTouch,runtime.dbo.v_AnalogHistory
where
t_ConfigInTouch.InTouch_TagName=v_AnalogHistory.TagName
AND Calculated_Site='MSO'
AND TagName like '%TD[HM][QT]%'
AND (InTouch_Comment not like '%tané IN%' and InTouch_Comment not like '%TOR%' and InTouch_Comment not like '%réact%'and InTouch_Comment not like '%appar%')
AND (InTouch_EngUnits like '%A%' or InTouch_EngUnits like '%kW%')
AND wwResolution='3600000'
AND wwRetrievalMode ='average'
AND wwVersion ='original'
AND DATETIME >DATEADD(HH,-1,GETDATE()) -- permet de recupérer la date et l'heure d'aujourdui -1 heure
AND Datetime <= GetDate() -- Date et heure d'aujourd'hui
)
SELECT Site, Salle, I1, I2, P
FROM CTE PIVOT (
MAX(Valeur)
FOR Parametre
IN( [I1], [I2], [P] )
)AS T
order by Salle |
Partager