Page 1 of 1

Peachtree 2012 / SQL query Sales Journal with COGS

Posted: 18 Oct 2012, 14:41
by gartom
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')