Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: fetch count for consecutive years

    hi all,

    I have records like bellow

    1 1999
    2 2000
    3. 2010
    4. 2011

    I have to get the count for Consecutive years. Can you help me I need it urgent.
    Last edited by Shan09; 06-17-11 at 13:54. Reason: specific heading

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    It always helps when you can provide us with some sample data and the result you're expecting. It's quite often a lot easier for us to understand the question.

    Let me give it a try, see if I have interpreted your question correct.
    Code:
    CREATE TABLE #DaTable(
    	id	INT	NOT NULL,
    	year_	INT	NOT NULL
    )
    
    INSERT INTO #DaTable(id, year_) VALUES
    (1, 1999), (2, 2000), (3, 2010), (4, 2011)
    
    -- I am guessing only the year matters, not the values of the Id's
    
    SELECT COUNT(*) as NrOfConsecYears
    FROM #DaTable as A
    	INNER JOIN #DaTable as B ON
    		A.year_ = B.year_ - 1
    
    -- if the values of the Id's also matter
    
    SELECT COUNT(*) as NrOfConsecYears
    FROM #DaTable as A
    	INNER JOIN #DaTable as B ON
    		A.id = B.id - 1 AND
    		A.year_ = B.year_ - 1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2011
    Posts
    2

    thank u

    Thanks a lot for the reply... It works.

Posting Permissions

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