July 27, 2010 at 1:51 pm
I am pretty new at SQL, so bear with me if this is an easy/stupid question. This is what I have for my query so far in simplified terms.
SELECT Table1.Timeslip,
Table1.DocumentDate,
Table1.EmployeeNumber,
Table1.User_ID
FROM Table1
WHERE User_id='1076'
AND Timeslip LIKE '%99'
ORDER BY DocumentDate Desc
I want to pull records when two or more records have the same DocumentDate and the same EmployeeNumber (along with the criteria already in the query). I haven't been able to find any information on this. I don't know if its even possible. Any help is greatly appreciated.
Thanks
July 27, 2010 at 2:15 pm
Try
SELECT
Table1.DocumentDate,
Table1.EmployeeNumber,
count(*)
FROM Table1
WHERE User_id='1076'
AND Timeslip LIKE '%99'
Group by Table1.DocumentDate,
Table1.EmployeeNumber
Having count(*) > 1
or even
select a.* from
Table1 a,
(SELECT
Table1.DocumentDate,
Table1.EmployeeNumber,
count(*)
FROM Table1
WHERE User_id='1076'
AND Timeslip LIKE '%99'
Group by Table1.DocumentDate,
Table1.EmployeeNumber
Having count(*) > 1) b
where a.documentDate = b.documentDate
and a.EmployeeNumber = b.EmployeeNumber
Not 100% sure about the order by Having vs. group by but this should work.
July 27, 2010 at 2:16 pm
bandgeek5454 (7/27/2010)
I am pretty new at SQL, so bear with me if this is an easy/stupid question. This is what I have for my query so far in simplified terms.SELECT Table1.Timeslip,
Table1.DocumentDate,
Table1.EmployeeNumber,
Table1.User_ID
FROM Table1
WHERE User_id='1076'
AND Timeslip LIKE '%99'
ORDER BY DocumentDate Desc
I want to pull records when two or more records have the same DocumentDate and the same EmployeeNumber (along with the criteria already in the query). I haven't been able to find any information on this. I don't know if its even possible. Any help is greatly appreciated.
Thanks
Absolutely. SQL is a very powerful language.
The core of your SQL code would be:
SELECT
DocumentDate, EmployeeNumber
FROM Table1
GROUP BY DocumentDate, EmployeeNumber
HAVING COUNT(*) > 1
HAVING is like WHERE, but it is performed later, so you can use aggregated fields in it (COUNT, SUM, MAX, etc.).
You need more information that just those two fields, so you're going to have to use that as a subquery. You could do it this way:
SELECT
Table1.Timeslip,
Table1.DocumentDate,
Table1.EmployeeNumber,
Table1.User_ID
FROM Table1
WHERE DocumentDate IN (
SELECT
DocumentDate, EmployeeNumber
FROM Table1
GROUP BY DocumentDate, EmployeeNumber
HAVING COUNT(*) > 1)
AND EmployeeNumber IN (
SELECT
DocumentDate, EmployeeNumber
FROM Table1
GROUP BY DocumentDate, EmployeeNumber
HAVING COUNT(*) > 1)
But I don't like repeating the same subquery twice. Instead, you can use a common table expression:
;WITH cte AS (
SELECT
DocumentDate, EmployeeNumber
FROM Table1
GROUP BY DocumentDate, EmployeeNumber
HAVING COUNT(*) > 1
)
SELECT
Table1.Timeslip,
Table1.DocumentDate,
Table1.EmployeeNumber,
Table1.User_ID
FROM Table1
WHERE DocumentDate IN
(SELECT DocumentDate FROM cte)
AND EmployeeNumber IN
(SELECT EmployeeNumber FROM cte)
July 27, 2010 at 2:20 pm
Henry's code is better. I didn't even think to use a join for some reason. Use his second query.
--J
July 27, 2010 at 2:22 pm
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply