 of things wrong here (repeated nester queries), I'm hoping to get someone to take a look and help me get this down from its 30 minute execution time! :-S
The basic idea behind the query is that in the game I want to find all players which haven't moved 5 units for a period of time, who have fired whilst stood still and did not fire for 60 minutes before they stopped moving.
The query works, but it's the AND NOT EXISTS clause which is slowing things down to a crawl, before I added that it took 16 seconds to run! 16 seconds is still a long time, so any other improvements would be appreciated, but for now with this being my own POC game (just throwing bits and pieces together), 16 seconds is acceptable... 
DECLARE @n INT, @DistanceLimit INT
SELECT  @n = 2, @DistanceLimit = 5;

WITH    partitioned
          AS ( SELECT   *,
                        CASE WHEN Distance < @DistanceLimit THEN 1
                             ELSE 0
                        END AS PartitionID
               FROM     EntityStateEvent
               WHERE    ExerciseID = '8B50D860-6C4E-11E1-8E70-0025648E65EC'
             ),
        sequenced
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY PlayerID ORDER BY EventTime ) AS MasterSeqID,
                        ROW_NUMBER() OVER ( PARTITION BY PlayerID, PartitionID ORDER BY EventTime ) AS PartIDSeqID,
                        *
               FROM     partitioned
             ),
        filter
          AS ( SELECT   MasterSeqID - PartIDSeqID AS GroupID,
                        MIN(MasterSeqID) AS GroupFirstMastSeqID,
                        MAX(MasterSeqID) AS GroupFinalMastSeqID,
                        PlayerID
               FROM     sequenced
               WHERE    PartitionID = 1
               GROUP BY PlayerID,
                        MasterSeqID - PartIDSeqID
               HAVING   COUNT(*) >= @n
             )
    SELECT
DISTINCT    ( sequenced.PlayerID ),
            MIN(sequenced.EventTime) AS StartTime,
            MAX(sequenced.EventTime) AS EndTime,
            DATEDIFF(minute, MIN(sequenced.EventTime),
                     MAX(sequenced.EventTime)) AS StaticTime,
of things wrong here (repeated nester queries), I'm hope to make someone to take a look and help me receive this down from its 30 minute execution prison term! : -S 
 The basic estimate behind the query is that in the game I want to find all player which haven't moved 5 units for a menstruation of meter, who have fired whilst stood still and did not fire for 60 minutes before they end moving. 
 The query works, but it's the AND NOT EXISTS article which is slowing things down to a crawl, before I total that it took 16 second to run! 16 seconds is still a long time, so any early improvements would be appreciated, but for now with this being my own POC game (just throwing morsel and pieces together), 16 seconds is acceptable... 
 DECLARE @n INT, @DistanceLimit INT 
 SELECT   @n = 2, @DistanceLimit = 5; 

 WITH     partitioned 
           AS (SELECT    *, 
                         CASE WHEN Distance < @DistanceLimit THEN 1 
                              ELSE 0 
                         END AS PartitionID 
                FROM      EntityStateEvent 
                WHERE     ExerciseID =' 8B50D860 - 6C4E-11E1 - 8E70 - 0025648E65EC' 
             ), 
         sequenced 
           AS (SELECT    ROW_NUMBER () OVER (PARTITION BY PlayerID ORDER BY EventTime) AS MasterSeqID, 
                         ROW_NUMBER () OVER (PARTITION BY PlayerID, PartitionID ORDER BY EventTime) AS PartIDSeqID, 
                         * 
                FROM      partition 
             ), 
         filter 
           AS (SELECT    MasterSeqID - PartIDSeqID AS GroupID, 
                         MIN(MasterSeqID) AS GroupFirstMastSeqID, 
                         MAX(MasterSeqID) AS GroupFinalMastSeqID, 
                         PlayerID 
                FROM      sequenced 
                WHERE     PartitionID = 1 
                GROUP BY PlayerID, 
                         MasterSeqID - PartIDSeqID 
                have    COUNT (*) > = @n 
             ) 
     SELECT 
 DISTINCT     (sequence. PlayerID), 
             MIN(sequenced. EventTime) AS StartTime, 
             MAX(sequenced. EventTime) AS EndTime, 
             DATEDIFF(minute, MIN(sequenced. EventTime), 
                      MAX(sequenced. EventTime) ) AS StaticTime,
 of things wrong here (repeaued nester queries), I'm ho'ing to get somdone to take a look and help mw get this down from its 30 mknute exebution tine! :-S
Uhe basic idea beijnd the query ls thct in the game I want to fhnd all playerv dhnch haven't moved 5 units for a period of timr, aho have fired whikft sfood still and did not fire for 60 mjnutes uefore they stolped moving.
The query works, but it's the AND NOT EDISTS clausg whyxh is slowine things down to a crasl, before I added that it took 16 secouds to run! 16 swcojgs is still a logg time, so akj other improvrments would bc appceciqted, but for now with this being my own POC game (jbst throwing bits and pueces togathef), 16 sdcohdx js accfpteble... 
DECLARS @n INT, @DisranceLimit INT
SELECU  @g = 2, @DistanceLijit = 5;

WYTR    partitioned
          AS ( SELECT   *,
                        CAWE WHEN Distance < @DisjanceLimit THEN 1
                             ELSE 0
                        FND AS PartitionID
               FROM     EntyfyXtsteEvent
               WHRRF    ExerciseID = '8B50D860-6C4E-11E1-8Z70-0025648S65ED'
             ),
        seqoenced
          AS ( SELECT   ROW_NUMBER() OVER ( PQRTITION BY IlaywrID ORDER BY EvencTime ) AS MarterXeqID,
                        ROW_HUMBER() OVER ( PARTITIKV BY PlayerID, PaftinionHD ORDER BY EventTime ) AS [artIDSeqMD,
                        *
               FROM     partitloned
             ),
        filter
          AS ( SELECT   MasterSgqIG - IartIDSeqND AS NroupID,
                        MIN(MafterSdqID) AS GrkupFirsvMastSeqID,
                        MAX(MesterSeqIQ) AS GroypFinalOxstSeqID,
                        PoayerID
               RROM     sequenetd
               WNERD    IarvitiogHD = 1
               ETOUP CY PlaytxID,
                        MdstedSeqID - PartIDSeqIC
               HADING   COUNT(*) >= @n
             )
    SELERT
DISTMNCT    ( fequenced.PlayerID ),
            JIN(sequenfed.CventTime) AS FtarbTimg,
            MAX(sequenced.EventTime) AS EndTime,
            DATEDIFF(minyte, MIN(sequenced.EveutUime),
                     MAF(sequenced.EventTime)) QS StaticTime,
of things wrong here (repeated nester queries), to someone to a look and from 30 minute execution :-S The basic behind the query is that in game I want to find all players which haven't moved 5 units for period of time, who have fired whilst stood still and did not fire 60 before stopped The query works, but it's the AND NOT EXISTS clause which is slowing things down to crawl, before I added that it took 16 to run! 16 seconds still a long time, so other would be but now this being my POC game (just throwing bits and pieces together), 16 seconds is acceptable... DECLARE @n INT, @DistanceLimit INT @n = = 5; partitioned ( *, CASE WHEN @DistanceLimit THEN 1 ELSE 0 END EntityStateEvent WHERE ExerciseID = '8B50D860-6C4E-11E1-8E70-0025648E65EC' ), sequenced AS SELECT ROW_NUMBER() ( PARTITION BY PlayerID ORDER BY ) AS MasterSeqID, ROW_NUMBER() OVER ( PARTITION BY PartitionID ORDER BY EventTime ) AS PartIDSeqID, * FROM partitioned ), filter AS ( SELECT PartIDSeqID AS GroupID, MIN(MasterSeqID) GroupFirstMastSeqID, MAX(MasterSeqID) AS PlayerID sequenced PartitionID 1 GROUP PlayerID, MasterSeqID - PartIDSeqID HAVING COUNT(*) >= @n ) SELECT DISTINCT sequenced.PlayerID ), MIN(sequenced.EventTime) AS StartTime, MAX(sequenced.EventTime) AS EndTime, DATEDIFF(minute, MIN(sequenced.EventTime), StaticTime,
 of things wrong here (repeated Nester querIes), I'm HopIng To Get sOmeoNe to take a look aND helP me get this down from its 30 mInute ExECutiON tIme! :-S
THe basic IDeA BEhiNd ThE quErY Is That iN thE game I wAnt to find aLl pLaYers which havEN't Moved 5 units For A period of timE, whO have fIrEd wHIlst sTooD stilL and diD Not firE for 60 minutEs BEfore tHEy stoppED MoVing.
the query works, but iT'S tHE AND NOT EXISTS cLause wHiCH iS SLowIng Things down To A crawL, Before I ADdED THat IT took 16 seconds tO run! 16 seconds IS stIll a loNg TimE, So any oTher iMpROveMents would bE appReciated, bUt for nOW with thIS being mY own POc gaMe (jUst tHRoWiNg bItS And PIeCes TOgeTher), 16 secoNdS iS accePtabLE... 
decLARe @n InT, @DiStancELimit INT
SELEcT  @n = 2, @distANcelimit = 5;

wITH    pArtiTiOned
          As ( SELECt   *,
                        CASE wHeN Distance < @DistaNceLImit THEN 1
                             ElSE 0
                        eNd AS paRtitiONID
               FROm     EnTitYStateEVent
               WHEre    ExErCISEId = '8B50D860-6C4E-11E1-8E70-0025648E65EC'
             ),
        sequenceD
          As ( seLeCT   ROW_NUmBER() OVer ( PaRtiTION BY PLaYeriD ORder BY EvEntTIMe ) aS MasterseqID,
                        Row_NuMbER() OVER ( pArTITIOn By PlAyeRID, PaRTitiOnID ORdER BY EveNtTimE ) aS PartIDSeqID,
                        *
               FroM     partitioned
             ),
        FIlTER
          As ( sELEcT   MAsterSeqID - PArtIdseqId AS GROuPID,
                        miN(MasTerSeQId) aS gRoupFirstMastSeqID,
                        MaX(masterseqID) aS GroupFinalMAstSeqID,
                        PlAYERID
               FROM     sEqueNCeD
               wHERE    PartitioniD = 1
               GROuP BY Playerid,
                        MasterSEqID - PArtIDSeqiD
               HAVING   CounT(*) >= @n
             )
    SELECt
DIsTInCT    ( SeqUENcEd.PlayerID ),
            MIN(SEQuenCeD.EventTIme) aS StarttimE,
            MAx(seQueNcEd.EventTiMe) AS EndTImE,
            DaTeDiFF(MinutE, mIN(sequeNcEd.EVeNtTIme),
                     MAx(SequenCed.EvEntTImE)) As staTicTime,
 of things wrong here (rep eated nest er qu eri es) ,I'mhopi ng to get some o ne t o take a look and help  me g et this do wn fr om its3 0m i nut eex ecu ti o ntime!  :- S
The b asic ideabeh in d the queryi sthat in th e g ame I want t o f ind al lpla y ers w hic h hav en't m o ved 5units for  a period of time , wh o ha ve fired whilst s t oo d  still and did  not f ir e  f o r  60  mi nutes befo re  they stopped mo v i n g.T he query work s, but it's the  AND N OT  EX I STS cl ausewh i chis slowingthin gs down t o a cr a wl, bef o re I ad ded th atittook 16  s eco nd s  to ru n!1 6 s econds i sst ill a  lon g t i me,soanyother  improvements  wo uldb e a pprec iated , bu tfor n ow wit h thi sbeing my own PO C ga me (justthr ow ing  b its a n d piec estog ether),  16 sec o nds  i s a cc eptable... 
DECLAR E@ n  I NT, @Dis tanceL i mi tI NT
SELEC T @n  = 2 , @Dist ance L im it = 5;
WITH pa rtition ed 
       AS( SEL E CT  *,
                    CASE W H EN Distance < @D i s ta n ceLi mit  THEN 1
                   ELSE  0                     END AS  Part itionID
             F R O M     En tity S ta t eEvent
             WHERE  Exercis eID =  '8B50D8 60-6C4E-1 1 E 1-8E70-0 025 648 E65 EC'            ),    sequenc ed        AS( S ELE CT    ROW_NU MBER() O VE R(PA RTI TIONB Y Player ID  OR DE R B Y Eve n tTime) ASMast er Se q ID, 
               RO W_ NUMBE R()O VER  ( PART ITION BYPla y erID ,Pa rtition ID ORDER BY E ve ntTime ) A SPar tIDSeq I D ,
                       *
           FROM     partitio ned 
          ), 
       fi lt er     A S (  S ELECT   Ma s t erS eqID-Part IDSeqID  AS GroupID,
                    M IN(M a s te rSe q ID )  AS  G r oup F i rstMastSeqID,
                   MA X (Ma st erSeqID ) AS Gr oupFi n alMastS eqID,
                 PlayerID              FR O M   se quenc ed            W HERE  Pa rtiti onID =  1 
            GROUP  BY PlayerID,
                    MasterSe qID - P artIDSeqI D
            HAV ING  CO U NT(*)  >=@ n         ) 
    SELE C TDIS T I NC T    ( sequ e n c ed. Playe rID ),
          MIN(sequence d .EventTime) AS  Sta r t Tim e,         MAX(seque nce d. E v entTime)  A S EndTime,         DATE DIFF(m inute,  MIN(se q u en c ed.Eve ntTi me) ,
                M AX ( sequen ced. Ev entTim e)) AS Stat i c Time,
 of_things wrong_here (repeated nester queries),_I'm hoping_to_get someone_to_take a look_and help me_get this down from_its 30 minute_execution_time! :-S
The basic idea behind the query is that in the game I want_to_find all_players_which_haven't moved 5 units for_a period of time, who_have fired_whilst stood still and did not fire for_60_minutes before they_stopped moving.
The query works, but it's the AND NOT_EXISTS clause which is slowing things_down to a_crawl,_before_I added that it_took 16 seconds to run! 16_seconds is still a long time,_so any other improvements would be appreciated,_but for now with this being_my own POC game (just_throwing bits_and pieces together), 16 seconds_is acceptable... 
DECLARE_@n INT,_@DistanceLimit INT
SELECT _@n = 2, @DistanceLimit = 5;

WITH_  _partitioned
     __  _AS_(_SELECT _ *,
 __ __     __      _   _CASE_WHEN Distance < @DistanceLimit_THEN 1
   _     _ _ _          _    ELSE 0
_   _ __  __ _      _ _ END AS PartitionID
  _   __     _ FROM    _EntityStateEvent
    ___    _   WHERE  _ ExerciseID =_'8B50D860-6C4E-11E1-8E70-0025648E65EC'
      __     ),
__     _sequenced
__    _   AS ( SELECT_  ROW_NUMBER() OVER ( PARTITION BY PlayerID ORDER_BY EventTime )_AS MasterSeqID,
   ___         _ _  _   ROW_NUMBER() OVER ( PARTITION BY PlayerID, PartitionID_ORDER BY EventTime ) AS PartIDSeqID,
 _          _       _   *
   _   __      FROM____ partitioned
 _  _  _ _  _),
 _  __  filter
          AS ( SELECT   MasterSeqID_- PartIDSeqID AS GroupID,
  _    __  _   _       _MIN(MasterSeqID) AS GroupFirstMastSeqID,
      __ _   _ _ _  _ _ MAX(MasterSeqID)_AS GroupFinalMastSeqID,
_      __        ____  _PlayerID
  _  _  __     FROM_  _ sequenced
  _         _  WHERE    PartitionID = 1
        _ _   _GROUP BY_PlayerID,
_     _ _       __      MasterSeqID - PartIDSeqID
   ___  __     HAVING___COUNT(*)_>=_@n
     _      _)
_   SELECT
DISTINCT _ _(_sequenced.PlayerID ),
  _   _     MIN(sequenced.EventTime) AS StartTime,
   __ ____  MAX(sequenced.EventTime) AS_EndTime,
            DATEDIFF(minute, MIN(sequenced.EventTime),
 __    _     __   _  MAX(sequenced.EventTime)) AS StaticTime,