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.
| Author |
Topic |
|
sheriefes
Starting Member
9 Posts |
 Posted - 2010-12-23 : 14:12:43
|
| HiI hav a 2 tablesownSales with fields - invoiceNo, invoicedate, purchaser,amountotherSales with fields - invoiceNo, invoicedate, purchaser,amountownSales contains records of sales made by Aex: invoiceNo invoicedate purchaser amount 4 10-12-10 B&Co 1000 5 11-12-10 C&Co 2000 6 12-12-10 D&Co 3000ie., as per invoiceNo 4, A sold to B&Co goods worth $1000 as per invoiceNo 6, A sold to D&Co goods worth $3000otherSales contains records of purchases made dealers from AexinvoiceNo, invoicedate, seller,amount A4 10-12-10 B&Co 1000 5-11/12 11-12-10 C&Co 2000 6 12-12-10 D&Co 3000ie., as per invoiceNo A4, B&Co purchased goods worth $1000 from A as per invoiceNo 6, D&Co purchased goods worth $3000 from ANow come to the point:1. We have sales details declared by A & also details of purchases from A, by other dealers 2. We want to cross verify - whether A's declaration is full and correct (from the declaratio by other dealsers, that they made such purchases from A)SELECT * FROM otherSales WHERE invoiceNo NOT IN(SELECT invoiceNo FROM ownSales) will surely an answer3. But, the problem is sometimes other dealers (B&CO, C&Co etc) not giving the invoiceNo as exactly as given by A - they make little alterations, see othersales recordsex;, in the case given above, the above query returns 2 records which is incorrect.4. that means, we must check amount, date, purchaser also as a lenient way( before reaching a conclusion A made sales suppressions)5. I want query for that? any idea |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-23 : 14:27:34
|
| Homework?--Gail ShawSQL Server MVP |
 |
|
|
|
|
|
|
|
Complex sub query - a Challenge