Page 1 of 1

dates

Posted: Tue May 31, 2005 7:44 am
by gurjit
Hi all,

I have this query which bring me the results for a counter weekly and displays the date.

I need for it to start at the start of every month (e.g. 01 May 2005), count the results for every week and then make the last date the end of the month (31st May 2005).

How can i do this:

Code: Select all

<?php
select DATE_FORMAT(DATE_SUB(h_date, INTERVAL WEEKDAY(h_date) DAY), \"%d/%m/%y\" ) AS start_of_week, DATE_FORMAT( DATE_SUB( DATE_ADD(h_date, INTERVAL 6 DAY ), INTERVAL WEEKDAY(h_date) DAY), \"%d/%m/%y\" ) AS end_of_week, DATE_FORMAT(h_date, \"%u %M %Y\") AS week, SUM(h_count) AS week_total,e.* FROM tbl_hitcounter e WHERE DATE_FORMAT(h_date, \"%M %Y\") = DATE_FORMAT(NOW(), \"%M %Y\") GROUP BY week ORDER BY week DESC
?>
the display should look like this

25/05/05 - 31/05/05 4802

17/05/05 - 24/05/05 5053

09/05/05 - 16/05/05 4788

01/05/05 - 08/05/05 5000

Posted: Tue May 31, 2005 7:56 am
by timvw

Code: Select all

$start = strtotime("1-$month-$year");
$end = strtotime("1-$month-$year +1 months");

Code: Select all

SELECT YEARWEEK(h_date) AS yearweek, SUM(h_count) AS week_total
FROM tbl_hitcounter
WHERE UNIX_TIMESTAMP(h_date) &gt;= $start AND UNIX_TIMESTAMP(h_date) &lt; $end
GROUP BY YEARWEEK(h_date)
ORDER BY yearweek DESC

Posted: Wed Jun 01, 2005 7:36 am
by gurjit
Hi timvw

does the same as my other query.

I need the dates to display like a calendar, counting only to the end of the month and displaying dates to end of the month, for example

06/06/05 - 12/06/05 900

01/06/05 - 05/06/05 800

-----------------------------------------------------------
30/05/05 - 31/05/05 600

23/05/05 - 29/05/05 4802

16/05/05 - 22/05/05 5053

09/05/05 - 15/05/05 4788

01/05/05 - 08/05/05 5000

The cut of point for the month is where it ends (30/05/05 - 31/05/05)

so when the new month starts it counts to sunday again (01/06/05 - 05/06/05)

Posted: Thu Jun 02, 2005 10:57 am
by gurjit
need help on this urgently..... cant get it to just read like a calendar