Counting enquires sent to partner.

[Darren]

Hello,



I am trying to design an ASP system which will submit the contents of a
feedback form to a partner, based on a daily maximum number of feedback
forms per day. For example, if partner1 whishes to receive no more than 5
feedback forms per day, then I want the system to check how many forms it
has submitted to partner1 in previous 24 hours and then act accordingly. If
the number has reached the partner maximum, I want it to submit to the next
partner and so on. So, if I have 5 partners each taking 5 forms per day, I
want the system to automatically distribute 5 emails per partner based on 25
feedback forms received in 24 hours.



Any ideas? Here is how I see it working (not real code):



DEFINE PARTNER RECIPIENT:

[email]PARTNER1@DOMAIN.COM[/email]

[email]PARTNER2@DOMAIN.COM[/email]

[email]PARTNER3@DOMAIN.COM[/email]



DEFINE NUMBER OF ENQUIRIES PER 24 HOURS:

PARTNER1: 5 enquiries per 24hrs

PARTNER2: 10 enquiries per 24hrs

PARTNER3: 2 enquiries per 24hrs

> USER SUBMIT FORM

If Partner1 Number of Enquiries in last 24 hours <= 5 then submit to
Partner1

Else if Partner2 Number of Enquiries in last 24 hours <= 10 then
submit to Partner2

Else if Partner3 Number of Enquiries in last 24
hours <= 2 then submit to Partner3

ELSE

Submit to overflow partner



Any ideas or tips greatly appreciated. It's the counting of feedback forms
in last 24hrs which is confusing me! I am thinking about inserting each
feedback form to a database with a timestamp, and then having a file read
the table when the submit page loads, check how many leads have gone to
partnerx where time stamp BETWEEN xx/xx/xxxx AND xx/xx/xxxx.



Thanks for any tips!



Gary.

[David Morgan]

Yes, you're on the right track although I would just have a table with the
PartnerID, SentDate.

Then in the Partner table you can have a sequence next to each partner, (the
order to start from at midnight)

Check the count of referrals for today from the table above and order the
results by the sequence

Then after sending an email update the count in the first table.

Something like this should get you the next partner, but remember to insert
into tblReferrals after each email is sent.


SELECT TOP 1 tblPartners.PartnerName, tblPartners.PartnerEmail,
tblPartners.ReferralSequence
FROM tblPartners LEFT JOIN tblReferrals ON tblPartners.PartnerID =
tblReferrals.PartnerID
WHERE (((tblReferrals.SentDate)=Date()))
GROUP BY tblPartners.PartnerName, tblPartners.PartnerEmail,
tblPartners.ReferralSequence
HAVING (((Count(tblReferrals.ReferralID))<5))
ORDER BY tblPartners.ReferralSequence;




"Darren" <d@nospam.com> wrote in message
news:G_ZQb.9782$ax6.104705158@news-text.cableinet.net...

> Hello,
>
>
>
> I am trying to design an ASP system which will submit the contents of a
> feedback form to a partner, based on a daily maximum number of feedback
> forms per day. For example, if partner1 whishes to receive no more than 5
> feedback forms per day, then I want the system to check how many forms it
> has submitted to partner1 in previous 24 hours and then act accordingly.

If

> the number has reached the partner maximum, I want it to submit to the

next

> partner and so on. So, if I have 5 partners each taking 5 forms per day,

I

> want the system to automatically distribute 5 emails per partner based on

25

> feedback forms received in 24 hours.
>
>
>
> Any ideas? Here is how I see it working (not real code):
>
>
>
> DEFINE PARTNER RECIPIENT:
>
> [email]PARTNER1@DOMAIN.COM[/email]
>
> [email]PARTNER2@DOMAIN.COM[/email]
>
> [email]PARTNER3@DOMAIN.COM[/email]
>
>
>
> DEFINE NUMBER OF ENQUIRIES PER 24 HOURS:
>
> PARTNER1: 5 enquiries per 24hrs
>
> PARTNER2: 10 enquiries per 24hrs
>
> PARTNER3: 2 enquiries per 24hrs
>
>
>

> > USER SUBMIT FORM

>
>
>
> If Partner1 Number of Enquiries in last 24 hours <= 5 then submit to
> Partner1
>
> Else if Partner2 Number of Enquiries in last 24 hours <= 10

then

> submit to Partner2
>
> Else if Partner3 Number of Enquiries in last 24
> hours <= 2 then submit to Partner3
>
> ELSE
>
> Submit to overflow partner
>
>
>
> Any ideas or tips greatly appreciated. It's the counting of feedback

forms

> in last 24hrs which is confusing me! I am thinking about inserting each
> feedback form to a database with a timestamp, and then having a file read
> the table when the submit page loads, check how many leads have gone to
> partnerx where time stamp BETWEEN xx/xx/xxxx AND xx/xx/xxxx.
>
>
>
> Thanks for any tips!
>
>
>
> Gary.
>
>
>