TSQL : To get the dates in weekwise from last saturday of previous month for 1 month
If someboday wants the query like
I need a query to get weekwise data for last month starting Last Saturday of the Month and previous 4 weeks from then if I run the report this month.
Example I ran the Report in Feb-2014
then I need to get the past weeks of data like below for example when I execute report I Feb, Data for Last month Jan should be displayed like this
week 1 (19/01 to 25/01)
week 2 (12/01 to 18/01)
week 3 (05/01 to 11/01)
week 4 (29/01 to 04/01)
Here is the Query
Declare @SelectedDate datetime
set @SelectedDate = getdate()I need a query to get weekwise data for last month starting Last Saturday of the Month and previous 4 weeks from then if I run the report this month.
Example I ran the Report in Feb-2014
then I need to get the past weeks of data like below for example when I execute report I Feb, Data for Last month Jan should be displayed like this
week 1 (19/01 to 25/01)
week 2 (12/01 to 18/01)
week 3 (05/01 to 11/01)
week 4 (29/01 to 04/01)
Here is the Query
Declare @SelectedDate datetime
select dateadd(d,-6,dates) as weekstartdate ,dates as weekEnddate from (select dateadd(day,number-1,DATEADD(year,Year(@SelectedDate)-1900,0))as dates from master..spt_valueswhere type='p' and number between 1 andDATEDIFF(day,DATEADD(year,Year(@SelectedDate)-1900,0),DATEADD(year,Year(@SelectedDate)-1900+1,0)))
Dtwhere DATENAME(weekday,dates)='Saturday' and month (dates) = month(dateadd(m,-1,@SelectedDate)) and year(dates) = year(dateadd(m,-1,@SelectedDate))Order by dates desc
Comments
Post a Comment