Results 1 to 3 of 3

Thread: Work out the latest sequence of results

  1. #1

    Thread Starter
    Frenzied Member FishGuy's Avatar
    Join Date
    Mar 2005
    Location
    Bradford UK
    Posts
    1,708

    Work out the latest sequence of results

    I have a query as below

    Which simply works out a result for each team and puts the results in date order.

    I want to count the last result from each team, and if the result before that was the same result then add one to the count and so on, unless the sequence is broken then exit the count.

    so if a team drew-drew their last 2 games but drew-lost their 3rd previous they return a result of draw-draw 2. all

    Im trying to figure out how to do this, but the only way I can figure is using a cursor for each team and each result, which doesnt seem like it will be efficient?

    Code:
    SELECT * FROM(
    Select ht.[Name]
    ,CASE WHEN HT_Home_Goals = HT_AWAY_Goals AND FT_Home_Goals = FT_AWAY_Goals THEN  'DRAW - DRAW'
    WHEN  HT_Home_Goals < HT_AWAY_Goals AND FT_Home_Goals = FT_AWAY_Goals THEN  'LOSE - DRAW'
    WHEN  HT_Home_Goals > HT_AWAY_Goals AND FT_Home_Goals = FT_AWAY_Goals THEN  'WIN - DRAW'
    WHEN  HT_Home_Goals = HT_AWAY_Goals AND FT_Home_Goals < FT_AWAY_Goals THEN  'DRAW - LOSE'
    WHEN  HT_Home_Goals = HT_AWAY_Goals AND FT_Home_Goals > FT_AWAY_Goals THEN  'DRAW - WIN'
    WHEN  HT_Home_Goals > HT_AWAY_Goals AND FT_Home_Goals > FT_AWAY_Goals THEN  'WIN - WIN'
    WHEN  HT_Home_Goals > HT_AWAY_Goals AND FT_Home_Goals < FT_AWAY_Goals THEN  'WIN - LOSE'
    WHEN  HT_Home_Goals < HT_AWAY_Goals AND FT_Home_Goals > FT_AWAY_Goals THEN  'LOSE - WIN'
    WHEN  HT_Home_Goals < HT_AWAY_Goals AND FT_Home_Goals < FT_AWAY_Goals THEN  'LOSE - LOSE'
    END AS Result
    ,Date
    ,'H' as 'HomeAway'
    from matches
    INNER JOIN Teams t on Away_ID = t.id
    INNER JOIN Teams ht on Home_ID = ht.id
    --where ht.[Name] = @TeamID
    UNION
    Select ht.[Name] 
    ,CASE WHEN HT_Home_Goals = HT_AWAY_Goals AND FT_Home_Goals = FT_AWAY_Goals THEN  'DRAW - DRAW'
    WHEN  HT_Home_Goals > HT_AWAY_Goals AND FT_Home_Goals = FT_AWAY_Goals THEN  'LOSE - DRAW'
    WHEN  HT_Home_Goals < HT_AWAY_Goals AND FT_Home_Goals = FT_AWAY_Goals THEN  'WIN - DRAW'
    WHEN  HT_Home_Goals = HT_AWAY_Goals AND FT_Home_Goals > FT_AWAY_Goals THEN  'DRAW - LOSE'
    WHEN  HT_Home_Goals = HT_AWAY_Goals AND FT_Home_Goals < FT_AWAY_Goals THEN  'DRAW - WIN'
    WHEN  HT_Home_Goals < HT_AWAY_Goals AND FT_Home_Goals < FT_AWAY_Goals THEN  'WIN - WIN'
    WHEN  HT_Home_Goals < HT_AWAY_Goals AND FT_Home_Goals > FT_AWAY_Goals THEN  'WIN - LOSE'
    WHEN  HT_Home_Goals > HT_AWAY_Goals AND FT_Home_Goals < FT_AWAY_Goals THEN  'LOSE - WIN'
    WHEN  HT_Home_Goals > HT_AWAY_Goals AND FT_Home_Goals > FT_AWAY_Goals THEN  'LOSE - LOSE'
    END AS Result
    ,Date
    ,'A' as 'Home/Away'
    from matches
    INNER JOIN dbo.Teams ht on Away_ID = ht.id
    --where ht.[Name] = @TeamID
    
      ) results
    ORDER BY Date DESC

  2. #2

    Thread Starter
    Frenzied Member FishGuy's Avatar
    Join Date
    Mar 2005
    Location
    Bradford UK
    Posts
    1,708

    Re: Work out the latest sequence of results

    I am looking to be able to produce streak results similar to below
    STREAKS Home Away Overall
    Current winning streak 2 1 3
    Current drawing streak - - -
    Current losing streak - - -

  3. #3

    Thread Starter
    Frenzied Member FishGuy's Avatar
    Join Date
    Mar 2005
    Location
    Bradford UK
    Posts
    1,708

    Re: Work out the latest sequence of results


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width