Duplicates / Unique questions - 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

Duplicates / Unique questions

Author

Topic

alexjungle
Starting Member

8 Posts

Posted - 2006-09-08 : 13:10:41

Hi - new to SQL here and have a few questions regarding duplicate records.

Here is my table with the following columns:

HotelName
EmployeeName
EmployeeNumber
FirstName
LastName
Address
City
Province
PostalCode
Country
Telephone
CompanyName
Email
MembershipNumber
DateTimeEntered
EveningPhone
LanguagePref
DateTimeUpdated

Some of the answers I'm looking for:

1)Total Number of Entries made by each employee per month (either by name or employee #)

2)Total Number of Entries made by each hotel per month

3)Number of Entries with unique name (no repeats) (firstname and lastname)

4)Who Repeat Entries are (ability to isolate those who have stayed for example…4 times)

Thanks for the help! I've been using query builder but with some many columns, it's confusing trying to find the right answers.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-08 : 13:14:54

Please post what you have so far for each as this looks like homework. We are willing to help with homework, but the student must at least show that he/she has tried to come up with a solution.

Tara Kizer

alexjungle
Starting Member

8 Posts

Posted - 2006-09-08 : 13:19:44

No homework assignment, I work for a hotel management company.

I wrote this in Query Analyzer:

select employeename, employeenumber, lastname, firstname, hotelname, count(*)
from travellers
group by employeename, employeenumber, lastname, firstname, hotelname
having count(*) > 1
order by count(*) desc, employeename, employeenumber, lastname, firstname;

but that didn't return all records. and then this is from Query Builder:

SELECT COUNT(*) AS Expr1, COUNT(*) AS Expr2, HotelName, EmployeeName, FirstName, LastName, DateTimeEntered
FROM dbo.travellers
GROUP BY HotelName, EmployeeName, FirstName, LastName, DateTimeEntered

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-08 : 13:23:35

Please post the DDL (CREATE TABLE) for your table, INSERT INTO statements for sample data, and the expected result set for each of the 4 items using this sample data. Sample data should be about 10 rows and it should represent your problem. Sample data does not have to be real data and the DDL does not even have to be your exact table layout, just make sure that whatever you post represents your problem. Here's an example of a thread of mine where I am asking for help and provide this type of information:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42516

Notice how quickly I got an answer.

Tara Kizer

alexjungle
Starting Member

8 Posts

Posted - 2006-09-08 : 14:10:19

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[travellers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[travellers]
GO

CREATE TABLE [dbo].[travellers] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[HotelName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Province] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MembershipNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateTimeEntered] [datetime] NULL ,
[EveningPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LanguagePref] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateTimeUpdated] [datetime] NULL
) ON [PRIMARY]
GO

I don't understand this though - I think I need to learn more. Thanks anwyays

alexjungle
Starting Member

8 Posts

Posted - 2006-09-08 : 19:40:11

sample data: - there's more columns of course but they won't fit here - I need to know unique first/lastnames

HotelName EmployeeName FirstName LastName
hotel 1 Aimee Don Martin
hotel 1 Aimee Joseph Marshall
hotel 1 Aimee Stacey NIckerson
hotel 1 Aimee Nathan Landry
hotel 1 Aimee Michael Pace
hotel 1 Aimee Lynn MacDonald
hotel 1 Aimee Trevor Bernard
hotel 1 Aimee Trevor Bernard
hotel 1 Aimee Andrea Paul

Kristen
Test

22859 Posts

Posted - 2006-09-09 : 04:29:38

Hi alexjungle, Welcome to SQL Team!


CREATE TABLE #travellers
(
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[HotelName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EmployeeName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EmployeeNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Province] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PostalCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Country] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Telephone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CompanyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MembershipNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DateTimeEntered] [datetime] NULL ,
	[EveningPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LanguagePref] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DateTimeUpdated] [datetime] NULL 
)

INSERT INTO #travellers
(
	HotelName, EmployeeName, FirstName, LastName
--	Address, City, Province, PostalCode, Country, Telephone, CompanyName, Email, 
--	MembershipNumber, DateTimeEntered, EveningPhone, LanguagePref, DateTimeUpdated
)
	SELECT	'hotel 1', 'Aimee', 'Don', 'Martin'
UNION ALL SELECT 'hotel 1', 'Aimee', 'Joseph', 'Marshall'
UNION ALL SELECT 'hotel 1', 'Aimee', 'Stacey', 'NIckerson'
UNION ALL SELECT 'hotel 1', 'Aimee', 'Nathan', 'Landry'
UNION ALL SELECT 'hotel 1', 'Aimee', 'Michael', 'Pace'
UNION ALL SELECT 'hotel 1', 'Aimee', 'Lynn', 'MacDonald'
UNION ALL SELECT 'hotel 1', 'Aimee', 'Trevor', 'Bernard'
UNION ALL SELECT 'hotel 1', 'Aimee', 'Trevor', 'Bernard'
UNION ALL SELECT 'hotel 1', 'Aimee', 'Andrea', 'Paul'

"Total Number of Entries made by each employee per month (either by name or employee #)"


SELECT	EmployeeName, 
	[Month] = DATEADD(Month, DATEDIFF(Month, 0, DateTimeEntered), 0),
	[Entries] = COUNT(*)
FROM	#travellers
GROUP BY EmployeeName, 
	DATEADD(Month, DATEDIFF(Month, 0, DateTimeEntered), 0)

Can't test, you didn't provide any DateTimeEntered data

"Total Number of Entries made by each hotel per month"

Same, change EmployeeName to HotelName

"Number of Entries with unique name (no repeats) (firstname and lastname)"
"Who Repeat Entries are (ability to isolate those who have stayed for example…4 times)"

I don't understand the first question, and it probably needs to be satisfied by the second question, which is probably:


SELECT	LastName,
	FirstName,
	[Entries] = COUNT(*)
FROM	#travellers
GROUP BY LastName,
	FirstName

You'll see I've struggled a bit. In order to answer these type of questions we need the Table DDL, some data, and an example of the correct output - which is why Tara asked for it. I expect you will now come back and explain some of that, and I, or someone else, will need to re-work what I did - which is a fair bit to ask for volunteer work!

Observations:

Your table should have a Primary Key - quite possibly on the ID column
You should consider using appropriate sized columns, rather than having everything at [nvarchar] (50) - do you have 50 character telephone numbers?
Is EmployeeNumber and MembershipNumber really VARCHAR, rather than INT?
Can an address really be squeezed into 50 characters? Mine [up to the City] is 40 characters, and not that exception IMO
PostalCode would probably only need 10 characters
EMail will need considerably more than 100 characters
LanguagePref should probably be a short code for which the language is looked-up in an associated table

Also consider whether you need to use Nvarchar, or whether varchar would be sufficient (as varchar is usually more efficient in a number of ways)

Also consider if any of the columns should be compulsory, and make them NOT NULL - currently you only require an ID which seems pretty skinny to me!

You probably should have some indexes on this table - depending on what you will query it by - probably including DateTimeEntered, EmployeeName or EmployeeNumber, LastName + FirstName

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-09 : 08:12:16

Also Learn SQL

http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

alexjungle
Starting Member

8 Posts

Posted - 2006-09-11 : 12:05:20

Thanks for your help - I'll have a look and get back to you with some more data.

alexjungle
Starting Member

8 Posts

Posted - 2006-09-11 : 13:19:44

This one works well:

"Total Number of Entries made by each employee per month (either by name or employee #)"

SELECT HotelName,
[Month] = DATEADD(Month, DATEDIFF(Month, 0, DateTimeEntered), 0),
[Entries] = COUNT(*)
FROM #travellers
GROUP BY HotelName,
DATEADD(Month, DATEDIFF(Month, 0, DateTimeEntered), 0)

Is there a way to break that up into distinct entries based on first/last name?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-11 : 13:58:51

Read up on GROUP BY and practice writing some SELECT's. The whole key to summarizing data and/or returning distinct results for certain columns is the GROUP BY clause. the best way to learn is to experiment and try different things yourself.

- Jeff

alexjungle
Starting Member

8 Posts

Posted - 2006-09-20 : 19:42:49

Hi again

is there a way for this query to return the values I need: I want to see the count of how many first/last name entries were made by each employee. Example is if Employee A submitted John Smith 3 times over the course of the year, I want to know how many times John appears by that employee and when they were submitted.

SELECT COUNT(*) AS Expr1, HotelName, EmployeeName, FirstName, LastName, DateTimeEntered
FROM dbo.travellers
GROUP BY HotelName, EmployeeName, FirstName, LastName, DateTimeEntered

sample data from the above query:

Expr1 Hotelname Employeename, FirstName, LastName, DateTimeEntered
1 HotelA EmployeeA Jim Busby 2005-04-22
1 HotelA EmployeeA Tim Coleman 2005-04-22
1 HotelA EmployeeB Wallace Birtch 2005-04-22
1 HotelA EmployeeB Eldon Wilson 2004-11-05
2 HotelA EmployeeB Nevin Hanning 2004-11-05
1 HotelA EmployeeC Steve Allan 2004-07-26

You can see there are 2 entries from Employee B for Nevin Hanning but that doesn't do quite what I need. Any help?

Kristen
Test

22859 Posts

Posted - 2006-09-21 : 10:54:19

"You can see there are 2 entries from Employee B for Nevin Hanning but that doesn't do quite what I need."

I see only one entry, thus I don't understand what you need as output!

Kristen

alexjungle
Starting Member

8 Posts

Posted - 2006-09-21 : 11:59:23

you can see in the 'Expr1' column that there are 2 counts of HotelA EmployeeB Nevin Hanning 2004-11-05 - but if the dates were different for example, the count (expr1) would only show '1'. Sorry if it's confusing, I'm confusing myself too!

Kristen
Test

22859 Posts

Posted - 2006-09-21 : 14:17:49

Ah, if they have different dates they will show up twice ... Got it!

Depends which Date Entered you need, but assuming the earliest you could do:


SELECT COUNT(*) AS Expr1, HotelName, EmployeeName, FirstName, LastName, MIN(DateTimeEntered)
FROM dbo.travellers
GROUP BY HotelName, EmployeeName, FirstName, LastName, DateTimeEntered

You could have the MAX(DateTimeEntered) instead - or even the Average if that would be of any use!!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources