Ok, here is the Transaction table DDL:

So I pull Customer 'House Accounts' from this table via the 's_credit_tran_type' field. There are 2 fields in the Customer table that are used, but its really not needed for this discussion. cust_membership_id = Account Receivable for every House Account. This is an identifier in Quickbooks, nothing more.

SELECT c.cust_fullname AS Name,

c.cust_membership_id AS Account,

t.c_amount AS Amount,

'EN' + CAST(t.i_ticket_id AS varchar(12)) AS Doc_Num,

t.s_credit_tran_type AS Detail_Account,

CONVERT(varchar(10), t.dt_when, 110) AS SaleDate,

'FOOD' AS ItemSold

FROM dbo.Transactions AS t INNER JOIN

dbo.Customers AS c ON t.s_ref_num = c.cust_id

WHERE (t.s_credit_tran_type = 'House Account') AND (t.b_cancel = 0) AND (t.dt_when >= DATEADD(dd, DATEDIFF(dd, '17530101', GETDATE()) / 7 * 7 - 7, '17530101')) AND

(t.dt_when <= DATEADD(dd, DATEDIFF(dd, '17530101', GETDATE()) / 7 * 7, '17530101'))

GROUP BY c.cust_fullname,

c.cust_membership_id,

t.c_amount,

t.i_ticket_id,

t.s_credit_tran_type,

t.dt_when

There is a Tender Type in the Point of Sale software (POS) that populates the 's_credit_tran_type' field called House Account. As you can see from the code above this is the main indicator for house account charges for the previous week. I have this code executing every Monday morning at 3am, outputting to a .CSV file and then BLAT auto emails this file to their CPA who then uses a program to push this data into Quickbooks to bill all the House Account charges to the respective Customers. There are roughly 15-25 House Account charges a week.

The issue is, when someone wants to return an item (this is a very popular bakery in a small town so this is why they allow returns) there is a paper trail having to go back to the CPA. I want the SQL code to give her the return (credit) amount in the same .CSV output file to rid them of the paper trail returns are currently handled, to make this nice and neat in Quickbooks for them.

I already have the workflow for the Cashier's worked out to where the POS Software will 'return' the item from a Guest Check. They re-open the Guest Check, remove the item the House Account ticket customer is returning (found by Ticket ID), and adjust the amount (handled auto by the POS) and then close the Guest Check. This will print a new receipt they will pass to the Customer.

When they do this return, it creates a b_cancel of 1 for a ticket ID....see below.

select c_amount, i_ticket_id, s_credit_tran_type, s_credit_auth, dt_when, b_cancel from Transactions

where s_credit_tran_type = 'House Account'

ORDER BY i_ticket_id DESC

See (in Yellow) how the Ticket ID's are the same? and the b_cancel = 1 denotes the 'old' Ticket is now 'voided' and the "new" ticket has the reduced amount due to an item being removed from the Guest Check (returned).

Here is the output from the SQL code at the beginning of this thread, this is what is exactly pushed into the .CSV file every week:

Notice how COLLEGE & ALL IN ONE AUTO have 2 'charges' for the same Ticket_ID? I want the difference to show up as a negative and only display the Ticket_ID that is b_cancel = 0

Example:ALL IN ONE AUTO Accounts Receivable -4.40 EN56094 House Account 01-06-2015 FOOD

There is a caveat tho...my original SQL code only searches the previous week for 'House Account' charges....so SQL will find the return Ticket (b_cancel = 1) as well...BUT the SQL code must be able to search as far back in the Transactions table to find its matching Ticket_ID...the customer could be returning an item a few weeks after the fact.

I do not need this to output to a different table...the same table, one nice neat file is all I need.

Man I hope this makes sense. Seems like this would be super simple for someone with exp. I am still learning SQL tho.