Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
629 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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
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 …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

722 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