Start and finish date of each week of the year

Question

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/

yes i didn't add the correct dates in there i probably should have now that i think about it — Nathan, Jan 02 '15 at 12:12

Saharsh Shah · Accepted Answer · 2015-01-02T13:47:52.627

1

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;

edited Jan 02 '15 at 13:47

answered Jan 02 '15 at 12:08

Saharsh Shah

28,687
8
48
83

just tried this and doesn't show any results `SELECT CEILING(DAYOFYEAR(`lead_date`) / 7) AS WeekNo, COUNT(`lead_idno`) AS leadCount FROM tbl_contacts GROUP BY WeekNo` – Nathan Jan 02 '15 at 12:19
yes mysql and no error in the query im running the command in phpmyadmin – Nathan Jan 02 '15 at 12:39
cant seem to work out how to use the mysql fiddle keeps giving me all these answers – Nathan Jan 02 '15 at 13:03
@Nathan Then just add sample data in table format in your question – Saharsh Shah Jan 02 '15 at 13:05
for me it comes up as leadyear null weekno null leadcount 54 when i run this in phpmyadmin – Nathan Jan 02 '15 at 13:41
the date is stored as varchar – Nathan Jan 02 '15 at 13:45
how can i now get that to display on a page as in the php code to display it :-) – Nathan Jan 02 '15 at 13:50
just need to display the results like they are year week leads im unsure how to display leadYear and weekNo as there not row names – Nathan Jan 02 '15 at 13:57
was chasing this :-) `$result2 = mysqli_query($con,"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"); while($row2 = mysqli_fetch_array($result2)) { echo $row2['leadYear'] . " " . $row2['WeekNo'] . " " .$row2['leadCount'] . "
" ; }` – Nathan Jan 02 '15 at 14:06
thanks for all our your help and taking the time to help me out :-) – Nathan Jan 02 '15 at 14:09

Start and finish date of each week of the year

1 Answers1