0

I'm using SQL Server 2008. My table structure looks like

SNo  From_date   To_date     EmpId
----------------------------------
1    6/6/2012   2/6/2013     1
2    3/6/2013   NULL         1
3    6/6/2012   5/12/2012    2

When I provide particular monthno and year it has to automatically return me the appropriate row where the month and year fall in the given date range. I tried with the below query but it is not working for next year checking. Can any 1 help me too fix this problem?

  declare @monthno int;
  declare @yearno int;
  set @monthno=7;
  set @yearno=2012;
  select * from YearMonthTable
  where (@monthno>= MONTH(From_Date) and @yearno >= YEAR(From_Date)) 
  and ((@monthno<= MONTH(To_date) and @yearno <=YEAR(To_date)) or To_date is null)
  and EmpId=1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
saranya
  • 179
  • 1
  • 5
  • 16

2 Answers2

0

For this execute separate queries it's working..

My try is not use more than one conditional statements in where clause..

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
selvackp
  • 1
  • 2
  • 9
0
  declare @monthno int;
  declare @yearno int;
  set @monthno=7;
  set @yearno=2012;
  select * from YearMonthTable
  where (@monthno>= MONTH(From_Date) and @yearno >= YEAR(From_Date)) 
  and ( (@yearno < YEAR(To_date)) or (@monthno <= MONTH(To_date) and  earno =YEAR(To_date))  or To_date is null)  and EmpId=1
Mohan Chandra Polaki
  • 1
  • if the year in the todate is greater than the @yearno param , then there is no need to check the month. Simple logic.. i hope this helps you guys .. thanks – Mohan Chandra Polaki Mar 24 '16 at 14:07