Post reply

Missing years as comma seperated list

  • March 2, 2011 at 11:10 pm

    #231966

    I have like following table with annual reports of employees.

    CREATE TABLE demo

    (EmpNO tinyint, RYear smallint )

    GO

    INSERT INTO demo

    SELECT 1,2002 UNION ALL

    SELECT 1,2001 UNION ALL

    SELECT 1,2005 UNION ALL

    SELECT 2,2002 UNION ALL

    SELECT 2,2006

    GO

    My task is to get comma separated list of missing years out of years 2001, 2002 and 2003.

    The result according to above data would be as

    EmpNo MissingYears

    1 2003

    2 2001,2003

    Please suggest optimal method for this task.

    Thanks & Regards

    Atif

    DBDigger Microsoft Data Platform Consultancy.

  • March 3, 2011 at 7:17 am

    #1293996

    I think that this article will guide you: Creating a comma-separated list (SQL Spackle)[/url]

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • March 3, 2011 at 5:54 pm

    #1294412

    Also see Date Gap Problem and Finding gaps in a sequential number sequence[/url] for more help solving your problem.


    Dan Guzman - Not the MVP (7/22/2010)
    All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply