x weeks ago

[akalinowski]

is there a way i can have reporting services dynamically select X weeks (sun - sat) ago

i want create a report that shows me sales 1 week, 2 week, 3-6 weeks ago
i can do it by days, but it starts on x days ago based on what day i run it, i always want it to be sun - sat, or if possible mon - sun.

thxnx

akalinowski

http://www.olivelabs.com

 

[xlbo]

Yes - but that is nothing to do with SSRS - that is simply the logic in the SQL used to get the data

you 1st need to get the date X weeks ago - then work out which day of the week it is and from there calculate how many days to add / subtract to get the appropriate sunday / saturday dates. Something like:

Declare @X as integer

SET @X = -1

SELECT DATEADD(Week,@X,getdate()),

DATEADD(DD,	CASE DATEPART(Weekday,DATEADD(Week,@X,getdate())) 
			WHEN 7 Then 0 
			else DATEPART(Weekday,DATEADD(Week,@X,getdate()))-1 END *-1
			,DATEADD(Week,@X,getdate())),
			
DATEADD(DD,	CASE DATEPART(Weekday,DATEADD(Week,@X,getdate())) 
			WHEN 7 Then 0 
			else 7 - DATEPART(Weekday,DATEADD(Week,@X,getdate())) END 
			,DATEADD(Week,@X,getdate()))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question