Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Jan 2010
    Posts
    161
    Thanks
    10
    Thanked 1 Time in 1 Post

    SQL QUERYY HELP not MySQL

    Hi

    Although similar I need some help with a SQL query.

    I have a table called ODBC_INVOICE_ALL

    Within this it has columns

    CustomerNumber
    InvoiceDate
    Total

    What I need the report to do is the following as each invoice during the period are all on separate lines.

    In the last 30 days which Customers have spent over £5000 and show the Total Spend and Customer Number.

    Thank in advance.

    Roy

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,636 Times in 4,598 Posts
    Because handling of dates and times varies *SO MUCH* from one database to another, you NEED to tell us what database you are using.

    Just for example:

    Using Access or JET OLEDB database:
    Code:
    SELECT customernumber, SUM(total) AS totalSpent
    FROM invoice_all
    WHERE invoicedate > ( DATE() - 30 )
    GROUP BY customernumber 
    HAVING SUM(total) > 5000
    ORDER BY customernumber
    Using SQL Server 2010 or later:
    Code:
    SELECT customernumber, SUM(total) AS totalSpent
    FROM invoice_all
    WHERE invoicedate > ( CONVERT(DATE, getDate())  - 30 )
    GROUP BY customernumber 
    HAVING SUM(total) > 5000
    ORDER BY customernumber
    Using older SQL Server:
    [code]
    Code:
    SELECT customernumber, SUM(total) AS totalSpent
    FROM invoice_all
    WHERE invoicedate > ( CONVERT(DATETIME,CONVERT(VARCHAR,getDate(),112),112) - 30 )
    GROUP BY customernumber 
    HAVING SUM(total) > 5000
    ORDER BY customernumber
    And so on.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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