CODEWITHSUNDEEP
sql
Ryan Flood
Ryan Flood

Reputation: 198

Selecting between a range of dates, controlling for age at those dates

I am trying to search a record that is structured like this 

trans_Date, customer_age, etc

Where customer age is the age at the time of the transaction. What I need to do is to select all customers who are currently a certain age, who had a transaction between a date range.

Lets say I only wanted current 20 year olds who purchased when between 2005 and 2010

I came up with this

select * from trans_logs 
where trans_date between TO_DATE("07/13/2005:00:00","MM/DD/YYYY:HH24:MI" and TO_DATE("01/01/2010:00:00","MM/DD/YYYY:HH24:MI")
and customer_age between 13 and 18

I get some correct results, but I also get people who transacted in 2005 at 18 (making them 25 now)

and((customer_age=13 and trans_date BETWEEN TO_DATE('01-JAN-2005:00:00'.'DD-MON-YYYY:HH24:MI') and TO_DATE('31-DEC-2005:23:59','DD-MON-YYYY:HH24:MI'))
or (customer_age=14 and trans_date BETWEEN TO_DATE('01-JAN-2006:00:00'.'DD-MON-YYYY:HH24:MI') and TO_DATE('31-DEC-2006:23:59','DD-MON-YYYY:HH24:MI'))
or (customer_age=15 and trans_date BETWEEN TO_DATE('01-JAN-2007:00:00'.'DD-MON-YYYY:HH24:MI') and TO_DATE('31-DEC-2007:23:59','DD-MON-YYYY:HH24:MI'))
or (customer_age=16 and trans_date BETWEEN TO_DATE('01-JAN-2008:00:00'.'DD-MON-YYYY:HH24:MI') and TO_DATE('31-DEC-2008:23:59','DD-MON-YYYY:HH24:MI'))
or (customer_age=17 and trans_date BETWEEN TO_DATE('01-JAN-2009:00:00'.'DD-MON-YYYY:HH24:MI') and TO_DATE('31-DEC-2009:23:59','DD-MON-YYYY:HH24:MI'))
or (customer_age=18 and trans_date BETWEEN TO_DATE('01-JAN-2010:00:00'.'DD-MON-YYYY:HH24:MI') and TO_DATE('31-DEC-2010:23:59','DD-MON-YYYY:HH24:MI')) )

But that returns an error saying I am missing a right paren. 1. What am I missing? and 2. Is there a more efficient way to do this?

EDIT: Forgot the second date and some other formatting things

Upvotes: 0

Views: 292

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You need to calculate the current age, given the information you have. The idea is to add the number of years since the transaction to the age. I'm not great with Oracle datetime functions, but the following should work:

select tl.*, 
from trans_logs tl
where customer_age + (SYSDATE- trans_Date)/365 = 20

Do note this is an approximation, since you will get people who are about "20" depending on the relatinonship between their birthdate and the transaction date.

It is better to bring in the birthdate, if you can. I do understand that asking people their age might be easier than asking people their birthdate, so you might not have a choice.

Upvotes: 1

Related Questions