Need more help now? use pay-as-you-go support, call now 03333 445950

We offer rebates on Sage50 subscriptions, discounts on products, service sagecover (not renewals) and stationery.

We also provide Sage 50 integration and email/web services.

Peachtree 2012 / SQL query Sales Journal with COGS

For some reason the powers that be in Sage have decided to us Sage 50 as a name for lots of different accounting systems and this has led to a lot of confusion.

The UK version is sold in UK Eire, Malta, Africa, Australia and New Zealand.

It does get used in other parts of the world (eg China France) but it is not actively marketed.

This forum is for questions relating to the non UK versions.
Post Reply
gartom
User
User
Posts: 1
Joined: 18 Oct 2012, 14:38
Sage Version: v2012 18 UK/Europe/Africa

Peachtree 2012 / SQL query Sales Journal with COGS

Post by gartom » 18 Oct 2012, 14:41

Currently I export data using the export / Recievables / Fabrication Analysis Export (custom sales Journal report). This will export the information to a CSV file. This contains the Cost Of Sales Amount. (COGS). To automate things, I have created a SQL statement that exports this to a SQL database. I have gathered all the fields that I need except the Cost of Sales Amount. I have not been able to find it. I have included the statement here. It works flawlessly. I just need to know how to add the COGS. Any help would be appreciated.



SELECT jrow.RowDate AS Date, jhdr.Reference AS [Invoice/CM#], custs.CustomerName, jhdr.ShipToName, jhdr.ShipToAddress1, jhdr.ShipToAddress2,
jhdr.ShipToCity, jhdr.ShipToState, jhdr.ShipToZIP, jhdr.ShipToCountry, emp.SalesrepID, LItem.ItemID, jrow.Quantity, LItem.StockingUM, jrow.amount,jrow.unitCost, coa.GLAccount
FROM OPENROWSET('MSDASQL', 'Provider=MSDASQL.1;DSN=dsn;UID=Peachtree; pwd = password',
'Select Reference, Postorder, JrnlKey_Journal, ShipToName, ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToZip, ShipToCountry, EmpRecordNumber from jrnlhdr')
AS jhdr INNER JOIN

OPENROWSET('MSDASQL', 'Provider=MSDASQL.1;DSN=dsn;UID=Peachtree; pwd = password',
'Select rowdate, postOrder, CAST (amount AS NUMERIC (20,2)) as amount, GLAcntnumber, CustomerRecordNumber, ItemRecordNumber, CAST (quantity AS NUMERIC (20,0))as Quantity, CAST (unitcost AS NUMERIC (20,4)) as unitCost from jrnlrow')
AS jrow ON jhdr.PostOrder = jrow.PostOrder INNER JOIN

OPENROWSET('MSDASQL', 'Provider=MSDASQL.1;DSN=dsn;UID=Peachtree; pwd = password',
'Select CustomerRecordNumber, CustomerID, Customer_Bill_Name as CustomerName from Customers') AS custs ON
jrow.CustomerRecordNumber = custs.CustomerRecordNumber INNER JOIN

OPENROWSET('MSDASQL', 'Provider=MSDASQL.1;DSN=dsn;UID=Peachtree; pwd = password',
'Select EmpRecordNumber, EmployeeID as SalesrepID from Employee') AS emp ON jhdr.EmpRecordNumber = emp.EmpRecordNumber INNER JOIN

OPENROWSET('MSDASQL', 'Provider=MSDASQL.1;DSN=dsn;UID=Peachtree; pwd = password',
'Select ItemRecordNumber, ItemID, StockingUM from LineItem') AS LItem ON jrow.ItemRecordNumber = LItem.ItemRecordNumber INNER JOIN

OPENROWSET('MSDASQL', 'Provider=MSDASQL.1;DSN=dsn;UID=Peachtree; pwd = password',
'Select GLAcntnumber, AccountID as GLAccount from chart') AS coa ON jrow.GLAcntNumber = coa.GLAcntNumber

WHERE (jhdr.JrnlKey_Journal = 3) AND (CONVERT(CHAR(10), jrow.RowDate, 120) >= '2010-01-01')

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest