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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Other Development Tools
 VB.Net 2005 - SQL Server 2005 - Data Issue
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

o9z
Starting Member

23 Posts

Posted - 02/05/2007 :  12:14:38  Show Profile  Reply with Quote
I have been working on a data export for quite some time now and have some issues.

What happens is as follows:

Data is entered by the user on the main form which contains sales information. The app is basically a data repository. Nothing more. The data stored in the SQL table is then exported to our accounting system. The problem I am running into is with my JOIN clause of my select. I only have one way to tie into the other SQL table. I am joining KS.Customer(main data table) to SALESCODE.CUSTOMER(the table that stores the account code for each customer). The problem is that there are multiple entries for each customer. Example:

Customer - Account Code - Description
CustA 0001 Market Sales - CustA
CustA 0002 Resales - CustA
CustA 0003 Weight Fees - CustA
CustA 0004 PPF - CustA

CustB 0004 Market Sales - CustB
CustB 0005 Resales - CustB
CustB 0006 Weight Fees - CustB
CustB 0007 PPF - CustB


Here is where the problem comes. My export is writing the exact data I want to the file.....except it is writing it THREE times per record since my join finds 3 matches in the SALESCODE table.

Is there an easy way to get around this?

The complexity of the export is much more than I just explained.

The export might look something like this

Invoice Custnum invdate salecode quantity price extamt descr
K00001 CustA 2-1-07 0001 140 61.40 8596.47 Market Sales - CustA
K00001 CustA 2-1-07 0004 140 -0.64 -89.36 PPF - CustA
K00001 CustA 2-1-07 0003 140 -0.05 -7.00 Weight Fees - CustA
K00001 CustA 2-1-07 /C 0 0 0 Market Sales - This is only a comment line interpreted by the accounting system
K00001 CustA 2-1-07 0001 3.26 46.01 150.00 Market Sales - CustA
K00001 CustA 2-1-07 0004 3.26 -0.38 -1.25 PPF - CustA
K00001 CustA 2-1-07 /C 0 0 0 Resales

The first 4 lines are part of the "Market" block of the export. The 2nd 3 lines are part of the "Resales" block of the export. The first line uses OVERALL number to figure the calculates for quantity, price, extamt. The 2nd lines uses overall PPF fees to calculate the deductions. The 3rd lines uses the overall weight fees.

The 2nd block(resales) looking in the database for the Resales_market, Resales_PPF, Resales_WeightFee fields. If the resales fields contain "0" it will not write these lines.

So the problem I am having is not getting the correct data to the export, but rather getting TOO MUCH of the correct data to the export. Because my join is finding matches, it will go through and write the correct stuff for each invoice #, but add it 3-fold.

Anyone have an easy solution to this, or need more information to understand it? I know it's a PITA!!!!

EDIT: Here is what my SQL statement looks like

SELECT KSInvoice, CUSTOMER, LOCATION, Finisher_Loc, SHIPDATE, totalnumber, pay, Weight_Fee, WEIGHT, RESALES_PAY, RESALES_WEIGHT, RESALES_numb, resales_ppf, salecode.Customer, salecode.SaleType, salecode.SALECODE, salecode.SALESACCT, salecode.[desc]
FROM KS LEFT JOIN SALECODE ON KS.Customer = salecode.customer
WHERE exported IS NULL

When I only have 1 invoice in the KS table, and I run this query, I will return 3 records.(This is not the exact output, but a stripped down version of what it would be)
K00001 CustA market line
K00001 CustA ppf line
K00001 CustA weight fee line

NOTE: I edited a lot of things out of here simply because there is MUCH more to this than I wrote out. There are a few more blocks to get written to the file and more fields to use. I tried to strip it down to give an idea as to what I am wanting to do.

Is there a way to modify my select to limit what it finds to 1 match per invoice?

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/05/2007 :  15:26:31  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
If 3 rows match, and you want to return only a single row, which row do you want returned ?? Hopefully you can see that you need to make some logical decisions regarding your data before you can just get the "right" answer.

Think of it this way: What if I tell you I have a table of states, and a table of cities, and I want you to return all states and all cities, but only 1 row per state. (i.e., I want only 50 rows returned). Forget about writing code to do this -- the first step is to logically define how you want to calculate these results .... i.e., do we return the first city listed alphabetically, or the one with the largest population, or the capital, or a comma-separated list of all cities stuffed into 1 column, or just the count of total cities in the state, and so on.

You need to decide upon that specification before anyone can write the SQL to achieve those results.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000