Re: Query to Select records in the last 4 weeks

[sangprabv]

Hi, I have tried to use this query: SELECT count(smsc_id) as total,
insertdate FROM momtbak   WHERE insertdate   BETWEEN
DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK) AND CURRENT_DATE() group by
week(date_format(insertdate,'%Y-%m-%d'),3) to group records in the
last 4 weeks by week. But the result returns this list:

 144
2008-11-06 07:00:24
1883
2008-11-10 07:00:06
1645
2008-11-17 11:59:46
2476
2008-11-24 21:54:11
1015
2008-12-01 20:45:43

The expected result is the date shown above is the weeknumber. What do I miss 
here? TIA


Willy


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

[SOLVED]Re: Query to Select records in the last 4 weeks

[sangprabv]

Hi,
I finally found the solution
SELECT count( smsc_id ) AS total, week( insertdate ) AS tanggal
FROM momtbak
WHERE insertdate
BETWEEN DATE_SUB( CURRENT_DATE( ) , INTERVAL 4 WEEK ) 
AND CURRENT_DATE( ) 
GROUP BY week( insertdate )

Willy


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query to Select records in the last 4 weeks

[Daevid Vincent]

On Thu, 2008-12-04 at 08:27 +0700, sangprabv wrote:

 Hi,
 I get stuck to build a query to select records between curdate() and the
 last 4 weeks and groupped by week. I tested with:
 
 SELECT * 
 FROM momtbak
 WHERE insertdate
 BETWEEN curdate( ) 
 AND curdate( ) - INTERVAL 4 week
 
 It doesn't work. Please help, TIA.


http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_date-add

Untested, but something like this maybe?

SELECT * FROM momtbak 
 WHERE insertdate 
 BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK) AND CURRENT_DATE();

Re: Query to Select records in the last 4 weeks

[sangprabv]

Hi,
Thanks for the prompt reply. I have tested yours and it seems to be
working. What about to group the result by week? TIA.


Willy
Your life would be very empty if you had nothing to regret.


-Original Message-
From: Daevid Vincent [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Cc: sangprabv [EMAIL PROTECTED]
Subject: Re: Query to Select records in the last 4 weeks
Date: Wed, 03 Dec 2008 17:52:32 -0800

SELECT * FROM momtbak 
 WHERE insertdate 
 BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK) AND CURRENT_DATE();


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]