SHARE
TWEET

Huntero Passrate

PRP_UA Jan 13th, 2020 228 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-14 23:59:59';
  3. --declare @periodend nvarchar (20) = '2020-01-10 23:59:59'
  4.  
  5. DECLARE @ClientVer nvarchar (20) = '0.0.79'
  6.  
  7. DECLARE @WorldID  int = 4 -- 1, 2, 3, 4
  8.  
  9. DECLARE @lvl_st  int;
  10. DECLARE @lvl_end  int;
  11.  
  12. SET @lvl_st   = 1 SET @lvl_end  = 4
  13.   SET @lvl_st   = 6 SET @lvl_end   = 9
  14.   SET @lvl_st   = 11 SET @lvl_end   = 14
  15. --  SET @lvl_st   = 16 SET @lvl_end   = 19
  16. --  SET @lvl_st   = 21 SET @lvl_end   = 24
  17. --  SET @lvl_st   = 26 SET @lvl_end   = 29
  18. --  SET @lvl_st   = 31 SET @lvl_end   = 34
  19. --  SET @lvl_st   = 36 SET @lvl_end   = 39
  20. --  SET @lvl_st   = 41 SET @lvl_end   = 44
  21. --  SET @lvl_st   = 46 SET @lvl_end   = 49
  22.  
  23.  
  24. ;With Fails AS (
  25.     select roomname AS RM
  26.     , count(userid) AS 'Users'
  27.     from finishrun
  28.  
  29.     where worldid = @WorldID
  30.     and timestamp between @Periodstart and @periodend
  31.     and registrationcountry not in ('ua', 'cn')
  32.     and IsLeft = 0 and roomnumber != -1
  33.    -- and roomnumber  between @lvl_st and @lvl_end  -- for common lvls
  34.        and roomnumber % 5 = 0 -- for bosses and angels
  35.     and ClientVersion = @ClientVer
  36.     group by roomname
  37. )
  38.  
  39.  
  40. select roomname
  41. , count(distinct(userid))  as 'DUserS'
  42. , count(userid) as 'UserS'
  43. , min(Fails.[Users]) AS 'Fails'
  44. from runroominfo AS rri LEFT JOIN Fails ON Fails.RM = rri.RoomName
  45. where worldid = @WorldID
  46.     and timestamp between @Periodstart and @periodend
  47.     and registrationcountry not in ('ua', 'cn')
  48.    -- and roomnumber  between @lvl_st and @lvl_end -- for common lvls
  49.     and roomnumber % 5 = 0 -- for bosses
  50.     and ClientVersion = @ClientVer
  51.     group by roomname
  52.     order by roomname
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