Want to the check the records existing in the date range in sql server -

i have following requirement,

sample data:

numberid    startdate,          enddate ---------------------------------------------------     1900415115  2012-09-22 00:39:00 2013-10-25 00:00:00 2429398311  2008-05-22 16:57:00 2013-07-30 00:00:00 4337719455  2008-05-22 16:57:00 2009-06-12 00:00:00 6644946399  2008-05-22 16:57:00 2009-01-16 00:00:00 9740698857  2008-05-22 16:57:00 2008-09-26 00:00:00 3928192597  2011-08-24 12:14:00 2012-09-24 00:00:00 1233655116  2013-08-14 00:39:00 2013-12-09 00:00:00 1780419233  2008-10-22 00:08:00 2014-08-24 00:00:00 1912939738  2011-08-26 01:32:00 2014-06-20 00:00:00 3810216146  2008-05-22 16:57:00 2009-01-16 00:00:00 5851814815  2009-10-07 01:00:00 2010-01-25 00:00:00 3692916726  2008-05-22 16:57:00 2008-10-02 00:00:00 3069490750  2008-05-22 16:57:00 2009-08-14 00:00:00 

i want check if 'numberid' exists in date range , want group

01/01/2008 - 01/01/2009 0809 01/01/2009 - 01/01/2010 0910 01/01/2010 - 01/01/2011 1011 01/01/2011 - 01/01/2012 1112 

appreciate quick help!!

thanks,

---

can try following data im not sure works enddate null values

numberid    startdate,          enddate ---------------------------------------------------  4405598510  2011-08-06 00:00:00 null 2418680054  2011-08-06 00:00:00 2011-12-28 00:00:00 4405598510  2011-08-06 00:00:00 null 1810168034  2011-08-06 00:00:00 null 6849266569  2011-08-06 00:00:00 2014-09-02 00:00:00 2682265222  2011-08-09 00:58:00 2012-09-20 00:00:00 6253123963  2011-08-09 00:00:00 2011-07-01 00:00:00 8276745680  2011-08-10 00:00:00 2014-06-27 00:00:00 3873103800  2011-08-10 00:00:00 2013-07-16 00:00:00 3703761027  2011-08-06 00:00:00 null 1810168034  2011-08-06 00:00:00 null 9888909217  2011-08-08 00:00:00 2013-06-30 00:00:00 3034945061  2011-08-09 00:59:00 null 4822850747  2011-08-10 00:00:00 2012-08-21 00:00:00 5849710101  2011-08-10 00:00:00 null 

and not yearly 2008, 2009.. 2012.. need specific dates please.

thanks

try this

with mytable (     numberid,     startdate,     enddate ) as(     select         numberid,         convert(datetime,startdate),         convert(datetime,enddate)     (     values         (4405598510,'2011-08-06 00:00:00',null),         (2418680054,'2011-08-06 00:00:00','2011-12-28 00:00:00'),         (4405598510,'2011-08-06 00:00:00',null),         (1810168034,'2011-08-06 00:00:00',null),         (6849266569,'2011-08-06 00:00:00','2014-09-02 00:00:00'),         (2682265222,'2011-08-09 00:58:00','2012-09-20 00:00:00'),         (6253123963,'2011-08-09 00:00:00','2011-07-01 00:00:00'),         (8276745680,'2011-08-10 00:00:00','2014-06-27 00:00:00'),         (3873103800,'2011-08-10 00:00:00','2013-07-16 00:00:00'),         (3703761027,'2011-08-06 00:00:00',null),         (1810168034,'2011-08-06 00:00:00',null)     ) [ ] (numberid,startdate,enddate) ) select     numberid,     startdate,     enddate,     case when 2009 between year(startdate) , isnull(year(enddate),year(startdate)) 'y' else 'n' end [0809],     case when 2010 between year(startdate) , isnull(year(enddate),year(startdate)) 'y' else 'n' end [0910],     case when 2011 between year(startdate) , isnull(year(enddate),year(startdate)) 'y' else 'n' end [1011],     case when 2012 between year(startdate) , isnull(year(enddate),year(startdate)) 'y' else 'n' end [1112],     case when 2013 between year(startdate) , isnull(year(enddate),year(startdate)) 'y' else 'n' end [1213] mytable 

edit:

you can do, instead of:

2009 between year(startdate) , isnull(year(enddate),year(startdate))  

so can setup interval, this:

case when startdate <= '2012-01-01' , isnull(enddate,startdate) >= '2011-01-01' 'y' else 'n' end [1011],