Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Group/Union Statement

Author  Topic 

cityinbetween
Starting Member

1 Post

Posted - 2014-09-22 : 16:06:46
Hello,

I am trying to use SQL to pull unique records from a large table. The table consists of people with in and out dates. Some people have duplicate entries with the same IN and OUT dates, others have duplicate IN dates but sometimes are missing an OUT date, and some don’t have an IN date but have an OUT date.

What I need to do is pull a report of all Unique Names with Unique IN and OUT dates (and not pull duplicate IN and OUT dates based on the Name).

I have tried 2 statements:
#1:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
GROUP BY tblTable1.Name, tblTable1.INDate
ORDER BY tblTable1.Name;

#2:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
UNION SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#));


Both of these work great… until I the OUT date. Once it starts to pull the outdate, it also pulls all those who have a duplicate IN date but the OUT date is missing.
Example:

Name IN OUT
John Smith 1/1/2014 1/2/2014
John Smith 1/1/2014 (blank)

I am very new to SQL and I am pretty sure I am missing something very simple… Is there a statement that can filter to ensure no duplicates appear on the query?

Thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-22 : 16:32:33
you didn't say what to do when someone has two non-equal in or out dates. that is what if you have:


Name IN OUT
John Smith 1/12/2014 1/2/2014
John Smith 1/12/2013 1/2/2013


However, one way to handle this is get the unique names then go back and get the dates -- perhaps the max or min dates:

Something like this:



select distinct tblTable1.Name, dates.INDate, dates.OUTDate
from tblTable1
...

join (select tblTable1.Name, max(tblTable1.INDate) INDate, max(tblTable1.OUTDate) OUTDate
from tblTable1
group by tblTable1.Name
) dates
On dates.Name = tblTable1.Name

where ...
Go to Top of Page
   

- Advertisement -