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

Post a Comment

Popular posts from this blog

Delete horizontal, vertical and angled lines from an image using Python to clear noise and read text with minimum errors

I was working on an algorithm to detect lines in a image. I found many solutions online, but none of them seem to work to my satisfaction. Thereafter, I decided to write an algorithm, which can work to my satisfaction. This algorithm can detect horizontal, vertical and angled lines. Following is the pseudo code of the algorithm. The implementation is in Python and it uses third party code. pix_array = Convert the image into 2D pixel array  Mono chromate the pixel array by setting values between 0-200 as 0 (black) and greater than 200 as 255(white) as  image_width = length of pix_array[0] image_height = length of pix_array Loop through pix_array, i.e. 1 pixel high horizontal slice of image: h = x coordinate of current pixel k = y coordinate of current pixel choose a value of radius as r draw a circle taking h,k as center using equation (X-h)^2 + (Y-k)^2 = r^2 derive values of X and Y and check if pix_arrY[X][Y] is black (0) if black add them to possible line c...
Read more

Unordered JSON compare for differences using javascript

Sometimes we need to programmatically compare JSON from two or more sources and determine what's the difference between them. The program below can do deep compare JSON even if they are not in the same order or conatins substructures and arrays. It provides the detailed list of mismatches and misses in both JSON's by calling out attributes with the same keys and different values or missing keys. const obj1={ "k1":"aq", "k2":"b", "k3":"c", "k4":{ "kk1":"aq", "kk2":"b", "kk3":"c", "kk4":{ "kkk1":"" }, "kk5":"abc" }, "k5":[{"kk5":"abc","kk6":"53"},{"kk5":"abc","kk6":"54"}] } const obj2={ "k1":"a", "k2...
Read more

Java Currency Formatter Changing $ to ¤

 Java currency formatting problem. Following code shows the conditions when the currency symbol is replaced by an alt currency symbol. public class Test { public static void main(String[] args){ double num = 1323.526; Locale eng = new Locale("", ""); NumberFormat engFormat = NumberFormat.getCurrencyInstance(eng); System.out.println(engFormat.format(num)); eng = new Locale("en", ""); engFormat = NumberFormat.getCurrencyInstance(eng); System.out.println(engFormat.format(num)); eng = new Locale("en", "en_US"); engFormat = NumberFormat.getCurrencyInstance(eng); System.out.println(engFormat.format(num)); eng = new Locale("", "US"); engFormat = NumberFormat.getCurrencyInstance(eng); System.out.println(engFormat.format(num)); eng = new Locale(...
Read more