CODEWITHSUNDEEP
sqlms-access
user2296381
user2296381

Reputation: 197

SQL Query For Customers Not Used For Last 3 Years

I THINK I'm having some trouble.

I'm trying to query 2 tables for customers that haven't been used on the table for the last 3 Years. The data consists of data ranging for 7+ years, so customers are used multiple times.

I think the issue with my current query: It's finding data of customers not used in the last 3 years... but it's not accounting for if there is also data of the customer within the last 3 years as well.

Can someone possibly help me? I'm guessing the answer is to use only the data of the customer with the latest date and ignore previous data.

SELECT DISTINCT 
    tbl_Customer.CustomerID
    , tbl_Customer.CustomerName
    , Table1.ImportDate
    , Table2.ImportDate
FROM 
    tbl_Customer 
LEFT JOIN 
    Table1 ON tbl_Customer.CustomerName = Table1.CustomerName 
LEFT JOIN 
    Table2 ON tbl_Customer.CustomerName = Table2.CustomerName
WHERE 
    (((DateAdd("yyyy", 3, [Table2].[ImportDate])) < Now()) 
AND 
    ((DateAdd("yyyy", 3, [Table1].[ImportDate])) < Now()))
ORDER BY 
    Table1.ImportDate DESC, 
    Table2.ImportDate DESC;

Upvotes: 1

Views: 318

Answers (3)

user2864740
user2864740

Reputation: 61875

The core problem with the initial query is that, for no imports (which will happen for "no order" customers) the condition

    DateAdd("yyyy", 3, ImportDate) < Now()
--> DateAdd("yyyy", 3, NULL) < Now()
--> NULL < Now()
--> NULL (or not true)

is not true. A simple fix is to add a guard

([Table1].[ImportDate] IS NULL
 OR DateAdd("yyyy", 3, [Table1].[ImportDate]) < Now())

around such expressions or to coalesce the NULL value before using it.

The ordering will also be wrong, as that means order by one value and then the other, not "by the greater of both" values. Compare with

ORDER BY
IIF(Table1.ImportDate > Table2.ImportDate, Table1.ImportDate, Table2.ImportDate)

However, I would use a LEFT JOIN on customers/orders, GROUP BY with a MAX on the order dates. Then you can use that result (as a derived subquery) to complete the query asked fairly trivially.

SELECT
    c.CustomerID
  , MAX(o.ImportDate) as lastImport
FROM tbl_Customer as c
-- The UNION is to simply "normalize" to a single table.
-- (Also, shouldn't the join be on a customer "ID"?)
LEFT JOIN (
        SELECT CustomerName, ImportDate from Table1
        UNION
        SELECT CustomerName, ImportDate from Table2) as o
    ON c.CustomerName = o.CustomerName
GROUP BY c.CustomerID

Then,

SELECT s.CustomerID
FROM (thatSubQuery) as s
WHERE
    -- no orders
    s.lastImport IS NULL
    -- only old orders
 OR DateAdd("yyyy", 3, s.lastImport) < Now()
ORDER BY s.lastImport

(YMMV with MS Access, this will work in a "real" database ;-)

Upvotes: 1

esmoore68
esmoore68

Reputation: 1296

Based on what I can infer from your query about your data structure, I think you want something like this:

DECLARE @CutOff DateTime
SET @CutOff = DATEADD(y, -3 GETDATE())

SELECT tbl_Customer.CustomerID, tbl_Customer.CustomerName
WHERE (CustomerName IN 
    (SELECT CustomerName FROM Table1 WHERE ImportDate < @CutOff))
    OR 
    (CustomerName IN 
    (SELECT CustomerName FROM Table2 WHERE ImportDate < @CutOff)))
    AND CustomerName NOT IN
    (SELECT CustomerName FROM Table1 WHERE ImportDate > @CutOff)
    AND CustomerName NOT IN
    (SELECT CustomerName FROM Table2 WHERE ImportDate > @CutOff)

Upvotes: 0

sion_corn
sion_corn

Reputation: 3141

SELECT DISTINCT 
    tbl_Customer.CustomerID, 
    tbl_Customer.CustomerName, 
    Table1.ImportDate, 
    Table2.ImportDate
FROM (tbl_Customer 
LEFT JOIN Table1 
    ON tbl_Customer.CustomerName = Table1.CustomerName) 
LEFT JOIN Table2 
    ON tbl_Customer.CustomerName = Table2.CustomerName
WHERE DateAdd("yyyy",3,[Table2].[ImportDate]) < Now() 
AND DateAdd("yyyy",3,[Table1].[ImportDate]) < Now() 
AND tbl_Customer.CustomerID NOT IN (
    SELECT DISTINCT 
        tbl_Customer.CustomerID, 
    FROM (tbl_Customer 
    LEFT JOIN Table1 
        ON tbl_Customer.CustomerName = Table1.CustomerName) 
    LEFT JOIN Table2 
        ON tbl_Customer.CustomerName = Table2.CustomerName
    WHERE DateAdd("yyyy",3,[Table2].[ImportDate]) >= Now() 
    AND DateAdd("yyyy",3,[Table1].[ImportDate]) >= Now() 
)
ORDER BY Table1.ImportDate DESC , Table2.ImportDate DESC;

Upvotes: 0

Related Questions