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
624 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

739 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