Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Set Variabes --
- DECLARE @name VARCHAR(3) = 'L20'
- DECLARE @cable int = (SELECT
- cd.id
- FROM
- dbo.WTG_ID wd JOIN
- dbo.power_wtg p ON wd.id = p.wtgid,
- dbo.data d JOIN
- dbo.Cable_ID cd ON cd.id = d.cable
- WHERE
- wd.name = @name and
- wd.string = cd.string and
- wd.position = cd.wtgs
- GROUP BY
- cd.id)
- -- Query --
- select
- case
- WHEN d.KP < 8321.399 THEN '1) K20'
- WHEN d.KP < 8336.499 THEN '2) K20 J-Tube'
- WHEN d.KP < 8356.499 THEN '3) K20 CPS'
- WHEN d.KP < 9392.977 THEN '4) K20 - L20'
- WHEN d.KP < 9412.977 THEN '5) L20 CPS'
- WHEN d.KP < 9428.077 THEN '6) L20 J-Tube'
- else '7) L20'
- end as Location,
- case
- WHEN d.temp < 21 THEN '20'
- WHEN d.temp < 22 THEN '21'
- WHEN d.temp < 23 THEN '22'
- WHEN d.temp < 24 THEN '23'
- WHEN d.temp < 25 THEN '24'
- WHEN d.temp < 26 THEN '25'
- WHEN d.temp < 27 THEN '26'
- WHEN d.temp < 28 THEN '27'
- WHEN d.temp < 29 THEN '28'
- WHEN d.temp < 30 THEN '29'
- WHEN d.temp < 31 THEN '30'
- WHEN d.temp < 32 THEN '31'
- else '32'
- end as temp,
- count (d.temp) as [frequency every 2 hours],
- count (distinct(datepart(day, d.time))) as [frequency every day],
- count (distinct(datepart(week, d.time))) as [frequency every week],
- min(d.KP) as 'Min KP',
- max(d.KP) as 'Max KP',
- avg(d.KP) as 'Avg KP'
- from
- data d
- where
- d.temp > 20 and
- d.cable = @cable
- group by
- case
- WHEN d.KP < 8321.399 THEN '1) K20'
- WHEN d.KP < 8336.499 THEN '2) K20 J-Tube'
- WHEN d.KP < 8356.499 THEN '3) K20 CPS'
- WHEN d.KP < 9392.977 THEN '4) K20 - L20'
- WHEN d.KP < 9412.977 THEN '5) L20 CPS'
- WHEN d.KP < 9428.077 THEN '6) L20 J-Tube'
- else '7) L20'
- end,
- case
- WHEN d.temp < 21 THEN '20'
- WHEN d.temp < 22 THEN '21'
- WHEN d.temp < 23 THEN '22'
- WHEN d.temp < 24 THEN '23'
- WHEN d.temp < 25 THEN '24'
- WHEN d.temp < 26 THEN '25'
- WHEN d.temp < 27 THEN '26'
- WHEN d.temp < 28 THEN '27'
- WHEN d.temp < 29 THEN '28'
- WHEN d.temp < 30 THEN '29'
- WHEN d.temp < 31 THEN '30'
- WHEN d.temp < 32 THEN '31'
- else '32'
- end
- ORDER BY
- 1 asc, 2 asc
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement