Getting data from a week and also that same week in previous months

[vqcheese]

I have an issue with some data im trying to get
here is my scenario
we'll use the week of august 18-24th for my example
the user will want the data from this week, but also will want data from the similar week in previous months.
right now im getting the current week of the month so this week in my example is 34, but 4 weeks(1 month) isn't that week im looking for in june.

any ideas?

 

[plog]

Yes, rethink what you actually want. Months and weeks aren't compatible entities, weeks have 7 days, months have 31. Well, sometimes they have 30. Then there's that one time a year when one has 28. Except of course every 4 years when it has 29.

What should be the previous week of the example you cited? Better yet, what should be the corresponding week for 3/24/2013 to 3/30/2013? How about 7/28/2013 to 8/3/2013?

 

[vqcheese]

for july the week would be 21-27
june would be 16-22

 

[plog]

I don't understand your response. I asked about 3 examples, I don't know which your 2 answers respond to. Let's numerate them:

1. Prior Month/Week for 8/18/2013 - 8/24/2013?
2. Prior Month/Week for 7/28/2013 - 8/3/2013?
3. Prior Month/Week for 3/24/2013 - 3/30/2013?

 

[vqcheese]

if my week like I stated was aug 18-24
the previous week in the previous month I would want is July 21-27

im not sure where you are getting march from because ive never stated anything about march?

 

[plog]

I'm getting March from the calendar. Right click the time on your computer and you can get a list of all of them. Be careful though, go far enough back and they start to repeat.

You're the one who listed the first example, I provided you with 2 more to illustrate my point about weeks and months not being compatible.

 

[WayneRyan]

vq,

You can create a function that will return the FIRST Sunday that is
earlier than "this date in the last month".

Rough attempt:

Create Function fnGetStartDate(@SourceDate as DateTime)
Returns DateTime
As
Begin

Declare @TheYear As Int
Declare @TheMonth As Int
Declare @TheDay As Int
Declare @TheDate As DateTime

-- current year
Set @TheYear = DatePart("year", @SourceDate)
-- Last month
Set @TheMonth = DatePart("month", @SourceDate) - 1
If @TheMonth = 0
   Begin
     Set @TheMonth = 12
     Set @TheYear = @TheYear - 1
   End  

-- Date
Set @TheDay = DatePart("day", @SourceDate)

set @TheDate = CAST(Cast(@TheMonth as varchar(5)) + '/' + 
                    Cast(@TheDay   as varchar(5)) + '/' + 
                    Cast(@TheYear  as Varchar(5)) As DateTime)
-- Set to sunday

Set @TheDate = DATEAdd(dd, DatePart(dw, @TheDate) * -1, @TheDate)

Return @TheDate

End

Wayne