Hi Lyn,

this is what i hav etried, but getting wrong reult

DECLARE @users TABLE(UserID int, Name varchar(50))

insert into @Users

select 1,'Member1' union all

select 2,'Member2' union all

select 3,'Member3' union all

select 4,'Member4' union all

select 5,'Member5' union all

select 6,'Member6'

DECLARE @Calendar TABLE

(CalendarDate Date Primary key, IsWeekend Bit, YearNo SmallInt, QuarterNo TinyInt, MonthNo TinyInt, DayOfYearNo SmallInt, DayNo TinyInt, WeekNo TinyInt, WeekDayNo TinyInt)

Declare @beginDate Date, @endDate Date

Select @beginDate = '02/01/2015', @endDate = '12/31/2015'

While @beginDate <= @endDate

Begin

Insert Into @Calendar (CalendarDate, IsWeekend, YearNo, QuarterNo, MonthNo, DayOfYearNo, DayNo, WeekNo, WeekDayNo)

Select

@beginDate As CalendarDate

,(Case When DATEPART(Weekday, @beginDate) In (7, 1) Then 1 Else 0 End) As IsWeekend

,DATEPART(Year, @beginDate) As YearNo

,DATEPART(QUARTER, @beginDate) As QuarterNo

,DATEPART(MONTH, @beginDate) As MonthNo

,DATEPART(DayOfYear, @beginDate) As DayOfYearNo

,DATEPART(Day, @beginDate) As DayNo

,DATEPART(Week, @beginDate) As WeekNo

,DATEPART(WEEKDAY, @beginDate) As WeekDayNo

Set @beginDate = DateAdd(Day, 1, @beginDate)

End

;WITH CTE

AS

(

SELECT

CalendarDate

,WeekNo

,Row_Number() OVER(PARTITION BY WeekNo ORDER BY CalendarDate ASC) As FirstDayOfWeek

,Row_Number() OVER(PARTITION BY WeekNo ORDER BY CalendarDate DESC) As LastDayOfWeek

FROM

@Calendar

)

SELECT

U.UserID

,U.Name

,WeekNo

,MAX(CASE WHEN FirstDayOfWeek = 1 THEN CalendarDate ELSE NULL END) As FirstDayOfWeek

,MAX(CASE WHEN LastDayOfWeek = 1 THEN CalendarDate ELSE NULL END) As LastDayOfWeek

FROM

@Users U

CROSS JOIN CTE

GROUP BY

U.UserID

,U.Name

,WeekNo

also i am not sure how the performance would be as i using while loop. please show me your idea with sample query.

thanks