• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 634
  • Last Modified:

How do I set the status of a field to inactive for customers who did not order in the last 1 year?

Hello:
I really need help on solving the following problems.  I appreciate any input.
Please  refer to schema in the attached file.
Question:
Set Status field to inactive for all customers who have not placed any order in the last 1 year. Get-Customer.xls
0
gloriagalvez
Asked:
gloriagalvez
  • 3
  • 2
1 Solution
 
ThomasianCommented:
Don't forget to create a backup before running the update.
UPDATE C
SET Status='Inactive'
FROM Customer C
WHERE NOT EXISTS (SELECT 1 FROM Order WHERE C.CustomerID=CustomerID AND OrderDate >= DATEADD(year,-1,CAST(GETDATE() as int)))

Open in new window

0
 
datAdrenalineCommented:
Since the inactive status is a calculated value, then it is not advisable to store it.  You can easily get the customers status with a Query with a SQL View that looks something like this:

SELECT CustomerId
    , CustomerName
    , IIf(Max(Order.OrderDate) >= DateAdd("yyyy",-1,Date()), "Active","In-Active") As Status
FROM Customer LEFT JOIN Order ON Customer.CustomerId = Order.CustomerId
GROUP BY CustomerId, CustomerName

If you do indeed wish to store that status, then you can use the above Query in a new UPDATE Query that updates the Customer table.
0
 
datAdrenalineCommented:
Please note that the SQL syntax I provided was for use within an MDB/ACCDB acting upon Linked Table objects or Local Table objects.

I felt the need to add this because you have tagged this question for SQL Server and MS Access, so I am unsure of the db engine that will be processing your statements.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gloriagalvezAuthor Commented:
I tried both solutions and they are not working.  They change all the records eventhough some orders were place within a year..
0
 
ThomasianCommented:
Can you post the query you tried?

You can't test both queries as is since the syntax is for different dbms.
0
 
datAdrenalineCommented:
>> I tried both solutions and they are not working.  They change all the records eventhough some orders were place within a year..  <<

The Query I posted does not change the data?

... BUT ... I fogot to encapuslate the Order table in square brackets.  "Order" is a reserved word (ie: its the keyword for an ORDER BY clause)

SELECT CustomerId
    , CustomerName
    , IIf(Max([Order].OrderDate) >= DateAdd("yyyy",-1,Date()), "Active","In-Active") As Status
FROM Customer LEFT JOIN [Order] ON Customer.CustomerId = [Order].CustomerId
GROUP BY CustomerId, CustomerName

Again, this SQL does not modify any data.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now