Query Help - 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

SQL Server Development (2000)

Query Help

Author

Topic

JTProg
Starting Member

24 Posts

Posted - 2007-02-21 : 13:48:00

I need to write a query to retrieve data, but I'm unsure how to get the proper results back. Can someone help? Here is a condensed version of the tables I'm working with.

Table 1:
batchnumber
batchdate

Table 2:
sid
batchnumber
sguar
scase
sinvoice

I'm trying to find duplicated sguar #'s, scase #'s, sinvoice #'s on different batches on the same day. This will let me know who it duplicating work within our teams. Can this be done?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-02-21 : 14:04:33

Yes, it can be done. What have you tried so far?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

JTProg
Starting Member

24 Posts

Posted - 2007-02-21 : 14:36:22

Sorry, this is what I have so far.

SELECT dbo.Table1.batchnumber, dbo.Table1.batchdate, dbo.Table2.sguar, dbo.Table2.sinvoice, dbo.Table2.scase
FROM dbo.Table1 INNER JOIN
dbo.Table2 ON dbo.Table1.batchnumber = dbo.Table2.batchnumber
WHERE (CONVERT(char(10), dbo.Table1.batchdate, 101) = CONVERT(char(10), GETDATE(), 101))
AND (dbo.Table2.scase In (SELECT dbo.Table2.scase FROM dbo.Table2 GROUP BY dbo.Table2.scase HAVING Count(*)>1 ))
OR (dbo.Table2.sinvoice In (SELECT dbo.Table2.sinvoice FROM dbo.Table2 GROUP BY dbo.Table2.sinvoice HAVING Count(*)>1 ))
OR (dbo.Table2.sguar In (SELECT dbo.Table2.sguar FROM dbo.Table2 GROUP BY dbo.Table2.sguar HAVING Count(*)>1 ))
GROUP BY dbo.Table1.batchnumber, dbo.Table1.batchdate, dbo.Table2.sguar, dbo.Table2.sinvoice, dbo.Table2.scase

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 14:57:01

Try this

SELECT DISTINCT	t1.BatchNumber,
		t1.BatchDate,
		t2.sGuar,
		t2.sInvoice,
		t2.sCase
FROM		dbo.Table1 AS t1
INNER JOIN	dbo.Table2 AS t2 ON t2.BatchNumber = t1.BatchNumber
LEFT JOIN	(
			SELECT		sInvoice
			FROM		dbo.Table2
			GROUP BY	sInvoice
			HAVING		COUNT(*) > 1
		) AS x1 on x1.sInvoice = t2.sInvoice
LEFT JOIN	(
			SELECT		sCase
			FROM		dbo.Table2
			GROUP BY	sCase
			HAVING		COUNT(*) > 1 
		) AS x2 on x2.sCase = t2.sCase
LEFT JOIN	(
			SELECT		sGuar
			FROM		dbo.Table2
			GROUP BY	sGuar
			HAVING		COUNT(*) > 1
		) as x3 on x2.sGuar = t2.sGuar
WHERE		t1.BatchDate >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
		AND t1.BatchDate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1)
		AND COALESCE(x1.sInvoice, x2.sCase, x3.Guar) IS NOT NULL

Peter Larsson
Helsingborg, Sweden

JTProg
Starting Member

24 Posts

Posted - 2007-02-21 : 16:51:33

The query does display duplicates, but it's checking for duplicates within the same batch. I need to check to see if other batches include the same sInvoice, sCase, or sGuar Numbers on the same day.

Example:
BatchNumber 392 created today has sInvoice of 239, sCase of 393, and sGuar of 137.
BatchNumber 393 created today has sInvoice of 239, sCase of 394, and sGuar of 137.

I want to be able to return a report displaying batchnumbers 392 & 393 contain sInvoice 239 which was created twice today, (or whatever the date might be.). batchnumbers 392 & 393 contain sGuar 137 which was created twice today, (or whatever the date might be.)

Can the query you created be modified to display this?

Thanks for helping.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources