June 13, 2012 at 5:39 am
Dear all,
I have a table called Weeks which is having 2 columns:
1) WeekId [Primary Key auto indexed] data type: Int
2) WeekStartDate [data type: Date]
The Column WeekStartDate contains 52 dates which are falling on saturday for this whole year(2012).
I need a list of dates containing current Week start date along with next 13 week start dates from the Weeks table.
Please kindly help with a sql query statement for the above mentioned problem.
Thanks in advance.
Ram
June 13, 2012 at 5:55 am
knocking up some sample data and results query, I believe this will do the trick
declare @weeks table (weekid int identity, weekstartdate date)
insert into @weeks values ('2012-06-02')
declare @x int = 1, @a int = 7
while @x <= 20
begin
insert into @weeks select convert(date,dateadd(day,@a,'2012-06-02'))
set @x = @x + 1
end
select * from @weeks
;with cte as
(
select
ROW_NUMBER() OVER (ORDER BY WeekStartDate) AS RowNum,
*
from
@weeks
where
weekstartdate >= DATEADD(DAY,-7,GETDATE())
)
select * from cte where rownum <=14
June 13, 2012 at 6:22 am
In following, -2 will give you previous Saturday, you can manipulate this number to get other days
select b,dateadd(day,Datediff(Week,0,GETDATE()) * 7,-2+((b-1)*7))
from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)) a(b)
June 13, 2012 at 2:30 pm
You could do something like this:
DECLARE @NbrWeeks INT
SET @NbrWeeks = 13
SELECT weekstartdate
FROM @weeks
WHERE weekstartdate >= DATEADD(DAY,-2,DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0))
and weekstartdate <= DATEADD(WEEK,@NbrWeeks,DATEADD(DAY,-2,DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0)))
June 13, 2012 at 10:25 pm
Dear All,
That's simply great! works charmly...
Many Many thanks for your valuable help!
Regards,
Ram
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply