SHARE
TWEET

Untitled

a guest Apr 18th, 2019 82 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. declare @time table (Timestamp datetime)
  2. declare @result2 table (DeviceId varchar(32), DeltaT float, [Timestamp] datetime,  Avg30DaysDeltaT float);
  3. declare @result1 table (DeviceId varchar(32), DeltaT float, [Timestamp] datetime,  Avg30DaysDeltaT float);
  4. declare @result3 table (DeviceId varchar(32), DeltaT float, [Timestamp] datetime,  Avg30DaysDeltaT float, [Latitude] float, [Longitude]float);
  5.  
  6. insert into @time select distinct Timestamp from [dbo].[Calculation] where [TimeStamp] >= '2019-03-01' AND [TimeStamp] <= '2019-04-17'
  7.  
  8. insert into  @result1 (DeviceId, DeltaT, [Timestamp],  Avg30DaysDeltaT)
  9.        
  10.             select c.DeviceId
  11.           , c.DeltaT
  12.           , c.TimeStamp
  13.           , c.Avg30DaysDeltaT
  14.         From [dbo].[Calculation] as c
  15.         where [TimeStamp] >= '2019-03-01' AND [TimeStamp] <= '2019-04-17'
  16.         order by Timestamp  desc
  17.        
  18.  
  19. insert into @result2 (DeviceId,  DeltaT, [Timestamp], Avg30DaysDeltaT)
  20.         SELECT          
  21.                 cply.DeviceId          
  22.               , cply.DeltaT
  23.               , t.[Timestamp]
  24.               , cply.Avg30DaysDeltaT        
  25.         FROM @time as t
  26.             CROSS APPLY (
  27.                 SELECT  TOP 20
  28.                       hc.DeviceId
  29.                     , hc.DeltaT as DeltaT
  30.                     , hc.TimeStamp
  31.                     , hc.Avg30DaysDeltaT as Avg30DaysDeltaT
  32.                 FROM @result1  AS hc
  33.                     order by hc.DeltaT desc)as cply
  34.                     where t.TimeStamp >= '2019-03-01' AND t.[TimeStamp] <= '2019-04-17'    
  35.  
  36. --select * from @result2
  37. --order by Timestamp desc
  38.  
  39. insert into @result3 (DeviceId , DeltaT, [Timestamp],  Avg30DaysDeltaT, [Latitude], [Longitude])
  40. select  r2.DeviceId
  41.         , r2.DeltaT
  42.         , r2.TimeStamp
  43.         , r2.Avg30DaysDeltaT
  44.         , s.[Latitude ] as [Latitude]
  45.         ,s.[Longitude ] as [Longitude]
  46. from @result2 as r2
  47. inner join [dbo].[Structure] as s
  48.     on s.DeviceId = r2.DeviceId
  49.     where r2.TimeStamp >= '2019-03-01' AND r2.[TimeStamp] <= '2019-04-17'
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top