SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Date Difference

  1. #1
    SitePoint Addict Feudalism's Avatar
    Join Date
    Jun 2002
    Location
    Buenos Aires, Argentina
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Date Difference

    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

  2. #2
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,449
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Off of the top of my head:

    WHERE date > NOW() - INTERVAL 1 WEEK

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict Feudalism's Avatar
    Join Date
    Jun 2002
    Location
    Buenos Aires, Argentina
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
           
     
          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.

  5. #5
    SitePoint Addict Feudalism's Avatar
    Join Date
    Jun 2002
    Location
    Buenos Aires, Argentina
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what about this? it's still buggy, but Im getting nearer ...

    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •