April 15, 2015 at 9:03 am
Hi All,
I am writing report to display some IIS site activity, part of the requirement is to produce a trend for user activity for each week of the year.
I have written a stored procedure that uses datepart to split year, month and year week number into separate columns. However the problem I am having is that when a week is split over 2 months I end up with the two entries for the same week but across two months which also splits the count of activity into two rows. So when I produce a line chart in SSRS I end up with a dip due to the week total being split.
An example would be week number 14 of this year is split over two months, I think I need to add same week number activity counts together but not sure how to handle this in the stored procedure.
Ant help would be great.
Adam
April 16, 2015 at 7:39 am
For complex date calculations like this I would suggest you create a Calendar table. It will save you a lot of headaches in the long run.
April 16, 2015 at 8:22 am
Hi Get me?,
Many thanks for your response, I agree the calendar approach would be the best. However, due to time constraints I have managed to get round the issue (although not perfect) by pulling the week number and data first into a temp table then adding the months the weeks belong to after in a separate query. Not great but it will have to do for now.
Thanks again.
Adam,
April 16, 2015 at 8:41 am
acrutchley (4/16/2015)
Hi Get me?,Many thanks for your response, I agree the calendar approach would be the best. However, due to time constraints I have managed to get round the issue (although not perfect) by pulling the week number and data first into a temp table then adding the months the weeks belong to after in a separate query. Not great but it will have to do for now.
Thanks again.
Adam,
If the report is to be by week, you could use the ISOWEEK date part to keep thinks really simple.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 16, 2015 at 9:11 am
Hi Jeff,
I did try the ISO_WEEK option but I was still getting overlaps with the months.... which caused duplicate week number entries because I was grouping by the month in the same query. Although there is probably a better option than running with two queries, I do get unique week numbers and months. Although week 53 was also a bit of an issue as datepart month returned this as Jan instead of December as I wished... simple update statement after the queries had run resolved this.... bit of a hack though!
Thanks for your help,
Adam
April 16, 2015 at 3:45 pm
acrutchley (4/16/2015)
Hi Jeff,I did try the ISO_WEEK option but I was still getting overlaps with the months.... which caused duplicate week number entries because I was grouping by the month in the same query. Although there is probably a better option than running with two queries, I do get unique week numbers and months. Although week 53 was also a bit of an issue as datepart month returned this as Jan instead of December as I wished... simple update statement after the queries had run resolved this.... bit of a hack though!
Thanks for your help,
Adam
So you're saying that you want the weeks of the year unless they straddle a month in which case you want the week to be split by the month? That's going to make for some funny looking numbers on the fringe weeks.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 16, 2015 at 4:02 pm
Hi Jeff,
No not at all, what I was after was a count of activity by week number over the year(s), and to display visually in a report which month and year the week belonged to. This was to help report viewers to identify easily which week to month and year they are viewing. Ideally weeks that span two months (eg week 14 2015 spans March and april) would show a single count for week 14 but would display March/April next to the week number plus the activity count.
Kind regards,
Adam
April 17, 2015 at 9:16 am
As was stated earlier, a calendar table would be your biggest friend here.
If you absolutely must do it just using some date functions, the following sort of code would do it. It's extremely ugly, and I'm sure there are much more elegant solutions. It was just the first solution my brain suggested, and at least provides some hint of the sort of logic you could use.
Still, I'd much rather use a calendar table than something like this 🙂
DECLARE @SomeDate DATE
SET @SomeDate='04/3/2014'
SELECT
StraddleMonth=CASE WHEN datename(month,dateadd(day,(1-datepart(WEEKDAY,@SomeDate)),@SomeDate))=datename(month,dateadd(day,(7-datepart(WEEKDAY,@SomeDate)),@SomeDate))
THEN datename(month,dateadd(day,(1-datepart(WEEKDAY,@SomeDate)),@SomeDate))
ELSE datename(month,dateadd(day,(1-datepart(WEEKDAY,@SomeDate)),@SomeDate))+'/'+datename(month,dateadd(day,(7-datepart(WEEKDAY,@SomeDate)),@SomeDate))
END,
WeekNumber=DATEPART(Week,@SomeDate)
April 19, 2015 at 9:59 am
acrutchley (4/16/2015)
Hi Jeff,I did try the ISO_WEEK option but I was still getting overlaps with the months.... which caused duplicate week number entries because I was grouping by the month in the same query. Although there is probably a better option than running with two queries, I do get unique week numbers and months. Although week 53 was also a bit of an issue as datepart month returned this as Jan instead of December as I wished... simple update statement after the queries had run resolved this.... bit of a hack though!
Thanks for your help,
Adam
Sorry for the delay.
Ugh!. Ok. I get it. Heh... I hate these types of display requirements. 😉 And Jacob is correct. A calendar table would make this relatively easy to do. I just need to ask a couple of questions.
1. What is the first day of a week according to your company? Sunday? Monday? or ???
2. How do you want the months to be displayed for the month-overlap weeks? I know that you posted that above but just want to make sure. For example, can this be limited to just 3 letter months such as Mar/Apr?
3. Same question as number 2 above but for the last week of the year when you have a year overlap. How do you want THAT displayed? If you want Dec 2014/Jan 2015, do you also want such a format in #2 above so that the humans that want this done in the first place don't have to read left or right to figure out what year July is in? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2015 at 3:32 am
Hi Jeff,
Thanks for getting back to me, and yes this requirement sounds simple to the humans doesn't it 🙂
Please see responses to your questions:
1. What is the first day of a week according to your company? Sunday? Monday? or ???
The first day of the week should be a Monday.
2. How do you want the months to be displayed for the month-overlap weeks? I know that you posted that above but just want to make sure. For example, can this be limited to just 3 letter months such as Mar/Apr?
Yes, three letter abbreviations are fine.
3. Same question as number 2 above but for the last week of the year when you have a year overlap. How do you want THAT displayed? If you want Dec 2014/Jan 2015, do you also want such a format in #2 above so that the humans that want this done in the first place don't have to read left or right to figure out what year July is in?
I think what I am looking at here from a presentation viewpoint is:
4 x columns
- 1st column = Year (will display 2 years if there is a crossover (2014/2015))
- 2nd column = Month (will display 2 months if there is a crossover (Feb/Mar))
- Third column = Week (No duplicates)
- Forth column = Count
The 1st column and second columns would be grouped and repeating values removed, from a presentation point of view but this is easily achieved in SSRS.
Thanks again,
Adam
April 20, 2015 at 8:32 am
We used a 4 4 5 fiscal calendar, stuffing the remaining days into the first and last week of the year.
This matched the fiscal calendar accounting used across all the divisions.
It also made for more consistent month to month comparisons, for the most part.
Not sure if it applies to your situation or not.
But being the same as whatever accounting does, and using it across the board, makes it easier to compare numbers everywhere.
Someone may want to compare IIS activity to some other measure.
In our case, everyone liked to mix and mTHIS IS CLEARLY SPAMasures, and having a consistent time frame was very important.
A calendar table is more or less a one time cost, that can be reused everywhere.
You might want to factor that into the speed / lack of time equation.
Sometimes slowing down a bit, designing for the longer term, can have benefits.
When a week is split across 2 months, and you display both, they may not be split at all or evenly year to year.
I would tend to think this would lead to a lot of questions, especially if people are mixing data.
Just something to think about.
You may eventually end up with a calendar, just at a later time.
April 20, 2015 at 9:07 am
Hi Greg,
Thank you for your comments and guidance, and yes I agree a calendar table probably will be mixed up into the solution before long 🙂
Kind regards,
Adam
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply