|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
May 2nd, 2005, 06:04 PM
|
|||
|
|||
|
Counts for date ranges.
Hi all,
I appreciate your taking a moment to help a brother out. I have a table similar to this: ----------------------------------------- -------- ---------------------------- CUSTOMER_NUMBER NOT NULL NUMBER(18) CLIENT_ID NOT NULL VARCHAR(18) ORDER_DATE NOT NULL DATE in my DB a new line is created in this table for every order processed. What I need to do is to build a query that will provide me a count by client id broken out by last 7 days, 8-14 days and 15-21 days. I would like the output to look something like this. CLIENT_ID Last 7 Days 8-14 Days 15-21 Days Hostess 15 21 4 Orowheat 8 25 19 I've tried writing this query 100 different times and have gotten nowhere. Any Help is appreciated.
|
|
#2
May 2nd, 2005, 09:24 PM
|
|||
|
|||
|
[Edit]
Here's a much nicer query than my original: Code:
SELECT client_id, SUM(IF(order_date > DATE_SUB(CURDATE(),INTERVAL 7 DAY),1,0)) as week_1, SUM(IF(order_date BETWEEN DATE_SUB(CURDATE(),INTERVAL 14 DAY) AND DATE_SUB(CURDATE(),INTERVAL 8 DAY),1,0)) as week_2, SUM(IF(order_date BETWEEN DATE_SUB(CURDATE(),INTERVAL 21 DAY) AND DATE_SUB(CURDATE(),INTERVAL 15 DAY),1,0)) as week_3 FROM table GROUP BY client_id Note that the above has been tested in MySQL only, so it may need some tweaking for your system. [/Edit]
__________________
"A pawn is the most important piece on the chessboard -- to a pawn" Last edited by Madpawn : May 3rd, 2005 at 12:54 AM.
|
|
| Viewing: Dev Articles Community Forums > Databases > General SQL Development > Counts for date ranges. |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
