Getting all rows between date range with min date in column

Question

I'm trying to construct a SQL statement for MS Access to grab all sales from our database by product that have never been sold to a particular customer before 1/1/2013. I believe the following SQL statement does that:

SELECT
  Sales.CustomerID,
  Min(Sales.InvoiceDate) AS MinOfInvoiceDate,
  Sum(SalesDetail.StdCost) AS SumOfStdCost
FROM
  Sales INNER JOIN SalesDetail
  ON Sales.SalesID = SalesDetail.SalesID
GROUP BY
  Sales.CustomerID
HAVING
  (((Min(Sales.InvoiceDate))>=#1/1/2013#));

The table Sales contains: ID, invoice number, customer ID and date. (I'd call it Invoice, but I didn't design it...) SalesDetail is each invoice line item: Sales/Invoice ID, ProductID/Number, Qty and Cost

My question is, how would I break this out further by month? Any help is appreciated!

Edit: Alrighty! :) So here's an example of output from the above query:

CustomerID | MinOfInvoice | SumOfStdCost
Customer1  | 2/1/2013     | $300

What I would like, is to show each invoice and it's date (or just month, doesn't matter) while maintaining the grouping/expression (?) criteria of the customer having a minimum invoice date greater than 1/1/2013, like so

CustomerID | InvoiceDateMonth  | StdCost
Customer1  | 2/1/2013          | $100
Customer1  | 3/15/2013         | $130
Customer1  | 4/7/2013          | $70

Customer1 started getting invoices after 1/1/2013, so they should be listed.

Customer2 has invoices in 2012 and 2013, so it should not be in the query results.

I really hope this is possible, I've been beating my head on it for days now, and would rather skip the painful process of having to check each customer individually in my code. :( I believe in making the DB do the work. ;)

Thanks again for any help!

The term "break this out further by month" is pretty vague. Consider editing your question to include an example of what you want the result to look like, and the selection/aggregation criteria you want applied. Sometimes doing so will even help you answer your own question. — Gord Thompson

Gord Thompson Gord Thompson · Accepted Answer · 2013-05-14T07:49:24

You can pull out more detail for those customers by performing a very similar query that uses a "stripped-down" version of your original query as a subquery to restrict the results to those customers of interest. The basic query for all customers would be...

SELECT
    Sales.CustomerID,
    Sales.InvoiceDate,
    SalesDetail.StdCost
FROM
    Sales INNER JOIN SalesDetail
        ON Sales.SalesID = SalesDetail.SalesID

...so we can simply add a WHERE clause that uses logic similar to your original query to include only the customers we want

SELECT
    Sales.CustomerID,
    Sales.InvoiceDate,
    SalesDetail.StdCost
FROM
    Sales INNER JOIN SalesDetail
        ON Sales.SalesID = SalesDetail.SalesID
WHERE 
    Sales.CustomerID IN
        (
            SELECT CustomerID 
            FROM Sales
            GROUP BY CustomerID
            HAVING MIN(InvoiceDate)>=#2013-01-01#
        )

Another way to do it would be to use a slightly different subquery to exclude customers with earlier invoices

SELECT
    Sales.CustomerID,
    Sales.InvoiceDate,
    SalesDetail.StdCost
FROM
    Sales INNER JOIN SalesDetail
        ON Sales.SalesID = SalesDetail.SalesID
WHERE 
    Sales.CustomerID NOT IN
        (
            SELECT CustomerID 
            FROM Sales
            WHERE InvoiceDate<#2013-01-01#
        )

Getting all rows between date range with min date in column

1 Answers