Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2006
    Posts
    4

    Question Unanswered: Grouping By Date Period

    I'm using SQL Server 2000.

    Example table:
    Code:
    PeopleID  Date        Status
    1         2004-01-01  True
    1         2005-01-01  True
    1         2006-01-01  True
    2         2004-01-01  True
    2         2005-01-01  False
    2         2006-01-01  True
    I'm trying to find a way to query whether or not someone has had a specific status for 3 years in a row. As you can see from the table above, PeopleID 1 has had a "Status" of "True" for 3 years in a row, whereas PeopleID 2 hasn't--there was one year where they had "False".

    I'm wondering I can query this, or if I'm going to have to scan the records manually.

    I suppose I could write a stored procedure and do some looping too.

    Appreciate any help, thanks!
    Last edited by nullGumby; 05-12-06 at 12:36.

  2. #2
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    If each person only has one entry per year something like this may work...

    Code:
    SELECT PeopleID
    FROM myTable
    WHERE Date > DateAdd(yyyy, -3 getdate())
      AND Status = 'True'
    GROUP BY PeopleID
    HAVING Count(PeopleID) = 3
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This should work if N always = 3:
    Code:
    select	YT1.PeopleID
    from	[YourTable] YT1
    	inner join [YourTable] YT2
    		on YT1.PeopleID = YT2.PeopleID
    		and YT1.Status = YT2.Status
    		and year(YT1.Date) = year(YT2.Date) - 1
    	inner join [YourTable] YT3
    		on YT2.PeopleID = YT3.PeopleID
    		and YT2.Status = YT3.Status
    		and year(YT2.Date) = year(YT3.Date) - 1
    where	YT1.Status = 'True'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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