Hi All,
I am having a scenario where i have two table which are as followed:
Table1:
UserId DealerCode Frequency
A AA Monthly
A BB Weekly
A CC Weekly
A DD Fortnightly
In this table a user can have one to one or one to many relationship with Dealer.
Table2:
UserId DealerCode Date
A AA 21/01/14
A BB 15/01/15
A BB 17/01/15
A BB 19/01/15
A BB 23/01/15
A BB 21/01/15
A DD 23/01/14
Now the scenarios are
Assumption: Week1=1 to 7, Week2=8 to 14, Week3=15 to 21 and Week4=22 to 31
1) we have to identify that one user have to attend how many calls in a month which will be calculated like this
Monthly =1, Weekly=4,Fortnightly=2 which makes A has to attend 1*1+2*4+1*2=11 calls in a month.
2) A user can max visit one dealer once in its frequency and if he has visited more than once than also we have to consider 1 which means A has visited BB 4 times in Week3 so we have to consider only 1 and visited BB once in Week4 than also its 1.
Can you please help in resolving this.
Thanks in advance.
Best Regards,
