1

I need to create a report where I need the sum of UserHits by week, starting from the first week to the last week of a specific year.

How to achieve this?

http://sqlfiddle.com/#!6/eb181/1/0

CREATE TABLE UserData
(
DateCreated dateTime2 NOT NULL,
UserHits int NOT NULL
);

insert into UserData
values
('2014-01-01',100),
('2014-01-02',50),
('2014-01-03',20),
('2014-01-20',100),
('2014-01-21',100),
('2014-01-22',70),
('2014-02-02',100),
('2014-02-03',100);
GibboK
  • 71,848
  • 143
  • 435
  • 658
  • 1
    _"How to achieve this in a faster way?"_ Faster than what? You haven't shown your approach. What is the difference between your question and the others on SO which ask for grouping by week? – Tim Schmelter Jun 23 '14 at 09:34
  • Thanks for commenting I have just edit my question. – GibboK Jun 23 '14 at 09:38
  • 2
    @GibboK you should fill out your information. At least where you are from. How week is defined is different from country to country. Personally I prefer ISO_WEEK. That is not what you get with datapart though. If you use are using ISOWEEK, a proper answer for your question will be quite extensive. – t-clausen.dk Jun 23 '14 at 10:17

1 Answers1

5
select  datepart(wk, DateCreated)
,       sum(UserHits)
from    UserData
group by
        datepart(wk, DateCreated)
Andomar
  • 232,371
  • 49
  • 380
  • 404