Page 1 of 1
Using 'not less than' in a query to exclude multiple results
Posted: Fri Sep 10, 2010 5:16 pm
by mecha_godzilla
Hi All,
I won't post the code I've got because it's far too tedious to read through, but I'll explain the situation:
I'm trying to write a report that queries customer records to see when the customer was
first contacted, then provide a breakdown of whether this was within 5 working days, more than 5 working days but less than 20, or greater than 20. These breakdowns are relative to when the customer's record was first created.
The problem I'm having is that where some customers were contacted within 5 working days, but there was other contact later on in the month, my reports are picking up the same record and placing them in more than one category.
What I want is some way to represent the following query but in the correct working syntax (I'm using MySQL, BTW):
Code: Select all
SELECT DISTINCT client_id FROM communications
WHERE (
(communication_date > (client_record_added_date + INTERVAL 7 DAY))
AND
(communication_date < (client_record_added_date + INTERVAL 28 DAY))
BUT IS NOT
(communication_date < (client_record_added_date + INTERVAL 7 DAY)))
Alternatively, if there's another way to achieve this please let me know. I think the real problem might lie in how to exclude the record, because even if I get the query correct it will still pick up the client_id from one of the other communications, so maybe nesting the query would work?
Thanks,
Mecha Godzilla
Re: Using 'not less than' in a query to exclude multiple res
Posted: Fri Sep 10, 2010 6:41 pm
by John Cartwright
You basically had it down, just the syntax was wrong.
Code: Select all
SELECT DISTINCT client_id FROM communications
WHERE communication_date BETWEEN DATE_ADD(client_record_added_date, INTERVAL 7 DAY) AND DATE_ADD(client_record_added_date, INTERVAL 28 DAY)
AND communication_date < date_add(client_record_added_date, INTERVAL 7 DAY)
Let me know if that works for you. It's hard to determine whether that's what you want without sample data.
Re: Using 'not less than' in a query to exclude multiple res
Posted: Fri Sep 10, 2010 7:00 pm
by mecha_godzilla
Hi John,
Thanks for that.
I amended my query but it didn't return any results. Based on my original script this is what the output was, with the numbers relating to the customer's record number:
Within five days: 15
Within five days: 13
Within five days: 30
Within five days: 35
Between five and twenty days: 18
Between five and twenty days: 35
So customers #15 #13 #30 and #35 were all contacted within 5 working days, customer #18 was contacted between five and twenty working days but customer #35 was also contacted (again) five and twenty working days; therefore customer #35 should be omitted from the second category. I've gone through the records, checked all the communications and that all tallies-up correctly but obviously the query isn't doing what I need.
The 'brute-force' way of solving this (I think) would be to calculate all the 'within 5 working days' results, put the record numbers in a comma-separated list, then use this in the 'between five and twenty working days' query so that they get excluded. Does that make sense?
Thanks again,
M_G
Re: Using 'not less than' in a query to exclude multiple res
Posted: Sat Sep 11, 2010 3:28 am
by VladSun
I'm not sure how you get an ID more than once while using DISTINCT. Is this the *whole* query you are using actually?
I suppose your query selects not only the ID, but some other fields (like datas), which leads to more than one record returned per ID because the these records are DISTINCT indeed. Is that true?
Re: Using 'not less than' in a query to exclude multiple res
Posted: Sat Sep 11, 2010 3:13 pm
by mecha_godzilla
Hi,
I'm only retrieving the record id (and actually I don't need that, I just need to know how many results were returned) but here is the problem more specifically defined:
1. Customer #35 has three records in the
communications table (IE the customer was contacted on three occasions)
2. One of these records (a) has a date that is less than 5 working days after the customer was added to the system
3. Two of these records (b and c) have a date that is more than 5 working days but less than 20 working days after the customer was added to the system
At the moment the query matches records (b and c) but what I need it to do is
not match them if a record with an earlier date is present - which is the case with record (a). So, even though I have DISTINCT in the query, it's irrelevant in this instance because it still picks up the later records.
I'm beginning to suspect that I probably need to do a nested query or else put a clause in the query that makes sure specific records are excluded based on their ID (which means I'd have to create a list of the relevant IDs and insert this at runtime).
If anyone has any other suggestions it'd be great to hear them as I'm stumped on this one
M_G
Re: Using 'not less than' in a query to exclude multiple res
Posted: Sat Sep 11, 2010 3:28 pm
by VladSun
So, probably your SELECT clause should be:
Code: Select all
SELECT
COUNT(DISTINCT client_id)
...
DISTINCT does do the job you want to do by its definition...
Re: Using 'not less than' in a query to exclude multiple res
Posted: Sat Sep 11, 2010 3:53 pm
by mecha_godzilla
Famous last words, but I think I understand your point
My problem though is how to build something into the query that says:
"if one or more records matches the criteria then return the customer ID once only, but if a record has an earlier date than the matching criteria then
don't return the customer ID for this record or any others that share this customer ID even if they match the criteria"
This implies that the records can't be evaluated in isolation.
The problem with what I'm trying to do is that the query might have to exclude a result even if it's previously been matched because a result with an earlier date might not have been found yet - if that makes any sense of course
Thanks,
M_G
Re: Using 'not less than' in a query to exclude multiple res
Posted: Sat Sep 11, 2010 4:12 pm
by VladSun
Hm... I think I got it ... finally
Try using LEFT JOINS
Code: Select all
SELECT
communications.client_id
FROM
communications
LEFT JOIN
communications as excluded_communications ON
communications.client_id = excluded_communications.client_id
AND
(
[condition for 5 to 20 days range]
)
WHERE
[condition for 5 days range]
AND
excluded_communications.client_id IS NULL
or subqueries:
Code: Select all
SELECT
communications.client_id,
(
select
excluded_communications.client_id
from
communications as excluded_communications)
where
excluded_communications.client_id = communications.client_id
AND
(
[condition for 5 to 20 days range]
)
) as excluded_client_id
FROM
communications
WHERE
[condition for 5 days range]
AND
excluded_client_id IS NULL
or the way you want it (by using IN operator):
Code: Select all
SELECT
communications.client_id
FROM
communications
WHERE
[condition for 5 days range]
AND
client_id not in
(
select
excluded_communications.client_id
from
communications as excluded_communications
where
[condition for 5 to 20 days range]
)
Re: Using 'not less than' in a query to exclude multiple res
Posted: Sat Sep 11, 2010 4:46 pm
by mecha_godzilla
Thanks for that - I appreciate the effort
I'll try those out now and report back...accounting for the hundreds of typos that creep into my scripts, that'll probably be in a couple of hours
M_G
Re: Using 'not less than' in a query to exclude multiple res
Posted: Sat Sep 11, 2010 5:23 pm
by mecha_godzilla
Quick update:
The
NOT IN example that you gave seems to be the most likely candidate - what's happened is that the query returns all the matches that meet the first criteria (less than 5 days) and excluded the one that meets the second criteria (between 5 and 20 days) so I've got the exact opposite of what I want

The lazy hacker approach would be to just swap the numbers around of course, but I'm
much too professional to do that
EDIT: I switched the two WHERE conditions around (remembering to amend the table names) and that seems to have solved it.
The reason why I didn't post my code up before is that I've got two other tables that have to be JOINed to the main one (because the reports are broken down into type of customer and type of service) and there are already four separate WHERE conditions in my query, so it's possible that your first two examples might work as well but I'm too braindead tonight to get the code right.
In any case, I haven't used subqueries or NOT IN conditions before so I've got another way to rethink the problem.
Thanks again - I hope you haven't given up anything important tonight to be on these forums...
M_G