Home » SQL & PL/SQL » SQL & PL/SQL » working plsql code need for this simple scenerio
working plsql code need for this simple scenerio [message #201989] Tue, 07 November 2006 14:15 Go to next message
sanotsh
Messages: 5
Registered: March 2005
Junior Member
Hi,

this description is lengthy but simple to understand and is complete. i need working plsql code for this process. i'm providing you with tables structure.

we have two tables cust_data and cust_contact everytime there is a new entry into cust_data, for the first time it is registered into cust_contact table with his custno and timestamp and no_of_times_contact will be set to 1,same like wise no_of_times_contact will be incremented along with timestamp into cust_contact table from cust_data table.

Table1 : cust_data -- multiple entries of same custno with different timestamps.

custno VARCHAR2(15) -- customer no
timestamp DATE -- datetime of contact
flag VARCHAR2(5) -- flag set to either 0 or 1


Table2 : cust_contact -- this will populate from above table

contact_reason VARCHAR2(10) -- this value is not need
no_of_times_contact NUMBER(3) -- total no. of times customer contacted us
custno VARCHAR2(15) -- customer no
timestamp date -- datetime of contact

if a customer does contact us for 7 days, on 8th day his no_of_times_contact will be reset 0. a customer need to contact only 3 times within 7 days. if he exceeds this limit then flag column is set to 1.

here is a condition that once flag is set to 1, no_of_times_contact will start decrementing
depending on a logic of first time contacted to first time non-contacted there is gap of 7 days.

u can see the time gap of 7-day & 15-day it is 7, same like wise between 8-day & 17 day, it is
same with 13-day and 19-day.

a cust can contact us any no. of times, but 3 is limit to set flag, and once flag is set conact count will start decrementing depending of 7 days gap of contact from his last contact.

I need a working plsql code for this follwing below scenerio in days.

Day 0 - cust contacts us, no_of_times_contact will be set to 1.
Day 7 - the no_of_times_contact need to be reset to 0 as he has not contacted back with 7 days.
Day 8 - same cust contacted us, no_of_times_contact gets set to 1
Day 10 - same cust contacted us, no_of_times_contact gets set to 2
Day 12 - same cust contacted us, no_of_times_contact gets set to 3
Day 13 - same cust contacted us, no_of_times_contact gets set to 4, this triggers flag set to 1
Day 15 - not contacted - no_of_times_contact reduces to 3, ( flag is still set as 1 )
Day 17 - not contacted - no_of_times_contact reduces to 2, ( flag is still set as 1 )
Day 19 - not contacted - no_of_times_contact reduces to 1, ( flag is still set as 1 )
Day 20 - not contacted - no_of_times_contact reduces to 0, ( flag is still set as 0 )
and so on ...

Please help how to put this logic in plsql code. i have tried but last part of decrementing
depending on 7-days gap after flag is set to 1 was difficult for me.

Please try to provide me complete plsql code working....in simple manner without using plsql tables and complex constructs, advanced features. it is needed to be complied on oracle 8/8i.

Thanks,
Vijay
kvijayreddy10@yahoo.com
Re: working plsql code need for this simple scenerio [message #201995 is a reply to message #201989] Tue, 07 November 2006 14:56 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Do you honestly expect someone to do your job for you for free?
Re: working plsql code need for this simple scenerio [message #202003 is a reply to message #201995] Tue, 07 November 2006 15:38 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OP
... in simple manner without using plsql tables and complex constructs, advanced features ...

./fa/450/0/ Sure; why not using SQL*Forms and V2 triggers? Those really aren't advanced.

Do you have any other wishes? A lemonade? Cup of coffee? Do sit back, relax, read a magazine while you wait for someone to code it for you. ./fa/1704/0/
Re: working plsql code need for this simple scenerio [message #202025 is a reply to message #202003] Tue, 07 November 2006 22:22 Go to previous messageGo to next message
sanotsh
Messages: 5
Registered: March 2005
Junior Member
this needed to be done in plsql code, not in forms.
i need decrementing part code.
Re: working plsql code need for this simple scenerio [message #202044 is a reply to message #202025] Wed, 08 November 2006 00:28 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You know what?
Why don't YOU write the code? Then, if you are stuck you come back and ask us for hints to solve a specific problem. Like anacedent said, you really don't expect anyone to write you a complete package, do you?
Previous Topic: How to fix ORA-01632 error?
Next Topic: Cursors
Goto Forum:
  


Current Time: Sat Dec 03 22:04:21 CST 2016

Total time taken to generate the page: 0.10445 seconds