Results 1 to 3 of 3
  1. #1
    Join Date
    May 2014
    Posts
    16

    Thumbs up Unanswered: sql statement getting the last 30 days of orders of each customers

    hi I need your help in constructing my SQL statement, I want to get only the last 30 days orders or each customers in my recordset sample.
    the sql should pick up each customer but only the last 30 days, the basis should be their last maximum transaction date orders minus 30 days.

    Sample Data

    Customer Transaction Date Amount Purchase
    ================================

    xxxx01 01/01/2016 100.00
    xxxx01 01/30/2016 200.00
    xxxx01 12/12/2015 150.00
    xxxx01 12/01/2014 105.00

    xxxx02 02/01/2016 100.00
    xxxx02 02/29/2016 200.00
    xxxx02 11/12/2015 150.00
    xxxx02 11/01/2014 105.00

    Maximum transaction date order for customer xxxx01 for 01/30/2016
    Maximum transaction date order for customer xxxx02 for 02/29/2016

    Desired output:

    Customer Transaction date Amount Purchase
    xxxx01 01/01/2016 100.00
    xxxx01 01/30/2016 200.00
    xxxx02 02/01/2016 100.00
    xxxx02 02/29/2016 200.00

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Not sure if this will work, but...
    Code:
    SELECT i.[Customer], i.[Transaction date], i.[Amount purchase] 
    FROM Invoices i INNER JOIN (SELECT [Customer], Max([Transaction date]) MaxDate FROM Invoices) mi
    ON i.Customer = mi.Customer
    WHERE i.[Transaction date] >= (mi.MaxDate - 30)
    DISCLAIMER:
    I haven't had coffee yet.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    May 2014
    Posts
    16
    thank you weejas, I will try that SQL and let you know but one thing only you use MAX function which requires aggregation anyway I will just add GROUP BY to the inner join SQL.

Posting Permissions

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