SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2004
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting revenue for specific month and year

    Hi all,

    I hav a revenue table where I store several prices, and the date of each transaction. Now I need th e revenue for a specific month/year. I have for now, this code, but it gives no result, although there are entries for that specific period.

    Code:
    SELECT SUM(treatment_price + arrangement_price + product_price) as revenue FROM (`revenue`) WHERE `user_id` = '9' AND month(date) = 02 and year(date) = 2010
    Can someone guide me to a solution for this?

    Kind regards,
    Michel

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Which data type is the column date defined as?

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2004
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Int.

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    The year and month functions expects a datetime value as input. What values do you store, unixtime or something else?

    It is better to store dates using the proper type.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    WHERE `date` >= UNIX_TIMESTAMP('2010-02-01')
      AND `date`  < UNIX_TIMESTAMP('2010-03-01')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist
    Join Date
    Jun 2004
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, thx. Got it working. I've used the method from r937.

    Thx all for helping out. Learned again today ;-)


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
  •