April 15, 2015 at 1:26 pm
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:07 am
I usually try to avoid using week number, since a similar problem occurs at year end/begin.
Can you instead use the start date of the week? Monday for example?
_____________________________________________
Scott
April 16, 2015 at 8:24 am
Hi Scott
Many thanks for your response, and great suggestion. 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,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply