Constructing Year Ranges from Annual Data

[alexjones]

I have a table tracking, say, an organization's participation in a particular program. The relevant columns in this table might look something like:

OrganizationID Year Status
1 1999 Inactive
1 2000 Inactive
1 2001 Active
1 2002 Active
1 2003 Active
1 2004 Inactive
1 2005 Inactive

What I would like to produce is:

OrganizationID Status FromYear ToYear
1 Inactive 1999 2000
1 Active 2001 2003
1 Inactive 2004 2005

Any ideas for accomplishing this would be greatly appreciated.

 

[fredericofonseca]

which sql version? if 2012+ using windowing functions is easy - otherwise a self join will be required, alongside with a row number to group them. a few options available

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[alexjones]

Database is SQL Server 2012. I am unfamiliar with windowing functions. Might you give me an example?

 

[alexjones]

I've dug into window functions, which, I agree, are way cool. However, I can't seem to find a specification that doesn't "jump over" the active status when setting the date ranges for the inactive status. I've tried lag and lead, first_value and last_value. Instead of getting:

OrganizationID Status FromYear ToYear
1 Inactive 1999 2000
1 Active 2001 2003
1 Inactive 2004 2005


I get:

OrganizationID Status FromYear ToYear
1 Inactive 1999 2005
1 Active 2001 2003

What am I missing?

 

[fredericofonseca]

this will do it (not 2012 specific as at the moment I don't have time to play with it)
note that depending on the volumes it might be better for performance to move the bit in green into a temporary table and then add an index on organization, rownum and status

;with orgs as
([highlight #8AE234]select organizationid
       ,year
       ,status
       ,row_number() over (partition by organizationid
                               order by year
                          ) as rownum
       from org[/highlight]
)
select organizationid
      ,max(status) as status
      ,min(year) as fromyear
      ,max(year) as toyear
from (select organizationid
            ,year
            ,status
            ,coalesce(org3.rownum,0) + 1 as rankx
      from orgs org1
      outer apply (select top 1 org2.rownum
                   from orgs org2
                   where org2.organizationid = org1.organizationid
                   and org2.rownum < org1.rownum
                   and org2.status <> org1.status
                   order by org2.rownum desc
                  ) org3
     ) org4
group by organizationid, rankx
order by organizationid, fromyear

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886