Huntero Passrate

declare @periodstart nvarchar(20) = '2020-01-04 00:00:00';
declare @periodend nvarchar (20) = '2020-01-14 23:59:59';
--declare @periodend nvarchar (20) = '2020-01-10 23:59:59'

DECLARE @ClientVer nvarchar (20) = '0.0.79'

DECLARE @WorldID  int = 4 -- 1, 2, 3, 4

DECLARE @lvl_st  int;
DECLARE @lvl_end  int;

SET @lvl_st   = 1 SET @lvl_end  = 4
  SET @lvl_st   = 6 SET @lvl_end   = 9
  SET @lvl_st   = 11 SET @lvl_end   = 14
--  SET @lvl_st   = 16 SET @lvl_end   = 19
--  SET @lvl_st   = 21 SET @lvl_end   = 24
--  SET @lvl_st   = 26 SET @lvl_end   = 29
--  SET @lvl_st   = 31 SET @lvl_end   = 34
--  SET @lvl_st   = 36 SET @lvl_end   = 39
--  SET @lvl_st   = 41 SET @lvl_end   = 44
--  SET @lvl_st   = 46 SET @lvl_end   = 49


;With Fails AS (
    select roomname AS RM
    , count(userid) AS 'Users'
    from finishrun

    where worldid = @WorldID
    and timestamp between @Periodstart and @periodend
    and registrationcountry not in ('ua', 'cn')
    and IsLeft = 0 and roomnumber != -1
   -- and roomnumber  between @lvl_st and @lvl_end  -- for common lvls
       and roomnumber % 5 = 0 -- for bosses and angels
    and ClientVersion = @ClientVer
    group by roomname
)


select roomname
, count(distinct(userid))  as 'DUserS'
, count(userid) as 'UserS'
, min(Fails.[Users]) AS 'Fails'
from runroominfo AS rri LEFT JOIN Fails ON Fails.RM = rri.RoomName
where worldid = @WorldID
    and timestamp between @Periodstart and @periodend
    and registrationcountry not in ('ua', 'cn')
   -- and roomnumber  between @lvl_st and @lvl_end -- for common lvls
    and roomnumber % 5 = 0 -- for bosses
    and ClientVersion = @ClientVer
    group by roomname
    order by roomname