[Solved] Want to the check the records existing in the date range in sql server [closed]

by


Try this

with myTable (
    numberid,
    startDate,
    endDate
) as(
    select
        numberid,
        CONVERT(DATETIME,startDate),
        CONVERT(DATETIME,endDate)
    from (
    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) and ISNULL(year(endDate),year(startDate)) then 'y' else 'n' end [0809],
    case when 2010 between year(startDate) and ISNULL(year(endDate),year(startDate)) then 'y' else 'n' end [0910],
    case when 2011 between year(startDate) and ISNULL(year(endDate),year(startDate)) then 'y' else 'n' end [1011],
    case when 2012 between year(startDate) and ISNULL(year(endDate),year(startDate)) then 'y' else 'n' end [1112],
    case when 2013 between year(startDate) and ISNULL(year(endDate),year(startDate)) then 'y' else 'n' end [1213]
from myTable

Edit:

you can do, instead of:

2009 between year(startDate) and ISNULL(year(endDate),year(startDate))

So you can setup the interval, this:

case when startDate <= '2012-01-01' and ISNULL(endDate,startDate) >= '2011-01-01' then 'y' else 'n' end [1011],

7

solved Want to the check the records existing in the date range in sql server [closed]