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

Caused by: java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation

HashiCorp Vault Integration with Ansible Etower using approle

utility to extract date from text with java