Kingston Dhasian (5/29/2013)
phoenix_ (5/29/2013)
Is it possible somehow to convert it to 'one' select statement so I will have only one result?
Yes. It is possible to convert it to single SELECT statement. But, I would advise against it.
We have provided better performing set-based solutions and you have used a WHILE loop which works on a row by row basis.
Maybe I am wrong but will Group By makes my join with other tables tough work...?
I want now join the results with another table like this:
eventNum startDate endDate
1 2013-01-05 2013-01-10
2 2013-01-17 2013-01-22
3 2013-01-08 2013-01-19
so the result should be the following
week_nbrstart_dateend_date eventNum
1 2013-01-03 2013-01-05 1
2 2013-01-06 2013-01-12 1
2 2013-01-06 2013-01-12 3
3 2013-01-13 2013-01-19 2
3 2013-01-13 2013-01-19 3
4 2013-01-20 2013-01-23 2
Kingston Dhasian (5/29/2013)
One more observation after executing your code: Your expected results are different from the results of this query.
That's because I changed the date range (more rows) and I added also the case statement (which is showing the first day of the week - and can affect the first row). Same with the last row.
Now the results will be the same:
declare @StartDate date
set @StartDate = '2013-01-03'
declare @EndDate date
set @EndDate = '2013-01-23'
declare @DateCalc date
declare @WeekStartDate date
set @WeekStartDate = DATEADD(ww, DATEDIFF(ww,0,@StartDate), 0)
set @DateCalc = @StartDate
WHILE (@WeekStartDate <= @EndDate )
begin
select case when @StartDate > @WeekStartDate then DatePart(ww,@StartDate) else DatePart(ww,@WeekStartDate) end as WeekNum, @StartDate as StartDate, case when DATEADD(dd, 6, @WeekStartDate) > @EndDate then @EndDate else DATEADD(dd, 6, @WeekStartDate) end as EndDate;
set @WeekStartDate = DATEADD(dd, 7, @WeekStartDate)
set @StartDate = @WeekStartDate
END