group by weak of month in mssql

Let the data be in format

CREATE TABLE contacts (
         id INT,
         status VARCHAR(100),
         created DATETIME DEFAULT NULL
       );
INSERT INTO contacts (id,status,created) VALUES(3,'3','2015-05-12');
INSERT INTO contacts (id,status,created) VALUES(4,'4','2015-02-09');
INSERT INTO contacts (id,status,created) VALUES(5,'5','2011-07-12');
INSERT INTO contacts (id,status,created) VALUES(6,'5','2015-05-01');
INSERT INTO contacts (id,status,created) VALUES(7,'3','2011-06-12');
INSERT INTO contacts (id,status,created) VALUES(8,'2','2011-07-12');
INSERT INTO contacts (id,status,created) VALUES(9,'1','2011-05-12');
INSERT INTO contacts (id,status,created) VALUES(10,'1','2011-06-12');
INSERT INTO contacts (id,status,created) VALUES(11,'1','2011-05-12');
INSERT INTO contacts (id,status,created) VALUES(12,'1','2011-06-12');
 
 
Now the requirement is to find weekly counts, by week of month as 'first week of November'. The following query will help.

with T as
(
select id,
convert(varchar,year(created))+'-'+
convert(varchar,month(created))+'-'+
convert(varchar,(datepart(week,created)) +1 -
datepart(week,convert(datetime,'01.'+convert(varchar,month(created))+'.'+convert(varchar,year(created)),103)))
as week_key
from contacts)
select count(*) as counter,week_key
from T
group by week_key

Follow following link for demo.

http://sqlfiddle.com/#!6/423bf/2/0

Comments

Popular posts from this blog

Unordered JSON compare for differences using javascript

Print multi tree data structure ascii visualization on console with java

utility to extract date from text with java