using single select statement i need the followng output...suppose if i am running a query today this i need to get employees list who are celebrating birth day and suppose if it is on friday then we need to get friday and saturday and sunday employees list also.
I think this gives what you're after... First I create a dummy table with some demo data in it. (in this instance 7 employees with birthdays in the next 7 days). Next I create a start and end date depending on if it's a Friday or not. Then I create a table with the next birthday in it. Finally SELECT from that table if the next birthday is within the start/end date range. IF OBJECT_ID('tempdb..#DemoData') IS NOT NULL DROP TABLE #DemoData IF OBJECT_ID('tempdb..#DemoDataWithNextBirthday') IS NOT NULL DROP TABLE #DemoDataWithNextBirthday CREATE TABLE #DemoData ( EmployeeId INT , Birthday DATETIME ) --put in some dummy date with birthdays this week INSERT INTO #DemoData VALUES(1, '19770415') INSERT INTO #DemoData VALUES(2, '19770416') INSERT INTO #DemoData VALUES(3, '19770417') INSERT INTO #DemoData VALUES(4, '19770418') INSERT INTO #DemoData VALUES(5, '19770419') INSERT INTO #DemoData VALUES(6, '19770420') INSERT INTO #DemoData VALUES(7, '19770421') -- DECLARE @today DATETIME DECLARE @startdate DATETIME DECLARE @enddate DATETIME --set @today to todays date SELECT @today = GETDATE() --comment in to test a friday --SELECT @today = '20130419'--GETDATE() --get just the date part SELECT @startdate = DATEADD(dd, 0, DATEDIFF(dd, 0, @today)) --if a friday set the end date to sunday else end date is the same as start date IF ( SELECT DATEPART(weekday, @today) ) = 6 --friday = 6 BEGIN SELECT @enddate = DATEADD(DAY, 2, @startdate) END ELSE BEGIN SELECT @enddate = @startdate END --Create a table containing the next birthday (make sure that if we're past the birthday we put the next birthday in next year --this is only important if the start and end date are in different years - which won't happen often but can happen) SELECT dd.EmployeeId , dd.Birthday , CASE WHEN DATEADD(year, ( YEAR(@startdate) - YEAR(birthday) ), birthday) >= @startdate THEN DATEADD(year, ( YEAR(@startdate) - YEAR(birthday) ),birthday) ELSE DATEADD(year, ( YEAR(@startdate) - YEAR(birthday) + 1 ), birthday) END AS NextBirthday INTO #DemoDataWithNextBirthday FROM #DemoData AS dd SELECT EmployeeId FROM #DemoDataWithNextBirthday AS dd WHERE NextBirthday BETWEEN @startdate AND @enddate