Member Avatar for mrhankey

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
  • 124 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for mrhankey

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

Member Avatar for urtrivedi

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
Member Avatar for urtrivedi

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.