April 2, 2008 at 11:20 am
I'd like to get the dates that range in a specific week for a specifc year.
For example: What dates are in Week 14 of 2008? or what is the range? 3/12 - 3/18
Any sugestions?
April 2, 2008 at 11:35 am
You need to start by providing a good definition of how the week of the year is determined.
Week of the year is something that has different definitions in different organizations.
April 2, 2008 at 11:58 am
Week of the year as defined by SQL datepart(wk,Date).
I'll be running a call count report by week and each day from that week for sales agents. I made a Cross Tab report that looks something like this. But this report only includes the current week of the date it runs.
Agent Week Sun Mon Tues Wed Thur Fri Sat Dates
Bill 12 34 21 35 56 45 60 50 3/30 to 4/5
John 12 34 21 35 56 45 60 50 3/30 to 4/5
I've been able to include the Week date range such as 3/30 to 4/5 at the end but this is only because I know when the report will run so I can use Getdate() as a reference. If however I wanted to run a report similiar to this and it was going to span more than one week I'd like to be able to get those date ranges for each week that shows in the report.
Agent Week Sun Mon Tues Wed Thur Fri Sat Dates
Bill 12 34 21 35 56 45 60 50 3/30 to 4/5
John 12 34 21 35 56 45 60 50 3/30 to 4/5
Bill 13 34 21 35 56 45 60 50 4/6 to 4/12
John 13 34 21 35 56 45 60 50 4/6 to 4/12
April 2, 2008 at 12:00 pm
As Michael said, it depends on your defintions but you can use a tally table like this
declare @sdate datetime
select @sdate = '1/1/2000'
select dateadd(dd,n,@sdate) ,
datepart(wk,(dateadd(dd,n,@sdate))),
datepart(yyyy,(dateadd(dd,n,@sdate))),
datepart(dw,(dateadd(dd,n,@sdate)))
from tally
where datepart(wk,(dateadd(dd,n,@sdate))) = 14
and datepart(yyyy,(dateadd(dd,n,@sdate))) =2008
April 2, 2008 at 12:32 pm
I sort of did that with the report that contains only the one week of data. Because I know the report runs on only Saturda I subtract 7 days from that and get my first day thus creating my date range
select @Day = datepart(dd,getdate()),@Month = datepart(mm,Getdate())
Select @Day as DD,@Month as MM
select @EndOfWeek = convert(Varchar(10),@Month) + '/'+convert(varchar(10),@Day)
select @EndofWeek
select @StartOfWeek = convert(varchar(10),datepart(mm,dateadd(dd,-7,Getdate()))) +'/' + convert(varchar(10),datepart(dd,dateadd(dd,-7,Getdate())))
Then I concatinated the variables for the select statement: @StartOfWeek + ' to ' + @EndOfWeek
This won't work though if I have multiple weeks in the report.
April 2, 2008 at 12:42 pm
create this as a view or subquery and join your dates week to it.
declare @sdate datetime
select @sdate = '1/1/2008'
select min(dateadd(dd,n,@sdate)),
max(dateadd(dd,n,@sdate)),
datepart(wk,(dateadd(dd,n,@sdate)))
from tally
where datepart(yyyy,(dateadd(dd,n,@sdate))) = datepart(yyyy,@sdate)
group by datepart(wk,(dateadd(dd,n,@sdate)))
order by datepart(wk,(dateadd(dd,n,@sdate)))
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply