SHARE
TWEET

Huntero Loss on levels

PRP_UA Jan 29th, 2020 211 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DECLARE @periodstart nvarchar(20) = '2020-01-24 00:00:00';
  2. DECLARE @periodend nvarchar (20) = '2020-01-26 23:59:59';
  3. DECLARE @ClientVer nvarchar (20) = '0.0.105';
  4. DECLARE @Hours  INT = 24;
  5.  
  6. DECLARE @WorldID  INT = 1 ; -- 1, 2, 3, 4
  7.  
  8. ;WITH Fails AS (
  9.     SELECT
  10.     roomnumber AS RM,
  11.     COUNT(f.userid) AS 'Users'
  12.  
  13.     FROM Users  JOIN finishrun AS f ON users.userid=f.userid
  14.     WHERE worldid = @WorldID
  15.         AND TIMESTAMP BETWEEN f.created AND dateadd(hh,@Hours,f.created)
  16.         AND users.created BETWEEN @periodstart AND @periodend
  17.         AND IsLeft = 0 AND   roomnumber!=0 AND roomname!='-2'
  18.         AND registrationclientversion=@ClientVer
  19.         AND f.retention1d=1
  20.     GROUP BY roomnumber
  21. )
  22.  
  23.  
  24. SELECT roomnumber
  25. ,COUNT(DISTINCT(rri.userid))  AS 'Users'
  26. , COUNT(rri.userid) AS 'Attempts'
  27. , MIN(Fails.[Users]) AS 'Fails'
  28. ,avg(healthpoints) AS AvgHP
  29. ,avg(healing) AS Heal
  30. --,max(healing) as MaxHeal
  31. FROM runroominfo AS rri
  32.     LEFT JOIN fails ON Fails.RM = rri.roomnumber
  33.     LEFT JOIN users ON users.userid=rri.userid
  34. WHERE worldid = @WorldID
  35.     AND TIMESTAMP BETWEEN rri.created AND dateadd(hh,@Hours,rri.created)
  36.     AND rri.created BETWEEN @periodstart AND @periodend
  37.     AND roomnumber!=0 AND healthpoints<9999
  38.     and registrationclientversion=@clientver
  39. GROUP BY roomnumber
  40. 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