a little help please - 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

a little help please

Author

Topic

premkumar
Starting Member

4 Posts

Posted - 2007-10-08 : 11:08:39

my case is this:
i need to find the customers who have placed duplicate orders in the last 1 week, from a single table.
for example, if i placed a 200 dollar order yesterday twice, mine should be visible. i tried with nested queries, but im getting more columns that dont satisfy the condition.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-08 : 11:12:33

Please provide the DDL of the table, the specific columns you want returned and the column containing the duplicate.

Future guru in the making.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 11:19:05

[code]SELECT CustomerID
FROM Table1
WHERE OrderDate >= DATEDIFF(DAY, 8, CURRENT_TIMESTAMP)
GROUP BY CustomerID
HAVING COUNT(*) > 1[/code]

E 12°55'05.25"
N 56°04'39.16"

premkumar
Starting Member

4 Posts

Posted - 2007-10-09 : 03:20:46

@ peso, the query that u gave gives an output only for the customer who has placed multiple orders of different value, but not the same order value. i want customers placing same value orders.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 03:23:31

Fixed. Not that hard to do, really...

SELECT		CustomerID,
		OrderValue
FROM		Table1
WHERE		OrderDate >= DATEDIFF(DAY, 8, CURRENT_TIMESTAMP)
GROUP BY	CustomerID,
		OrderValue
HAVING		COUNT(*) > 1

Or if you only want CustomerID?

SELECT DISTINCT	CustomerID
FROM		Table1
WHERE		OrderDate >= DATEDIFF(DAY, 8, CURRENT_TIMESTAMP)
GROUP BY	CustomerID,
		OrderValue
HAVING		COUNT(*) > 1

E 12°55'05.25"
N 56°04'39.16"

premkumar
Starting Member

4 Posts

Posted - 2007-10-09 : 03:30:29

@ peso,

thanks a lot.. thanks...!!! for ur timely help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources