Returning duplicates - 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

Returning duplicates

Author

Topic

adamstagnaro
Starting Member

4 Posts

Posted - 2011-07-14 : 14:50:26

Hi all,

New to the forum and a newbie at SQL... haven't been able to find anything existing to answer my question.

I'd like to write a query that returns all entries with the following criteria:
1. BadPart = 1
2. TimeStamp is today

OR if

1. Timestamp is today
2. if a multiple of column "PartNumber" exists, return all multiples from today only

I've been trying to add another column that is 0 if only one entry from today exists, or 1 if multiple entries are present. Haven't had any luck, and have no idea if this is the right approach anyway. Any help is appreciated.

Thanks,

Adam S

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-14 : 14:57:13

I'm not sure of how your tables look. As I see I have written the code for a general query.

If you can provide the Table with the columns that might helps us more.

Select * From Your Table
Where
(Badpart = 1
AND
TimeColumn = GETDATE())
OR
(TimeColumn = GETDATE()
AND
PartColumn LIKE '%partnumber%')

Hope this Helps!
Dasman

==========================
Pain is Weakness Leaving the Body.

adamstagnaro
Starting Member

4 Posts

Posted - 2011-07-14 : 15:14:20

Wow, thanks for the fast response. I haven't been able to get your recommendation to work as I'd like.

The table looks similar to this:

ID Timestamp BadPart PartNumber
1 7/14/2011 0 101
2 7/14/2011 1 102*
3 7/14/2011 0 103
4 7/14/2011 0 104
5 7/14/2011 1 105*
6 7/14/2011 0 102*
7 7/14/2011 0 106
8 7/14/2011 0 107
9 7/14/2011 0 108
...

I'd like to return all those rows with the asterick. So, the ones that have a 1 for BadPart or ones that have duplicates. I keep going round and round on this... feeling a little off today...

Adam S

adamstagnaro
Starting Member

4 Posts

Posted - 2011-07-15 : 20:22:52

Bueller?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-15 : 21:10:03

Would this work for you?

select * 
from YourTable t1
where BadPart = 1
or exists 
(
	select * 
	from YourTable t2 
	where t2.PartNumber = t1.PartNumber 
	having count(*) > 1
)

Didn't quite follow what you said about the second where condition required only for today's date. If you want dups for only today's date, then

select * 
from YourTable t1
where BadPart = 1
or exists 
(
	select * 
	from YourTable t2 
	where t2.PartNumber = t1.PartNumber 
		and dateadd(dd,datediff(dd,0,getdate()),0) = Timestamp
	having count(*) > 1
)

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-16 : 00:38:47

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

>> I've been trying to add another column that is 0 if only one entry from today exists, or 1 if multiple entries are present. <<

No, that is a bit flag, which was assembly language and not SQL

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

adamstagnaro
Starting Member

4 Posts

Posted - 2011-07-19 : 17:17:04

Thank you Sunitabeck; I will give that a try and see where I get...

jcelko, sorry for offending your sensibilities. I better get on Wikipedia and look up all the things you tossed at me. I'm a mechanical engineer, not a programmer... so pseudocode is often the best way for me to convey my thoughts. My "needless dialect" is often all I have to get by.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources