Comparing Sales Data to Accounts Receivable

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)

Comparing Sales Data to Accounts Receivable

Author

Topic

cmonache
Starting Member

3 Posts

Posted - 2004-05-07 : 22:55:39

Hello,

Currently working on extended business in Japan. My current company has a very strange way of handling point of sales and accounts receivable. Basically we have sales data at the transaction level but the AR is summarized at the customer level. Also there is no way to match back and forth between this two files via and invoice. So basically we have sales going out with money coming in and not real way to match the AR with the sales transaction. I know it is crazy. Basically the data might look like this:

------------------ Sales Data -------------------
Customer Date Sales Price
------------- ------- ----------------
1111111 200304 10
1111111 200304 20
1111111 200304 70
2222222 200305 10
2222222 200305 20

------------------- AR Data ----------------------
Customer Date Sales Price
------------- ------- ----------------
1111111 200304 100
2222222 200305 30

In a perfect world I could just match up the total for sales and AR receivable and be finished but the total do not match from month to month and there is no rule to follow to say when the money will be follow after sales.

So I have two databases with 6 months of sales data and 6 months of Accounts Receivable data. Can any one help me to create some fuzzy logic based on FIFO that might get me a reasonable match over a 6 month period using the customer number, date and amount fields from AR and sales data?

This is kind of a challenge maybe.

Thanks!

Chris

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-08 : 15:26:30

There should be a table related to AR that matches the AR Data to some kind of a PO. You should have the same thing for the Sales. Doesn't this exist anywhere? If not, they just need to scrap it and redo it since they have no idea if they're getting paid correctly. If they do have this, you need to link back to the PO numbers and build your logic from there.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

cmonache
Starting Member

3 Posts

Posted - 2004-05-08 : 17:20:35

Hello,

The sales tables and AR tables are in two unrelated data bases and there is no invoice to compare. In many cases we do not even send invoices to customers. It is kind of a strange way they sometimes do business in Japan. We are in the process of implementing JD Edwards but this will not be available until next year and we are trying to do some comparisons on past years data.

The current problem is that we indeed do not know if we are getting paid correctly. At a high level we know based on customer totals but we do not know on an order level. This has caused problems for us. Under Japan GAAP point of sale is recognized at point of shipment. Under US GAAP point of sale is recognized when the customer takes owner ship of the goods. Basically we are concerned that in past years that sales which should have been accounted for in the following year were recognized in the previous year.

In the sales files I have a customer number, a sales date, and an amount. In the AR file I have a AR date, amount and customer number. I was going to try to do a simple match on amounts and customer numbers but the sales data is recorded on order/transaction level and AR is summarized at a month total or the total for what the customer pays. The customer would pay for 10 orders all at one time which would be recorded in AR.
I am trying to find some logic that would allow me to try to get a close match. Does not have to be exact.

Chris

Chris

cmonache
Starting Member

3 Posts

Posted - 2004-05-08 : 22:36:26

Hello,

I figured out how to do what I wanted to do but now I need help trying to make my SQL logic work. To match my my AR file and Sales file I used the following logic and was able to match 564 out of 771 records:

Select CustomerNumber, Sum(Sales) * 1.05 as MonthSalesTotals, ShipmentMonthYear
Into TempTable2
From IAF_Sales_2003
GROUP BY CustomerNumber, ShipmentMonthYear
Order By CustomerNumber

Select CustomerNumber, AR_CustomerNumber, MonthSalesTotals, AR_Amount,ShipmentMonthYear, AR_DateReceived
From TempTable2 INNER JOIN IAF_AR_2003
ON (TempTable2.CustomerNumber = IAF_AR_2003.AR_CustomerNumber) AND (TempTable2.MonthSalesTotals = IAF_AR_2003.AR_Amount)
Order By CustomerNumber

Now I want to try to create a list of the unmatched records. I tried to use the following logic but if I use EXISTS it returns all 771 records. If I use NOT EXISTS it returns zero records.

Select CustomerNumber, Sum(Sales) * 1.05 as MonthSalesTotals,
ShipmentMonthYear
Into TempTable2
From IAF_Sales_2003
GROUP BY CustomerNumber, ShipmentMonthYear
Order By CustomerNumber

Select CustomerNumber, MonthSalesTotals, ShipmentMonthYear FROM TempTable2
Where EXISTS (
Select CustomerNumber, AR_CustomerNumber, MonthSalesTotals, AR_Amount, ShipmentMonthYear
From TempTable2 INNER JOIN IAF_AR_2003
ON (TempTable2.CustomerNumber = IAF_AR_2003.AR_CustomerNumber) AND (TempTable2.MonthSalesTotals = IAF_AR_2003.AR_Amount)

)

Thanks in advance for the logic fix...

Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources