CODEWITHSUNDEEP
mysqlsqldateselectweek-number
Nathan
Nathan

Reputation: 509

Start and finish date of each week of the year

I am trying to display on a page each week of the years start and finish date so like below

Week 1 - 1/1/2015 - 7/1/2015  
Week 2 - 8/1/2015 - 14/1/2015

and so forth is there any way to achieve this my end result will be displaying how many leads have been entered into our CRM for each week of the year so like

week 1 50 leads  
week 2 10 leads

table structure can be found here http://jsfiddle.net/prac85yx/

Upvotes: 0

Views: 129

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT YEAR(STR_TO_DATE(lead_date, '%d/%c/%Y')) leadYear, 
       CEILING(DAYOFYEAR(STR_TO_DATE(lead_date, '%d/%c/%Y')) / 7) AS WeekNo, 
       COUNT(lead_idno) AS leadCount
FROM tbl_contacts 
GROUP BY leadYear, WeekNo;

Upvotes: 1

Related Questions