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
    160
    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
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 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
    •