July 25, 2013 at 5:30 am
Hi All,
I've created a column in by sql query where by if i run the report from 27/05/2013 to 30/06/2013, any data occuring during WC 27/05/2013 the column will populate as Week 1, anything during WC 03/06/2013 will populate as Week 2 etc...
This is so when I put this into SSRS I can create a Pie Chart for Weeks 1,2 etc for the duration mentioned above, plus when I change the dates, Week 1, Week 2 etc will change accordingly.
DECLARE @DisplayTo as DATETIME
declare @prevmonth as datetime
declare @prevweek5 as varchar (50)
declare @prevweek4 as varchar (50)
declare @prevweek3 as varchar (50)
declare @prevweek2 as varchar (50)
declare @prevweek1 as varchar (50)
DECLARE @ToDate as DATETIME
SET @DisplayTo = '2013-06-30 23:59:59'
SET @ToDate = dbo.fUniversalTime(@DisplayTo)
set @prevmonth = dateadd(week,-4,DATEADD(dd, @@DATEFIRST - DATEPART(dw, @DisplayFrom), @DisplayFrom))
set @prevweek5 = CONVERT(varchar(50),dateadd(week,-4,DATEADD(dd, @@DATEFIRST - DATEPART(dw, @ToDate), @ToDate)),103)
set @prevweek4 = CONVERT(varchar(50),dateadd(week,-3,DATEADD(dd, @@DATEFIRST - DATEPART(dw, @ToDate), @ToDate)),103)
set @prevweek3 = CONVERT(varchar(50),dateadd(week,-2,DATEADD(dd, @@DATEFIRST - DATEPART(dw, @ToDate), @ToDate)),103)
set @prevweek2 = CONVERT(varchar(50),dateadd(week,-1,DATEADD(dd, @@DATEFIRST - DATEPART(dw, @ToDate), @ToDate)),103)
set @prevweek1 = CONVERT(varchar(50),dateadd(week,0,DATEADD(dd, @@DATEFIRST - DATEPART(dw, @ToDate), @ToDate)),103)
Here I set my @prevweek parameters to the WC Date of the previous 5 weeks, then...
case when CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, cons.StartDate), cons.StartDate)), 103) = @prevweek5 then '1'
when CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, cons.StartDate), cons.StartDate)), 103) = @prevweek4 then '2'
when CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, cons.StartDate), cons.StartDate)), 103) = @prevweek3 then '3'
when CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, cons.StartDate), cons.StartDate)), 103) = @prevweek2 then '4'
when CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, cons.StartDate), cons.StartDate)), 103) = @prevweek1 then '5' End as 'RptWeek',
I match up the WC parameters to the WC Dates I get from my data and label them weeks 1 - 5 which i can then use as a filter in my Pie Charts in SSRS so i can have pir chart just for Week 1's data. This works fine in SQL Studio but when I put this into SSRS the column just appears blank.
Any help on why this is or know a better way of labeling the preceeding weeks as 1,2,3 etc instead of the acutal week number would be much appreciated.
July 25, 2013 at 6:13 am
Managed to sort it. I missed out Set Datefirst 1 at the top my query after all that!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply