Complex Query Calculation Help

Taylor O'Cain
Taylor O'Cain used Ask the Experts™
on
Hi all,

I'm looking for some help writing a rather complex query against my Access database. I have no dedicated SQL experience, only advanced Excel/Access experience.

I have a single database setup that has orders going back 6 years.

I'm trying to compare the Historical orders with the Current orders and flag the Current Orders that fall within a -120 & +90 date window around the expected contract End Dates from the Historical Orders. Problem is that for large customers, they can have hundreds of orders, all with different date ranges, and they all don't need to be flagged. Only the ones that fall within the window in which we expect the next order to be placed.

I've created unique customer ID's for all customers so that part is not the problem. Only the date window piece.

Any help would be much appreciated. I'm open to exploring all options as well, this doesn't necessarily need to be done via SQL if there is a better known way.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
post sample data or export some data to excel and upload here.

a sample query will be

select CustomerID, [Current Orders], [End Dates]
Form [Historical Orders]
Where [End Dates] Between DateAdd("d",-120,Date()) And DateAdd("d",90,Date())
HainKurtSr. System Analyst

Commented:
or create a simple access db with those tables and some data
zip and attach here...

Not: of course data should be obscured...
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can use a query like this:

Select 
    CurrentOrders.*,
    HistoricalOrders.*
From
    CurrentOrders,
    HistoricalOrders
Where
    CurrentOrders.CustomerID = HistoricalOrders.CustomerID
    And
    CurrentOrders.OrderDate Between 
        DateAdd("m", -4, HistoricalOrders.EndDate) And 
        DateAdd("m", 3, HistoricalOrders.EndDate)

Open in new window

/gustav
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Author

Commented:
Thanks everyone for the input thus far.

Gustav, I think the query may need to be a bit more than that. I've attached a sample data set here.

Column G is what the lines should be flagged as due to the corresponding lines I highlighted in the respective colors. This is all for the same customer but the data set has many more customers mixed in.
ExampleWorkbook.xlsx
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It's really not very clear what you ask for, but if you have one table only, it could be similar to this:

Select 
    CurrentOrders.*,
    HistoricalOrders.*
From
    Orders As CurrentOrders,
    Orders As HistoricalOrders
Where
    CurrentOrders.CustomerID = HistoricalOrders.CustomerID
    And
    CurrentOrders.FlaggedAs = "New Order"
    And
    HistoricalOrders.FlaggedAs = "Closed Order"
    And
    CurrentOrders.StartDate Between 
        DateAdd("m", -4, HistoricalOrders.EndDate) And 
        DateAdd("m", 3, HistoricalOrders.EndDate)

Open in new window

/gustav

Author

Commented:
Let me try to take another stab at the ask. Maybe this is more simple than i expected.

First off, that file is all that I have and I've removed all but one customer. Using only the contract end and start dates, I need to flag any line that has a start date within 120 days before and 90 days after an end date for the same customer ID.

It looks like the script above is close, just not sure what you mean by CurrentOrders vs. HistoricalOrders given that it's all from the same table.
Top Expert 2016

Commented:
please explain the following
that has a start date within 120 days before and 90 days after an end date
before what? before the current date?

Author

Commented:
Sure.

If the contract begin date of any given line is within a "window" of 120 days before and 90 days after when we expect it to end, or contract end date of another line, then its a renewed order. Specifically, the line containing said contract begin date.

Real life example:
You have a 2 year contract on an iPhone with Verizon that you purchased April 1 of 2015. Verizon would expect you to come back into the store in a "window" centered around April 1 of 2017 to renew the contract. That window for a renewed order would be from 12/2/16 (i.e. 4/1/17 - 120 days) to 6/20/17 (i.e. 4/1/17 + 90 days). If you came in any earlier than 12/2/16 or after 6/20/17 then we'd consider it a new order, like if you needed a new phone case or charger.
Top Expert 2016

Commented:
test this query, change tblOrders with actual name of table

SELECT tblOrders.[Customer ID], tblOrders.[Contract Start Date], tblOrders.[Contract End Date], tblOrders.[Flagged As], IIf([Contract End Date] Between DateAdd("d",-120,Date()) And DateAdd("d",90,Date()),"Renew Order","New Order") AS FlagAs
FROM tblOrders
ORDER BY tblOrders.[Contract Start Date];

Author

Commented:
Thanks Rey

One ask for clarification regarding the IIf statement. The check should not be against the current date which I believe is how you wrote it with the Date()... The check should be if the Contract Start Date falls within the range around the other lines Contract END Date.

Would we rewrite that to be?

IIf([Contract Start Date] Between DateAdd("d",-120,[Contract End Date]) And DateAdd("d",90,[Contract End Date]),"Renew Order","New Order") AS FlagAs
Top Expert 2016

Commented:
The check should be if the Contract Start Date falls within the range around the other lines Contract END Date.

by <other lines Contract END Date> do you mean the Contract END Date of the previous record?

are the dates for Contract Start Date and Contract END Date you posted real?
if not please provide REAL dates

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial