Display DATA from last month

dcjones

Hi all.

What I am trying to do is construct a query that display all the data from last month, whatever the month is today. This is what I am work with so far.

SELECT 
c.code,     

c.begin_date,     

c.expire_date,   

c.discount,   

c.member_id,     

c.product_id,     

c.used_count,     

c.saleprice,    

c.coupon_id, 
c.paid, 
m.member_id,   

m.agentrate,
p.payment_id, 
p.member_id,    

p.coupon_id,    

p.time,   

p.amount, 
p.p_discount, 
p.comm_paid,
p.d_paid(f_date)
FROM 
coupon c , members m, payments p
WHERE 
c.coupon_id = '1FD3683A1A'  

AND  c.coupon_id = p.coupon_id    

AND  m.member_id = '1338245'
AND  p.comm_paid = 0
AND
p.d_paid < "This is where I am stuck"
GROUP BY p.d_paid(f_date)

Can anyone point me in the right direction.

mwasif

What is the value of p.d_paid and what it holds?

dcjones

Hi mwasif.

The p.d_paid variable hold a date.

Many thanks for any help you can give

NogDog

There may be a better way, but I think this would do the trick:

. . . AND
DATE_FORMAT(p.d_paid, '%Y%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTHS), '%Y%m')
. . .

dcjones

Hi NogDog

Thanks for your input.

This is what I have now, but it displays an error. I have played around but cannot see what is wrong.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MONTHS ) , '%Y%m' )
LIMIT 0, 30' at line 1

SELECT 
c.code,      

c.begin_date,      

c.expire_date,    

c.discount,    

c.member_id,      

c.product_id,      

c.used_count,      

c.saleprice,     

c.coupon_id,  

c.paid,  

p.payment_id,  

p.member_id,     

p.coupon_id,     

p.time,    

p.amount,  

p.p_discount,  

p.comm_paid,  

p.d_paid,   

m.member_id,    

m.agentrate
FROM 
amember_baggagecoupon c ,amember_baggagepayments p, amember_baggagemembers m
WHERE 
c.coupon_id = '1FD3683A1A'   

AND  c.coupon_id = p.coupon_id     

AND  m.member_id = '1338245' 
AND DATE_FORMAT(p.d_paid, '%Y%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTHS), '%Y%m')
ORDER BY p.payment_id

mwasif

DATE_FORMAT(p.d_paid, '%Y%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m')