Date Difference

[Feudalism]

how can I modify this query so I get only the past week's ventas? there is a field called "fecha" that stores the date of the selling.

SELECT ventas. * , ventas.importe_acordado - SUM( pagos.importe ) AS valor_dinamico
FROM ventas
LEFT OUTER JOIN pagos ON pagos.venta = ventas.id
GROUP BY ventas.id
HAVING valor_dinamico >0

[stymiee]

Off of the top of my head:

WHERE date > NOW() - INTERVAL 1 WEEK

[r937]

depends what you mean by "the past week"

if a week runs sunday to saturday, the past week was december 9 thru 15

but you probably wanted stymiee's solution

[Feudalism]

       
 
      SELECT ventas. * , ventas.importe_acordado - SUM( pagos.importe )  AS valor_dinamico
FROM ventas
LEFT  OUTER  JOIN pagos ON pagos.venta = ventas.id
WHERE STR_TO_DATE( pagos.fecha,  '%d/%m/%Y'  )  > NOW(  )  -  INTERVAL 1 WEEK
GROUP  BY ventas.id
HAVING valor_dinamico >0
LIMIT 0 , 30     
      MySQL ha dicho:  
  #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 'WEEK  GROUP BY ventas.id  HAVING valor_dinamico > 0 
 LIMIT 0, 30' at line 1 
// NOW() - INTERVAL 1 WEEK\r\nGROUP BY ventas.id\r\nHAVING valor_dinamico >0 '); }   if (window.parent.frame_content) {     // reset content frame name, as querywindow needs to set a unique name     // before submitting form data, and navigation frame needs the original name     if (window.parent.frame_content.name != 'frame_content') {         window.parent.frame_content.name = 'frame_content';     }     if (window.parent.frame_content.id != 'frame_content') {         window.parent.frame_content.id = 'frame_content';     }     //window.parent.frame_content.setAttribute('name', 'frame_content');     //window.parent.frame_content.setAttribute('id', 'frame_content'); } //]]>

I think Im having some trouble.. also, is there a way to SUM( pagos.importe ) only where pagos.type = 3 ? is there a way to have a condition in the sum?

I dont want to trouble you all night long, so Im currently reading on google about this, anyway, any help or recommended reading is appreciated.

[Feudalism]

what about this? it's still buggy, but Im getting nearer ...

SELECT ventas. * 
, ventas.importe_acordado - SUM( pagos.importe )  AS valor_dinamico
, STR_TO_DATE( pagos.fecha,  '%d/%m/%Y'  ) - NOW() as last_contact

FROM ventas
LEFT  OUTER  JOIN pagos ON pagos.venta = ventas.id
WHERE STR_TO_DATE( pagos.fecha,  '%d/%m/%Y'  )  > NOW(  )  -  INTERVAL 1 WEEK
GROUP  BY ventas.id
HAVING valor_dinamico >0  and last_contact > INTERVAL 1 WEEK