Find Records That Do No Match - 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)

Find Records That Do No Match

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2003-03-12 : 12:41:00

I have the following code that gives me the numbers I'm looking for. Does anyone know of a better way to write it? I would really like to have one query that will collect all of the information I'm looking for so I can create an sproc for Crystal Reports to report off of.

I have three tables: QBTRN, QBSPL, and PRODMAST

I need to find any disparities that exist between the tables. The invoice numbers must be the same and the amounts must be the same. QBTRN holds one record for each customer. QBSPL should have line item details for each customer's order. The records are totaled for each customer and then one record is written to QBTRN. The same is true for Prodmast which is a holding table for QBSPL. Somehow the records have gotten out of sync and need to be corrected.

Here's the code:

--drop table tmpprdspltrn

select distinct docnum as trndocnum
into tmpprdspltrn
from qbtrn

alter table tmpprdspltrn add trnamt money 

alter table tmpprdspltrn add spldocnum varchar(8) null
alter table tmpprdspltrn add splamt money 

alter table tmpprdspltrn add prddocnum varchar(8) null
alter table tmpprdspltrn add prdamt money

update tmpprdspltrn
  set trnamt = amount
from tmpprdspltrn
join qbtrn on qbtrn.docnum = tmpprdspltrn.trndocnum
where tmpprdspltrn.trndocnum is not null

update tmpprdspltrn
  set spldocnum = qbspl.docnum
from tmpprdspltrn
join qbspl on qbspl.docnum = tmpprdspltrn.trndocnum
where tmpprdspltrn.trndocnum is not null

update tmpprdspltrn
  set splamt = (select sum(price) from qbspl where docnum = tmpprdspltrn.spldocnum)
from tmpprdspltrn
join qbspl on qbspl.docnum = tmpprdspltrn.trndocnum
where tmpprdspltrn.trndocnum is not null

update tmpprdspltrn
  set prddocnum = prodmast.invnumber
from tmpprdspltrn
join prodmast on prodmast.invnumber = tmpprdspltrn.trndocnum
where tmpprdspltrn.trndocnum is not null

update tmpprdspltrn
  set prdamt = (select sum(saleprice) from prodmast where invnumber = tmpprdspltrn.prddocnum)
from tmpprdspltrn
join prodmast on prodmast.invnumber = tmpprdspltrn.trndocnum
where tmpprdspltrn.trndocnum is not null

select * 
from tmpprdspltrn 
where (trndocnum <> spldocnum) or (trndocnum <> prddocnum) or (spldocnum <>  prddocnum)
--trnamt <> splamt --122
select * 
from tmpprdspltrn 
where ((trnamt <> splamt) and (trndocnum = spldocnum))
--trnamt <> prdamt --12
select * 
from tmpprdspltrn 
where ((trnamt <> prdamt) and (trndocnum = prddocnum))
--splamt <> prdamt --71
select * 
from tmpprdspltrn 
where ((splamt <>  prdamt) and (spldocnum =  prddocnum))

--orphaned prodmast invnumbers  --1216
select tmpprdspltrn.trndocnum, prodmast.invnumber 
from prodmast
right outer join tmpprdspltrn on tmpprdspltrn.trndocnum = prodmast.invnumber
where prodmast.invnumber is null

--orphaned qbspl docnums  --0
select tmpprdspltrn.trndocnum, qbspl.docnum 
from qbspl
right outer join tmpprdspltrn on tmpprdspltrn.trndocnum = qbspl.docnum
where qbspl.docnum is null

(SQL 7)

Thanks for taking the time to look at this!

Best regards,
Teresa

Edited by - tj on 03/12/2003 12:44:31

Edited by - tj on 03/12/2003 13:47:40

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-12 : 13:43:31

Your upper case SQL prevents me form focusing my eyes long enough to read your whole post.

Are you trying to do Relational Difference?

Jay White
{0}

TJ
Posting Yak Master

201 Posts

Posted - 2003-03-12 : 13:48:50

Can you read it now? It's a standard here to make everything upper case. I can't read it when it's upper case either! I only change it before putting it into production. :)

I'm not sure if it's relational? I'm not sure what that means?? WAIT!! Let me click on the link and read...then I'll let you know!

Yes, it is relational! However, there are two conditions that need tested. Can they both be included in the same query or will I need two?

Thanks for responding,
Teresa

Edited by - tj on 03/12/2003 13:50:11

Edited by - tj on 03/12/2003 13:56:43

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-12 : 13:51:05

Cab you post the DDL of the tables including the constraints. Also, btw, you only need 1 ALTER statement, just sep. the columns with a commas (actually I don't think you really even need to build the work table). And doesn't quailfying the columns painful? Why not just do something like:

UPDATE T
SET TRNAMT = AMOUNT
FROM TMPPRDSPLTRN T
JOIN QBTRN Q ON Q.DOCNUM = T.TRNDOCNUM
WHERE T.TRNDOCNUM IS NOT NULL

But again, I think these updates aren't required. I think we can just create 1 simple (or not) SQL statement. But I need the contraints (and the DDL will help too).

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-12 : 13:57:59

Also,

These two statements don't make sense (to me anyway).

UPDATE T
SET SPLDOCNUM = Q.DOCNUM
FROM TMPPRDSPLTRN T
JOIN QBSPL Q ON Q.DOCNUM = T.TRNDOCNUM
WHERE T.TRNDOCNUM IS NOT NULL

-- The one above seems to suggest that there is a 1 to 1 relationship between DOCNUM's between QBSPL And TMPPRDSPLTRN

-- But in the following Your Summing a Price based on Docnum from QBSPL which seems to represent a 1 to many relationship between QBSPL And TMPPRDSPLTRN

UPDATE T
SET SPLAMT = (SELECT SUM(PRICE) FROM QBSPL WHERE DOCNUM = TMPPRDSPLTRN.SPLDOCNUM)
FROM TMPPRDSPLTRN T
JOIN QBSPL Q ON Q.DOCNUM = T.TRNDOCNUM
WHERE T.TRNDOCNUM IS NOT NULL

Am I missing something?

Brett

8-)

TJ
Posting Yak Master

201 Posts

Posted - 2003-03-12 : 14:12:48

quote:

UPDATE T
SET SPLDOCNUM = Q.DOCNUM
FROM TMPPRDSPLTRN T
JOIN QBSPL Q ON Q.DOCNUM = T.TRNDOCNUM
WHERE T.TRNDOCNUM IS NOT NULL

-- The one above seems to suggest that there is a 1 to 1 relationship between DOCNUM's between QBSPL And TMPPRDSPLTRN

quote:

This is true. I want to update the temp table with corresponding docnums from qbspl.

[quote]
-- But in the following Your Summing a Price based on Docnum from QBSPL which seems to represent a 1 to many relationship between QBSPL And TMPPRDSPLTRN

UPDATE T
SET SPLAMT = (SELECT SUM(PRICE) FROM QBSPL WHERE DOCNUM = TMPPRDSPLTRN.SPLDOCNUM)
FROM TMPPRDSPLTRN T
JOIN QBSPL Q ON Q.DOCNUM = T.TRNDOCNUM
WHERE T.TRNDOCNUM IS NOT NULL

Am I missing something?

this statement updates the price. When done, I end up with two columns from QBTRN, two from QBSPL, two from PRODMAST.

my ending results look like this:

TRNDOCNUM TRNAMT                SPLDOCNUM SPLAMT                PRDDOCNUM PRDAMT                
--------- --------------------- --------- --------------------- --------- --------------------- 
28610     550.8900              28610     515.0000              28610     550.8900
28658     550.8900              28658     515.0000              28658     550.8900
28673     449.0000              28673     420.0000              28673     449.0000
28897     174.4400              28897     164.0000              28897     174.4400
28959     174.4400              28959     164.0000              28959     174.4400
29027     550.8900              29027     515.0000              29027     550.8900
29032     989.7500              29032     920.0000              29032     989.7500
29039     337.8600              29039     315.0000              29039     337.8600

Now I can do a comparison on the columns within the table without using any joined tables. My goal is to create a recordset to use with Crystal Reports. While what I already have works, I am looking for a simpler, more concise way to write it.

Thanks again!
Teresa

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-12 : 14:36:20

OK, SQL Coding in the dark (The DDL With constraints will help):

SELECT QBTRN_DOCNUM, QBTRN_AMT, QBSPL_DOCNUM, QBSPL_AMT, PRODMAST_DOCNUM, PRODMAST_AMT
FROM
(SELECT DOCNUM AS QBTRN_DOCNUM, SUM(AMT) AS QBTRN_AMT
FROM QBTRN
GROUP BY DOCNUM) As X
FULL OUTTER JOIN
(SELECT DOCNUM AS QBSPL_DOCNUM , SUM(AMT) AS QBSPL_AMT
FROM QBSPL
GROUP BY DOCNUM) As Y ON X.QBTRN_DOCNUM = Y.QBSPL_DOCNUM
FULL OUTTER JOIN
(SELECT DOCNUM AS PRODMAST_DOCNUM, SUM(AMT) AS PRODMAST_AMT
FROM PRODMAST
GROUP BY DOCNUM) As Z ON Y.QBSPL_DOCNUM = Z.PRODMAST_DOCNUM

What'dya think?

Brett

8-)

TJ
Posting Yak Master

201 Posts

Posted - 2003-03-12 : 15:31:59

quote:

OK, SQL Coding in the dark (The DDL With constraints will help):

what is DDL

I'll test the code and see how well you did with the blind-fold on.

TJ
Posting Yak Master

201 Posts

Posted - 2003-03-12 : 15:44:25

Brett,

Yes!!! It works!!! What is this query called? Where can I find information on it that would have enlightened my little world??

While it may have been the 'blind' leading the 'blind' you did a great job!

Thank you, thank you, thank you!!!

Teresa

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-13 : 08:51:51

DDL is "Data Definition Language". This is what you use to build your tables. It can be scripted (Right Click in EM, All Tasks, Generate SQL Script).

What I posted is DML, "Data Manipulation Language". This is your general SQL statement to gain access to and alter data.

I don't know that there is any specific name for that statement, except to say I utilized "Derived" Tables. That's where you say SELECT * FROM (SELECT...) AS XXX

The derived part is the inner select.

Glad I could help

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources