SHARE
TWEET

Huntero Hp loss

PRP_UA Jan 14th, 2020 220 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. declare @periodstart nvarchar(20) = '2020-01-04 00:00:00';
  2. declare @periodend nvarchar (20) = '2020-01-13 23:59:59';
  3. --SET  @periodend = DATEADD(hh, -6, SYSDATETIME())
  4.  
  5. DECLARE @WorldID  int = 1 -- 1, 2, 3, 4
  6. DECLARE @ClientVer nvarchar (20) = '0.0.79'
  7.  
  8. ;WITH HealthPoint
  9.     AS
  10.     (
  11.         SELECT rri.[UserID]
  12.             , WorldId
  13.             , RoomNumber
  14.             , healing
  15.             , HealthPoints AS Health
  16.             , registrationclientversion AS version
  17.             , timestamp
  18.             , roomname
  19.             , LEAD (HealthPoints) OVER (PARTITION  BY rri.UserID, WorldId, RunNumber ORDER BY RoomNumber) AS HealthAfter
  20.         FROM RunRoomInfo AS rri
  21.         JOIN users  
  22.          ON users.userid = rri.userid
  23. WHERE  worldid = @WorldID
  24.    AND registrationclientversion = @ClientVer
  25.    AND timestamp BETWEEN @periodstart AND @periodend
  26.  
  27.     )
  28. ,HealthScore
  29. AS
  30. (
  31.     SELECT UserID
  32.         , RoomNumber
  33.         , health
  34.         , Health - healthafter AS HpLoss
  35.     FROM HealthPoint
  36.     WHERE  HealthAfter IS NOT NULL
  37. )
  38.  
  39.  
  40. SELECT roomnumber
  41.     , count(DISTINCT userid) Users
  42.     , count (userid) Attempts
  43.     , avg(health) AvgHp
  44.     , -1*avg(hploss) AvgHPLoss
  45. FROM HealthScore
  46. WHERE hploss BETWEEN -500 AND 5000
  47.     AND roomnumber != 0  AND health < 9999
  48. GROUP BY roomnumber
  49. ORDER BY roomnumber
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