Help writing a query.

[Gary]

Hello,

I am trying to write a query and getting a little confused.

My problem:
I send enquiries to partners from 12:00am to 11:59pm each day. I have ten
partners, and each partner has a different daily limit. In my partner table
(Table1), I have a columnm for the Daily Enquiry Limit called "DailyCap". I
also have a second table (Table2) which counts how many enquiries a partner
has had and assigns a date stamp.So, my query needs to:

Select PARTNER
where Table2.PartnerCount is less than Table1.DailyCap
AND where Table2.TimeStamp between 12:00am TODAY and 11:59pm TODAY.

This should hopefully then select any partner who hasent yet reached the
daily cap between midnight start and 24 hours later.

Any odeas how to write this query using proper SQL? I have tried but
failed.

Regards,

Gary.

[David Portas]

I'll assume your tables look like this:

CREATE TABLE Partners (partner_no INTEGER PRIMARY KEY, partner_name
VARCHAR(20) NOT NULL UNIQUE, dailycap INTEGER NOT NULL CHECK (dailycap>=0))

CREATE TABLE PartnerEnquiries (partner_no INTEGER REFERENCES Partners
(partner_no), enquiry_dt DATETIME, PRIMARY KEY (partner_no, enquiry_dt))

Here's the query:

SELECT P.partner_name, MAX(enquiry_dt)
FROM Partners AS P
LEFT JOIN PartnerEnquiries AS E
ON P.partner_no = E.partner_no
AND E.enquiry_dt >=CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND E.enquiry_dt <DATEADD(DAY,1,CONVERT(CHAR(8),CURRENT_TIMESTAMP,1 12))
GROUP BY P.partner_no, P.partner_name, P.dailycap
HAVING COUNT(E.partner_no) < P.dailycap

--
David Portas
SQL Server MVP
--

[Gary]

Hi David,

Thanks for the time you have taken to reply. I am fairly new to SQL, could
you or perhaps another NG user comment on the code you have provided and let
me know what all the elements are and how they work? It looks quite
complex!!!

Thanks,

Gary.
"David Portas" <RE****************************@acm.org> wrote in message
news:uq********************@giganews.com...

I'll assume your tables look like this:

CREATE TABLE Partners (partner_no INTEGER PRIMARY KEY, partner_name
VARCHAR(20) NOT NULL UNIQUE, dailycap INTEGER NOT NULL CHECK (dailycap>=0))
CREATE TABLE PartnerEnquiries (partner_no INTEGER REFERENCES Partners
(partner_no), enquiry_dt DATETIME, PRIMARY KEY (partner_no, enquiry_dt))

Here's the query:

SELECT P.partner_name, MAX(enquiry_dt)
FROM Partners AS P
LEFT JOIN PartnerEnquiries AS E
ON P.partner_no = E.partner_no
AND E.enquiry_dt >=CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND E.enquiry_dt <DATEADD(DAY,1,CONVERT(CHAR(8),CURRENT_TIMESTAMP,1 12)) GROUP BY P.partner_no, P.partner_name, P.dailycap
HAVING COUNT(E.partner_no) < P.dailycap

--
David Portas
SQL Server MVP
--

[David Portas]

Here it is again with comments. You can refer to Books Online for the
meaning of particular keywords.

SELECT P.partner_name, MAX(enquiry_dt) /* latest datetime */
FROM Partners AS P
/* Left join because maybe not every partner has an enquiry: */
LEFT JOIN PartnerEnquiries AS E
ON P.partner_no = E.partner_no
/* Include rows only where Enquiry_dt is today: */
AND E.enquiry_dt >=CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND E.enquiry_dt <DATEADD(DAY,1,CONVERT(CHAR(8),CURRENT_TIMESTAMP,1 12))
GROUP BY P.partner_no, P.partner_name, P.dailycap
/* where the row count from the enquires table is < dailycap: */
HAVING COUNT(E.partner_no) < P.dailycap

I have assumed that you have a row in PartnerEnquiries for each enquiry and
that you want to count those rows and compare to dailycap. That part wasn't
entirely clear to me from your original post.

--
David Portas
SQL Server MVP
--