CODEWITHSUNDEEP
mysqlsql
Johnny
Johnny

Reputation: 416

mysql query how to show each day's total cash sale for the current week

I have the following mysql query which shows the each day's total cash sale for the current week.

SELECT  
  sum(Price) as totalprice,
  WEEKDAY(CreatedOn) as dayno,
  DATE(CreatedOn) as CreatedOn,
  AgentID  
FROM records
WHERE CreatedOn BETWEEN (CURDATE()-WEEKDAY(CURDATE())) AND CURDATE()
GROUP BY DATE(CreatedOn)

When I run the query it looks like this:

enter image description here

There are records on 30th November(today's date). So,

day 0 (Monday) no cash sale

day 1 (Tuesday) $5049

day 2 (Wednsday) $99

Nothing is displayed for day 3 (Thursday/today). I cannot figure out the reason there are definitely record in the database but cannot get them to be displayed. I would appreciate any help.

Upvotes: 0

Views: 1510

Answers (2)

spencer7593
spencer7593

Reputation: 108400

The condition in the query currently specifies on or before midnight today, so any rows for today after midnight are going to be excluded.

I think you are intending to specify CreatedOn before midnight of the following day.

I also suggest you don't subtract an integer value from a date/datetime, and instead use the INTERVAL syntax 

WHERE CreatedOn >= CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
  AND CreatedOn <  CURDATE() + INTERVAL 1 DAY

To test those expressions before we include them in a query, we can run a SELECT statement:

 SELECT CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
      , CURDATE() + INTERVAL 1 DAY

and verify that those expressions are returning what we expect, the values we want to use. For testing, we can replace CURDATE() with a date value to test the return for days other than today.

Upvotes: 2

Paul Maxwell
Paul Maxwell

Reputation: 35583

CURDATE() is today's date but at 00:00:00+0000000

"push up" the higher date by 1 day and avoid using between for date/time ranges:

WHERE CreatedOn >= date_sub(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) 
AND CreatedOn < date_add(CURDATE(), INTERVAL 1 DAY)


select date_sub(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) 
     , date_add(CURDATE(), INTERVAL 1 DAY)

Upvotes: 2

Related Questions