CODEWITHSUNDEEP
mysqldatabaseintervals
John S
John S

Reputation: 21

MYSQL Query works for current week not next

SELECT a.agent, a.talktime, a.percentclosed,COALESCE(COUNT(b.dealamount),0),COALESCE(SUM(b.dealamount),0) , b.dealdate
FROM closers a
LEFT JOIN deals b ON a.agent = b.agent
WHERE b.dealdate IS NULL OR YEARWEEK( b.dealdate ) = YEARWEEK( NOW( ) ) 
GROUP BY a.agent
ORDER BY COALESCE( SUM( b.dealamount ) , 0 ) DESC

Can anybody help me fix this query i know its messy but im new to this,

This works fine for the current week but if i add +1 to simulate a future week everybody who has a deal the week before does not show,

Thanks,

John,

IMAGE OF QUERY'S

Upvotes: 1

Views: 763

Answers (3)

Hituptony
Hituptony

Reputation: 2860

WHERE b.dealdate IS NULL OR WEEKOFYEAR( b.dealdate ) = WEEKOFYEAR( date_add(NOW( ), interval 1 week))

use the interval feature of mysql, and the date_add or date_sub function

Reference

Upvotes: 1

John S
John S

Reputation: 21

Got it to work!,

SELECT a.agent, a.talktime, a.percentclosed, COALESCE( COUNT( b.dealamount ) , 0 )     ,COALESCE( SUM( b.dealamount ) , 0 ) , b.dealdate
FROM closers a
LEFT JOIN deals b ON a.agent = b.agent
AND WEEKOFYEAR( b.dealdate ) = WEEKOFYEAR( NOW( ) ) 
WHERE b.dealdate IS NULL 
OR WEEKOFYEAR( b.dealdate ) = WEEKOFYEAR( NOW( ) ) 
GROUP BY a.agent
ORDER BY COALESCE( SUM( b.dealamount ) , 0 ) DESC 
LIMIT 0 , 30

Upvotes: 1

Ghigo
Ghigo

Reputation: 2332

WEEKOFYEAR is not good in this case because it will wrap at year's end. You should use YEARWEEK().

WHERE b.dealdate IS NULL OR YEARWEEK( b.dealdate ) = YEARWEEK( NOW( ) ) +1

Upvotes: 1

Related Questions