Select Distinct Query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Select Distinct Query

Author

Topic

Tessie
Starting Member

10 Posts

Posted - 2006-11-20 : 17:31:25

Hello,
I've reviewed a number of other posts, but none have helped me so far. Any suggestions will be appreciated.

There are two tables with an ID column to link them. Table A has unique entries, whereas Table B has multiple entries for each record in Table A.
Table A has TableA.IntakeID, LastName, FirstName
Table B has TableB.ID, TableA.IntakeID, Service, ContactDate, Reported

I need to be able to join these such that one record is returned:
LastName, FirstName from Table A
with
the first record from Table B that matches TableA.ID and where the
ContactDate falls between <beginDate> and <endDate> and where the Service has not already been reported. (It would be okay to return the last record from Table B matching that criteria, too.) I use a subquery to eliminate a service that has been counted in the previous period because a service within a given period may only be counted once. A report count is run once a quarter.

A temp table is created to hold the intermediate results, but only because I haven't been able to figure out how to do it with one scope of work. Here is what I have so far:

Create Table #CurrentServices
(IntakeID varchar(30),
Service varchar(50),
ContactDate varchar(8))
(nb: the IntakeID is a unique string, not an Identity column)

Set @Service = 'Service A'
Insert into #CurrentServices
(
IntakeID, Service, ContactDate
)
Select
Distinct TableB.IntakeID,
@Service,
ContactDate
From
TableB
Inner Join
Table A
On TableA.IntakeID = TableB.IntakeID
Where
TableB.ContactDate Between @startDate and @endDate
and
TableB.Reported = 0
and
TableB.IntakeID not in
(select TableB.IntakeID
from TableB
where
TableB.ContactDate
Between @previousPeriodStart and @previousPeriodEnd
and
TableB.Reported = 1)

This works fine if I leave the ContactDate off the query. As soon as I try to fetch that column too, I undo the Distinct.

Is there another way I can fetch just one matching row from Table B?

Thanks for any advice.
Tessie

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-20 : 18:06:58

I think this is what you want?


select A.FirstName, A.LastName, B.Service, B.ContactDate
from TableA A
inner join (select IntakeID, Service, min(ContactDate) as ContactDate
	from TableB
	where ContactDate between '1/1/2006' and '5/1/2006' and Reported = 0
	group by IntakeID, Service) B on B.IntakeID = A.IntakeID

Tessie
Starting Member

10 Posts

Posted - 2006-11-21 : 10:35:40

Thank you for the suggestion. It works!
Cheers,
Tessie

Tessie
Starting Member

10 Posts

Posted - 2006-11-22 : 14:30:43

Okay.
Now I need to be able to get TableB.ID.
This is always unique, so the Min (or Max) aggregate on the ContactDate no longer works--it returns more rows than needed.
For example,


Select   
	Max (Contact.ContactDate) as ContactDate,
	TableB.ID as ContactID
Into
	#TempContacts
From
	TableB
Inner Join
	Intake
On
	 Intake.ID = TableB.IntakeID 
Where
	<report period criteria> and
	TableB.ID not in
		(
		Select TableB.ID
                 from TableB
                 where
		 TableB.Reporting > 0 
		) 
Group By TableB.ID

Does anyone have any suggestions on how to query a distinct value (the Max contact date) while fetching but ignoring the distinct value of TableB.ID?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 14:41:24

[code]SELECT a.LastName,
a.FirstName,
b.Service,
b.ContactDate,
b.Reported
FROM TableA a
INNER JOIN TableB b ON b.IntakeID = a.IntakeID
INNER JOIN (
SELECT IntakeID,
MIN(IntakeID) miid
FROM TableB
WHERE ContactDate BETWEEN @BeginDate AND @EndDate
AND Reported = 0
GROUP BY IntakeID
) q ON q.IntakeID = b.IntakeID[/code]

Peter Larsson
Helsingborg, Sweden

Tessie
Starting Member

10 Posts

Posted - 2006-11-27 : 11:48:00

Thank you. I will try this.

Tessie
Starting Member

10 Posts

Posted - 2006-11-27 : 17:18:25

Thank you for your patience and assistance. It's greatly appreciated.
There is still a problem, so I hope I can engage your help once again.

The problem occurs when I try to return another column, either in the outer
or inner query.

Rather than trying to abbreviate, I'll explain the situation a little
more fully.
There are two tables: Intake and Contact.
Intake has some information like Program ID, while Contact has detailed
information for each Intake record (one to many relationship).

The query should return a single matching instance of contact for
each Intake where contact is a unique type of contact.
It should ignore multiple records of the same type of contact
for the same Intake within the report period.
The Contact table has over 30 contact types.

The following query works fine but only returns the IntakeID and
Max(ContactDate). If I add another column (AdvocateID),
then duplicate IntakeIDs are returned for the same service
(InternalService in this example). The data is such that Contact.ID
is unique, ContactDate is not unique, IntakeID is not unique, and AdvocateID
is not unique in the Contact table.
In the Intake table, Intake.ID is unique.

I tried moving the more restrictive queries to yet another
inner query, but without success. As soon as the query is parsed
at the next higher level, the duplicate IntakeIDs are introduced
again.

Ideally, the query should return:
Max (Contact.ContactDate)
Contact.IntakeID
Contact.AdvocateID

(with only one result row for each IntakeID)

I will simply repeat the query for each of the service types.

If you have any further advice or assistance you can give me,
I'd greatly appreciate it.
Many thanks. Here's the query so far:


Select		
		Max (Contact.ContactDate) as ContactDate
		, Contact.IntakeID
From
		Contact
Inner Join
(
		Select 
			Contact.IntakeID
			, Contact.AdvocateID
		From
			Contact
		Inner Join
			Intake
		On
			Intake.ID = Contact.IntakeID 
		Where
		Intake.ProgramID = @programID and
		Contact.ContactDate Between @startDate and @endDate and
		Contact.DoNotReport = 0 and
		Len(Contact.ReportID) = 0 and
		Contact.InternalService > 0 and
		Contact.IntakeID not in -- Check to ensure not in previous period report:
			(
			Select   
				Contact.IntakeID as IntakeID
			From 
				Contact 
			Inner Join Intake 
			On
				Intake.ID = Contact.IntakeID 
			Where
							Intake.ProgramID = @programID and
							Contact.ContactDate Between @prevStart and @prevEnd and
							Contact.DoNotReport = 0 and
							(Contact.ReportID = @reportID or Len(Contact.ReportID) = 0) and
							Contact.InternalService > 0 
						) 
	) q
	On
		q.IntakeID = Contact.IntakeID

	Group By Contact.IntakeID
	Order by Contact.IntakeID, Contact.ContactDate

Tessie

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-27 : 18:32:33

I was reading your textual explanation and looking at the code, it left me with this question;

What is the uniqueness constraint for the resultset?

(one row per:)
IntakeID, ContactDate, AdvocateID
or (one row per:)
IntakeID, ContactDate, InternalService

rockmoose

Tessie
Starting Member

10 Posts

Posted - 2006-11-27 : 19:01:52

Hello,

The constant is InternalService, but it is not unique.
The closest thing to the unique constraint is to find the Max occurrence of ContactDate for an IntakeID, AdvocateID where the Service = (some service).

I've been looking at it more, and I'm going to try the following:


Select 
	Max (c.ContactDate) as ContactDate
	, c.IntakeID
	, Max (c.AdvocateID) as AdvocateID

From	Contact c
Inner Join Intake i
on c.IntakeID = i.ID
Where
   (remaining report criteria)

If you have any suggestions, please let me know.
Cheers,
Tessie

Tessie
Starting Member

10 Posts

Posted - 2006-11-28 : 12:19:42

The two aggregate functions work. I had tried a combination of distinct with max, but not both max before.
Thanks to everyone for your help!

Tessie

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-28 : 17:14:01

Yes, but in:

Max (c.ContactDate) as ContactDate
, c.IntakeID
, Max (c.AdvocateID) as AdvocateID

The ContactDate and AdvocateID might not match up (come from the same row in the Contact table).

This is what I mean:

select	IntakeID
	,max(ContactDate) as ContactDate
	,max(AdvocateID) as AdvocateID
from
	(
		select 1 as IntakeID, '20060101' as ContactDate, 3 as AdvocateID
		union all
		select 1 as IntakeID, '20060303' as ContactDate, 1 as AdvocateID
	) c
group by
	IntakeID


IntakeID    ContactDate AdvocateID  
----------- ----------- ----------- 
1           20060303    3

(1 row(s) affected)

Tessie
Starting Member

10 Posts

Posted - 2006-11-29 : 00:04:36

Yes, you are correct. After a little testing, I came across that problem.

I may have to break the query up and aggregate the results rather than trying to fetch all the information in one go.

If you have any suggestions, please let me know. And thanks for pointing out the problem with using two Max's in one statement.

Tessie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 01:38:31

It is better you provide us some sample data, your expected output from that supplied sample data and probably some table DDL too.

Peter Larsson
Helsingborg, Sweden

Tessie
Starting Member

10 Posts

Posted - 2006-11-29 : 12:01:39

Thank you for your help.
Here are the definitions and sample data for Intake and Contact. I truncated the Contact table somewhat because it's not normalized and has many service areas. I included only three service areas to simplify things.


CREATE TABLE [dbo].[INTAKE](
	[ID] [char](23) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[IntakeDate] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[GrantAssignmentID] [char](23) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[IntakeLastName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[IntakeFirstName] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[IntakeMiddleName] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_INTAKE] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[CONTACT](
	[ID] [char](23) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[IntakeID] [char](23) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ReportID] [char](23) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AdvocateID] [char](23) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ContactDate] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ContactType] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DoNotReport] [smallint] NULL,
	[InternalService] [smallint] NULL,
	[Referral] [smallint] NULL,
	[Notification] [smallint] NULL
 CONSTRAINT [PK_CONTACT] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

In addition to Intake and Contact, there's an Advocate table which ties into Contact via AdvocateID (Advocate.ID). It looks like this:


CREATE TABLE [dbo].[ADVOCATE](
	[ID] [char](23) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LastName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FirstName] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[MiddleName] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_ADVOCATE] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Sample data for Intake:



Intake.ID
20010604.14:16:52.23671
20010604.14:28:37.92826
20010604.14:32:22.17208
20010604.15:06:48.16446
20010604.15:08:20.27082
20010604.15:09:36.94076
20010604.15:23:31.24208
20010604.15:29:30.24202
20010604.16:22:13.26328
20010604.16:31:03.25688

Intake.ADVOCATEID
20010604.10:46:04.26087
20010604.10:52:12.29561
20010604.10:48:22.26084
20010604.10:52:12.29561
20010604.10:47:09.26962
20010604.10:50:03.26963
20010604.10:50:03.26963
20010604.10:47:09.26962
20010604.10:53:49.30128
20010604.10:53:49.30128

Intake.ReportID
20020103.10:15:12.13889
20020103.10:15:12.13889

Intake.GrantAssignmentID
20010604.10:36:46.17166
20010604.10:36:46.17166
20010604.10:36:46.17166
20010604.10:36:46.17166
20010604.10:36:46.17166
20010604.10:36:46.17166
20010604.10:36:46.17166
20010604.10:36:46.17166
20010604.10:34:54.19646
20010604.10:34:54.19646

Intake.DoNotReport
0
0
0
0
0
0
0
0
0
0

Intake.IntakeDate
20060901
20060901
20060901
20060910
20060910
20060915
20060920
20060930
20061015
20061015

Intake.LastName
LastName1
LastName2
LastName3
LastName4
LastName5
LastName6
LastName7
LastName8
LastName9
LastName10

Intake.FirstName
Kate
John
Paul
Pierre
Rupert
Nancy
Olga
Mia
Tracy
Tasha

Sample data for Contact:


Contact.ID
20010604.14:19:33.92885
20010604.14:30:46.29760
20010604.14:34:30.26642
20010604.15:10:42.16448
20010604.15:12:44.24200
20010604.15:12:56.94085
20010604.15:23:53.28446
20010604.15:25:46.18969
20010604.15:36:08.22525
20010604.16:06:36.27528

Contact.IntakeID
20010604.14:16:52.23671
20010604.14:28:37.92826
20010604.14:32:22.17208
20010604.15:06:48.16446
20010604.15:08:20.27082
20010604.15:09:36.94076
20010604.15:23:31.24208
20010604.15:23:31.24208
20010604.15:29:30.24202
20010604.15:29:30.24202

Contact.AdvocateID
20010604.10:46:04.26087
20010604.10:52:12.29561
20010604.10:48:22.26084
20010604.10:52:12.29561
20010604.10:47:09.26962
20010604.10:50:03.26963
20010604.10:50:03.26963
20010604.10:50:03.26963
20010604.10:47:09.26962
20010604.10:47:09.26962

Contact.ContactDate
20060901
20060901
20060901
20060910
20060910
20060915
20060920
20060930
20061015
20061015

Contact.DoNotReport
0
0
0
0
0
0
0
0
0
0

Contact.InternalService
1
0
1
1
0
1
1
1
1
0

Contact.Referral
0
0
0
0
1
0
0
0
1
0

Contact.Notification
1
1
1
0
1
0
0
0
1
0

The final result should look like:

(Advocate)FirstName LastName
ContactDate
IntakeLastName, IntakeFirstName
(Service)

Where ContactDate is either the first or last Contact.ContactDate that falls within the report period. It's not really important which, just as long as it fits in the period reported.
And Service is simply the name of the service rendered (e.g., "Internal Service" or "Referral"). For Service, I use a local variable to hold the string equivalent of the service being reported (@Service which in this example is set to "Internal Service").

The query with the two Max functions:


Insert into
	 #tblContact (
	  ContactDate
	, IntakeID
	, AdvocateID
	, Service
	)
	Select		
			Max (Contact.ContactDate) as ContactDate
			, Contact.IntakeID
			, Max (Contact.AdvocateID) as AdvocateID
			, @Service
	From
					Contact
	Inner Join
	(
					Select 
						Contact.IntakeID
						, Contact.AdvocateID
					From
						Contact
					Inner Join
						Intake
					On
						Intake.ID = Contact.IntakeID 
					Where
							Intake.GrantAssignmentID = @sGrantID and
							Contact.ContactDate Between @sDateFrom and @sDateTo and
							Contact.DoNotReport = 0 and
							Len(Contact.ReportID) = 0 and
							Contact.InternalService > 0 and
							Contact.IntakeID not in
								(
								Select   
									Contact.IntakeID as IntakeID
									From 
										Contact 
									Inner Join Intake 
									On
										 Intake.ID = Contact.IntakeID 
									Where
										Intake.GrantAssignmentID = @sGrantID and
										Contact.ContactDate Between @sPreviousPeriodsStart and @sPreviousPeriodsEnd and
										Contact.DoNotReport = 0 and
										(Contact.ReportID = @sReportID or Len(Contact.ReportID) = 0) and
										Contact.InternalService > 0 
									) 
				) q
				On
					q.IntakeID = Contact.IntakeID

				Group By Contact.IntakeID
				Order by Contact.IntakeID, Contact.ContactDate

Thanks for any suggestions, direction,
Tessie

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-29 : 17:48:51

Tessie,

The sample data needs to be in the form:

insert tableName(...) values(...)
insert tableName(...) values(...)
insert tableName(...) values(...)

or in some form that is easily digestible by us.

if we can just cut&paste the CREATE TABLE statements and the INSERT statements into Query Analyzer
I am sure you soon will have a good response.

I really do appreciate all the effort that you put into this thread for explaining your problem and providing sample data.
It is so much better than most posters

Ideally, if we would like a script with create table + insert statements that sets up a test case.
And a description of the desired result.
With those 2 it will be much easier for us to provide an example of a working sql-statement.

regards,
rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 18:18:48

[code]SELECT a.LastName [Intake last name],
a.FirstName [Intake first name],
b.Service,
b.ContactDate,
b.Reported,
c.LastName [Advocate last name],
c.FirstName [Advocate first name]
FROM dbo.Intake a
INNER JOIN (
SELECT IntakeID,
MIN(ID) miid
FROM dbo.Contact
WHERE ContactDate BETWEEN @BeginDate AND @EndDate
AND Reported = 0
GROUP BY IntakeID
) q ON q.IntakeID = a.IntakeID
INNER JOIN dbo.Contact b ON b.ID = q.miid
INNER JOIN dbo.Advocate c ON c.ID = b.AdvocateID[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 18:45:33

If you want to, exchange MIN with MAX to get newest/lastest matching record instead of oldest/earliest matching record.

Peter Larsson
Helsingborg, Sweden

Tessie
Starting Member

10 Posts

Posted - 2006-11-30 : 10:55:15

Many thanks to Peso and Rockmoose!
Peso, the query you presented works beautifully!

Best wishes to you both, and all the contributors,
for the holidays and new year :)

Tessie

ps: Next time I post, I will follow your instructions, Rockmoose.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources