February 11, 2014 at 12:53 am
I have two tables;
CUSTOMER (customer_id NUMBER, customer_name VARCHAR2(100), recently_purchased VARCHAR2(1))
and
CUST_PRODUCTS (product_id NUMBER, customer_id NUMBER, date_purchased DATE);
I need to update CUSTOMER, to set recently_purchased = 'Y' if product purchased products are in the last 12 months.
I know that two tables has to be joined and I can use CUST_PRODUCTS where DATEDIFF(month,date_purchased, GETDATE()) <= 12, but I can't get my query right. please help.
February 11, 2014 at 12:59 am
What do you have so far?
(your tables also seem to be Oracle tables, right?)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 11, 2014 at 1:26 am
so far I wrote it in PL\SQL:
UPDATE CUSTOMER
SET recently_purchased = 'Y'
WHERE customer_id in (select customer_id from cust_products where date_purchased >= ADD_MONTHS(TRUNC(SYSDATE),-12));
UPDATE CUSTOMER
SET recently_purchased = 'N'
WHERE customer_id not in (select customer_id from cust_products where date_purchased >= ADD_MONTHS(TRUNC(SYSDATE),-12));
February 11, 2014 at 1:34 am
Don't these statement work? Do you get an error or is the resulting data incorrect?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply