Hello all,

i was wondering if you can help me with a problem i'm having with a MS SQL query.

The application holds customers, each customer has a balance, once their balance gets below $10 the customer is sent an email saying their balance is low, this email is then recorded into the emailLogs database table.

I'm trying to put together a report that lists all of the customers in the database that have been emailed a reminder email about their balance being low, and then showing their current balance. However the problem with my query is that if a customer has used their balance more than once during the date period then they will be listed that many times, as their is an email record for each time.

I need them to only be listed once in my table.

Here are the tables:

Users:
ID
CompanyName
Balance
CurrencyTypeID

EmailLog:
ID
Subject
Message
DateRecordCreated

And here is my query:

SELECT Users.ID as UserID, Users.CompanyName as CompanyName, convert(char(11), EmailLog.DateRecordCreated, 106) as DateRecordCreated, EmailLog.Message as EmailMessage, Users.Balance as Balance, Users.CurrencyTypeID as CurrencyTypeID
FROM EmailLog
LEFT OUTER JOIN Users ON Users.ID = EmailLog.UserID
WHERE Users.CurrencyTypeID=1 AND EmailLog.DateRecordCreated between convert(dateTime, '2006-09-1 00:00:00', 110) and convert(dateTime, '2006-09-29 23:59:59', 110) AND EmailLog.Subject LIKE '%Your account balance is low%'
ORDER BY EmailLog.DateRecordCreated DESC

How do i stop a customer being shown more than once?

Thanks in advance.

Hi,

you should group your result by customer's ID using the 'GROUP BY' expression.

G

Hello, thanks for the reply!

I did try grouping, however if i group it by the ID then it errors because i'm trying to pull through the other fields:

Users.CompanyName as CompanyName, convert(char(11), EmailLog.DateRecordCreated, 106) as DateRecordCreated, EmailLog.Message as EmailMessage, Users.Balance as Balance, Users.CurrencyTypeID as CurrencyTypeID

and i NEED these fields to be returned for my report.

The error i get when grouping is similar to this:

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'Users.CompanyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Anybody else know how to get around this, i'm still stuck on this one! :(

After going back through looking at my tables i believe i've explained the tables wrong, so many apologies for this (how silly).

let me try again:

I have three tables in my database not two and they are:

EmailLog, Contacts and Users

EmailLog contains these fields

ID
Subject
Message
DateRecordCreated

Contacts table contains these fields:

ID
ContactEmail
UserID

The Users table contains the following fields:

ID
CompanyName
Balance
CurrencyTypeID

What i need the query to do is get all the records from the EmailLog table that have a DateRecordCreated date between two given dates, but if an email has been sent to a contact more than once in this time period i only want to list the latest record. The reason for this is that a customer's balance might have dropped below the cutoff 5 times in a month, and therefore will have been emailed 5 times, however i don't need to know about all 5 times, i just need to know about the latest one.

Hope that makes a little more sense? :S

It would of course be easier if the EmailLog table contained the User ID but this it not a change i can make now as there would be a lot of code to change, instead it shows a Contact ID, and in turn the contacts table does have the User ID in it (users can have multiple contact emails).