Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 637
  • 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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