0

hi,

been trawling the web trying to find anything on this but cant find anything to hand.

my query:

SELECT monthlycommission.DatePaid, monthlycommission.MonthPaid, monthlycommission.YearPaid -1 year
FROM monthlycommission
where YearPaid = now()
group by MonthPaid
order by DatePaid ASC

basically this query works however it is not what i want it to be.

i want it to where the YearPaid is the current year minus 1

so i will have thid query along with anotehr query which is where YearPaid is now().

so it will be comparing this years commision vs last years amount paid.

the YearPaid - 1 year only subtracts it to 2010 in the output, but the data is still from this year so i need a where clause that is a minus 1 year.

any help greatly appreciated.

thanks

2
Contributors
3
Replies
4
Views
5 Years
Discussion Span
Last Post by urtrivedi
0

ok thought i was getting further however it is not returning the -1 year.

this is the code i tried. also tried curdate()

code:

SELECT monthlycommission.YearPaid, sum(monthlycommission.Amount)CommissionPaid, monthlycommission.MonthPaid
FROM monthlycommission
WHERE DatePaid is not null and DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY MonthPaid
ORDER BY DatePaid ASC

still no joy

0

I think just write -1 there in interval

SELECT monthlycommission.YearPaid, sum(monthlycommission.Amount)CommissionPaid, monthlycommission.MonthPaid
FROM monthlycommission
WHERE DatePaid is not null and DATE_SUB(NOW(), INTERVAL -1 YEAR)
GROUP BY MonthPaid
ORDER BY DatePaid ASC
0

THIS IS LITTLE MODIFIED VERSION
I think just write -1 there in interval

SELECT monthlycommission.YearPaid, sum(monthlycommission.Amount)CommissionPaid, monthlycommission.MonthPaid
FROM monthlycommission
WHERE DatePaid is not null and year(DATE_SUB(NOW(), INTERVAL -1 YEAR))-monthlycommission.YearPaid
GROUP BY MonthPaid
ORDER BY DatePaid ASC
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.