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')
Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
Peachtree 2012 / SQL query Sales Journal with COGS
Who is online
Users browsing this forum: No registered users and 4 guests