Solved

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

Posted on 2011-09-27
6
626 Views
Last Modified: 2013-11-29
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
Comment
Question by:gloriagalvez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:Thomasian
ID: 36714327
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
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36714363
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
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36714371
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:gloriagalvez
ID: 36714418
I tried both solutions and they are not working.  They change all the records eventhough some orders were place within a year..
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36714428
Can you post the query you tried?

You can't test both queries as is since the syntax is for different dbms.
0
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 500 total points
ID: 36714488
>> 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question