Group/Union Statement - SQL Server Forums

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 ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources