USE MFR_Merchandise
GO 

DECLARE @FilterDate  AS  TABLE(filterdate date NOT NULL) 
DECLARE @FromDate as date
DECLARE @ToDate as date

set @FromDate='11-02-2011' -- can be dynamic
set @ToDate='02-24-2012'   -- can be dynamic

INSERT INTO @FilterDate
-- <filter dates base on @FromDate and to @ToDate value must be inserted>

-- example: if @FromDate='11-02-2011' and @ToDate='02-24-2012'
-- the value to be inserted will be the following dates

-- 11-30-2011
-- 12-31-2011
-- 01-31-2012
-- 02-24-2012

-- Notice that the date between @FromDate and @ToDate dates are move -- to the end of its month
-- EXCEPT THE LAST MONTH which will be the original value in @ToDate.

quote:

---

SELECT CASE WHEN DATEADD(mm,DATEDIFF(mm,0,MonthDate)+1,0)-1 > @ToDate THEN @ToDate ELSE DATEADD(mm,DATEDIFF(mm,0,MonthDate)+1,0)-1 END
FROM CTE
WHERE DATEDIFF(mm,0,MonthDate) < = @Todate

---

quote:

---

Originally posted by visakh16

;With CTE (MonthDate)
AS
(
SELECT DATEADD(mm,DATEDIFF(mm,0,@fromdate)+1,0) -1

UNION ALL

SELECT CASE WHEN DATEADD(mm,DATEDIFF(mm,0,MonthDate)+1,0)-1 > @ToDate THEN @ToDate ELSE DATEADD(mm,DATEDIFF(mm,0,MonthDate)+1,0)-1 END
FROM CTE
WHERE DATEDIFF(mm,0,MonthDate) < = @Todate
)

SELECT MonthDate
FROM CTE

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by khtan

; with rcte as
(
	select	FilterDate = dateadd(month, datediff(month, 0, @FromDate) + 1, -1)

	union all

	select	FilterDate = @ToDate

	union all

	select	FilterDate = dateadd(month, datediff(month, 0, FilterDate) + 2, -1)
	from	rcte
	where	dateadd(month, 1, FilterDate)	<= @ToDate
)
select	*
from	rcte
option (maxrecursion 0)

---

KH
[spoiler]Time is always against us[/spoiler]

---