Assistance with an IF THEN in SQL Sever 2008 R2?

Question

Post reply

Assistance with an IF THEN in SQL Sever 2008 R2?

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

In that case and pretending this is your actual data...

SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY i_ticket_id ORDER BY dt_when)

,i_transaction_id

,c_amount

,dt_when = CONVERT(DATETIME,dt_when)

,i_ticket_id

,s_credit_auth

,s_credit_tran_type

,b_cancel

INTO #WorkTable

FROM ( --=== This simulates the result from your query as an input to the temp table.

SELECT 4478,16.10,'2015-01-06 15:44:17.733',58475,'ALL IN ONE AUTO' ,'House Account', 1 UNION ALL

SELECT 4479,25.55,'2015-01-06 15:45:11.060',58476,'ADCO SHEET METAL','House Account', 0 UNION ALL

SELECT 4480,11.50,'2015-01-06 15:48:21.367',58475,'ALL IN ONE AUTO' ,'House Account', 1 UNION ALL

SELECT 4481,10.00,'2015-01-06 15:50:33.123',58475,'ALL IN ONE AUTO' ,'House Account', 0

)d(i_transaction_id,c_amount,dt_when,i_ticket_id,s_credit_auth,s_credit_tran_type,b_cancel)

;

... the following code should do it for you...

SELECT hi.i_transaction_id,hi.c_amount,hi.dt_when,hi.i_ticket_id,hi.s_credit_auth,hi.s_credit_tran_type,hi.b_cancel

,Changed = (hi.c_amount-ISNULL(lo.c_amount,hi.c_amount))

FROM #WorkTable lo

RIGHT JOIN #WorkTable hi

ON lo.i_ticket_id = hi.i_ticket_id

AND lo.RowNum + 1 = hi.RowNum

ORDER BY hi.i_transaction_id

;

That produces the following output for the given data and you should be able to fold that into your code...

i_transaction_id c_amount dt_when i_ticket_id s_credit_auth s_credit_tran_type b_cancel Changed

---------------- -------- ----------------------- ----------- ---------------- ------------------ ----------- ---------------------------------------

4478 16.10 2015-01-06 15:44:17.733 58475 ALL IN ONE AUTO House Account 1 0.00

4479 25.55 2015-01-06 15:45:11.060 58476 ADCO SHEET METAL House Account 0 0.00

4480 11.50 2015-01-06 15:48:21.367 58475 ALL IN ONE AUTO House Account 1 -4.60

4481 10.00 2015-01-06 15:50:33.123 58475 ALL IN ONE AUTO House Account 0 -1.50

(4 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chef423 SSCertifiable Points: 5279 More actions · Answer 2

Jeff Moden (1/11/2015)
In that case and pretending this is your actual data...
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY i_ticket_id ORDER BY dt_when)
,i_transaction_id
,c_amount
,dt_when = CONVERT(DATETIME,dt_when)
,i_ticket_id
,s_credit_auth
,s_credit_tran_type
,b_cancel
INTO #WorkTable
FROM ( --=== This simulates the result from your query as an input to the temp table.
SELECT 4478,16.10,'2015-01-06 15:44:17.733',58475,'ALL IN ONE AUTO' ,'House Account', 1 UNION ALL
SELECT 4479,25.55,'2015-01-06 15:45:11.060',58476,'ADCO SHEET METAL','House Account', 0 UNION ALL
SELECT 4480,11.50,'2015-01-06 15:48:21.367',58475,'ALL IN ONE AUTO' ,'House Account', 1 UNION ALL
SELECT 4481,10.00,'2015-01-06 15:50:33.123',58475,'ALL IN ONE AUTO' ,'House Account', 0
)d(i_transaction_id,c_amount,dt_when,i_ticket_id,s_credit_auth,s_credit_tran_type,b_cancel)
;
... the following code should do it for you...
SELECT hi.i_transaction_id,hi.c_amount,hi.dt_when,hi.i_ticket_id,hi.s_credit_auth,hi.s_credit_tran_type,hi.b_cancel
,Changed = (hi.c_amount-ISNULL(lo.c_amount,hi.c_amount))
FROM #WorkTable lo
RIGHT JOIN #WorkTable hi
ON lo.i_ticket_id = hi.i_ticket_id
AND lo.RowNum + 1 = hi.RowNum
ORDER BY hi.i_transaction_id
;
That produces the following output for the given data and you should be able to fold that into your code...
i_transaction_id c_amount dt_when i_ticket_id s_credit_auth s_credit_tran_type b_cancel Changed
---------------- -------- ----------------------- ----------- ---------------- ------------------ ----------- ---------------------------------------
4478 16.10 2015-01-06 15:44:17.733 58475 ALL IN ONE AUTO House Account 1 0.00
4479 25.55 2015-01-06 15:45:11.060 58476 ADCO SHEET METAL House Account 0 0.00
4480 11.50 2015-01-06 15:48:21.367 58475 ALL IN ONE AUTO House Account 1 -4.60
4481 10.00 2015-01-06 15:50:33.123 58475 ALL IN ONE AUTO House Account 0 -1.50
(4 row(s) affected)

Thanks, but confused as to how to get this:

SELECT hi.i_transaction_id,hi.c_amount,hi.dt_when,hi.i_ticket_id,hi.s_credit_auth,hi.s_credit_tran_type,hi.b_cancel

,Changed = (hi.c_amount-ISNULL(lo.c_amount,hi.c_amount))

FROM #WorkTable lo

RIGHT JOIN #WorkTable hi

ON lo.i_ticket_id = hi.i_ticket_id

AND lo.RowNum + 1 = hi.RowNum

ORDER BY hi.i_transaction_id

;

Into this:

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

chef423 SSCertifiable Points: 5279 More actions · Answer 3

I just need to look at it a bit more, thanks for all the help...

I need to figure out how to query my output table....

There is no Transaction ID in my original output.....

steve.tarry SSC Enthusiast Points: 132 More actions · Answer 4

I don't quite understand the issue here (sorry), but are you trying to find the difference between Cancelled and Non Cancelled for given ticket?

If so would something like the following help

select

ticket

sum(case when cancel=1 then amount else 0 end) cancelamount

sum(case when cancel=0 then amount else 0 end) amount

sum(case when cancel=0 then amount else 0 end) -

sum(case when cancel=1 then amount else 0 end) diff

from <table>

group by ticket

Enhanced from the code supplied in previous post....

SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY i_ticket_id ORDER BY dt_when)

,i_transaction_id

,c_amount

,dt_when = CONVERT(DATETIME,dt_when)

,i_ticket_id

,s_credit_auth

,s_credit_tran_type

,b_cancel

INTO #WorkTable

FROM ( --=== This simulates the result from your query as an input to the temp table.

SELECT 4478,16.10,'2015-01-06 15:44:17.733',58475,'ALL IN ONE AUTO' ,'House Account', 1 UNION ALL

SELECT 4479,25.55,'2015-01-06 15:45:11.060',58476,'ADCO SHEET METAL','House Account', 0 UNION ALL

SELECT 4480,11.50,'2015-01-06 15:48:21.367',58475,'ALL IN ONE AUTO' ,'House Account', 1 UNION ALL

SELECT 4481,10.00,'2015-01-06 15:50:33.123',58475,'ALL IN ONE AUTO' ,'House Account', 0

)d(i_transaction_id,c_amount,dt_when,i_ticket_id,s_credit_auth,s_credit_tran_type,b_cancel)

;

Select i_ticket_id,

sum(case when b_cancel=1 then c_amount else 0 end) cancelledAMT,

sum(case when b_cancel=0 then c_amount else 0 end) AMT,

abs(sum(case when b_cancel=1 then c_amount else 0 end) -

sum(case when b_cancel=0 then c_amount else 0 end)) AMTDIFF

from #WorkTable

Group By i_ticket_id

chef423 SSCertifiable Points: 5279 More actions · Answer 5

Well, with this code:

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

SELECT hi.c_amount,

hi.dt_when,

hi.i_ticket_id,

hi.s_credit_auth,

hi.s_credit_tran_type,

hi.b_cancel,

Changed = (hi.c_amount-ISNULL(lo.c_amount, hi.c_amount))

FROM Transactions lo

RIGHT JOIN Transactions hi

ON lo.i_ticket_id = hi.i_ticket_id

--AND lo. + 1 = hi.RowNum

WHERE hi.s_credit_tran_type = 'House Account' AND hi.b_cancel = 1

ORDER BY hi.i_ticket_id

I'm really close...I get this output:

c_amountdt_when i_ticket_id s_credit_auth s_credit_tran_type b_cancel Changed

16.10 2015-01-06 15:44:17.73358475ALL IN ONE AUTO House Account 10.00

16.10 2015-01-06 15:44:17.73358475ALL IN ONE AUTO House Account 14.60

Let me try to explain what this code does in terms of the database its querying...

The top part of the code from SELECT to GROUP BY seaches the database from last week (t.dt_when >) and looks for the Key work HOUSE ACCOUNT...if there IS a HOUSE ACCOUNT 'charge' I have a batch file that pulls out the House Account charges and puts them into a .CSV file which is then imported into Quickbooks. It works flawlessly.

BUT now when a customer has a return...there is a big ugly paper trail the CPA receives to credit the customers 'House Account' so I want to implement the correct code to take any 'return' and simply add the line to the .CSV file as (-4.60), the Changed column above...BUT I need the code to search as far back as needed to find the same TicketID.

When a return is made, the cashier reopens the Ticket and then removes the Items from the Ticket and then closes the ticket, then SQL will b_cancel = 1 of the original TicketID and create a new TicketID with the reduced amount. So if the original was say $30, and they returned one item that took off -$15 the new balance of the Ticket would be $15.

Make sense?

Thanks for any help.....PLEASE!

steve.tarry SSC Enthusiast Points: 132 More actions · Answer 6

So does this not give you what you need?

--DROP TABLE #WorkTable;

SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY i_ticket_id ORDER BY dt_when)

,i_transaction_id

,c_amount

,dt_when = CONVERT(DATETIME,dt_when)

,i_ticket_id

,s_credit_auth

,s_credit_tran_type

,b_cancel

INTO #WorkTable

FROM ( --=== This simulates the result from your query as an input to the temp table.

SELECT 4478,16.10,'2015-01-06 15:44:17.733',58475,'ALL IN ONE AUTO' ,'House Account', 1 UNION ALL

SELECT 4479,25.55,'2015-01-06 15:45:11.060',58476,'ADCO SHEET METAL','House Account', 1 UNION ALL

SELECT 4480,11.50,'2015-01-06 15:48:21.367',58475,'ALL IN ONE AUTO' ,'House Account', 0 --UNION ALL

--SELECT 4481,10.00,'2015-01-06 15:50:33.123',58475,'ALL IN ONE AUTO' ,'House Account', 0

)d(i_transaction_id,c_amount,dt_when,i_ticket_id,s_credit_auth,s_credit_tran_type,b_cancel)

;

SelectROW_NUMBER() OVER (PARTITION BY i_ticket_id ORDER BY i_ticket_id) Rowno,

i_ticket_id,

sum(case when b_cancel=1 then c_amount else 0 end) Amount,

sum(case when b_cancel=0 then c_amount else 0 end) CancelledAmt,

sum(case when b_cancel=1 then c_amount else 0 end) -

sum(case when b_cancel=0 then c_amount else 0 end) NewBalance,

s_credit_auth,

s_credit_tran_type

from #WorkTable

Group By i_ticket_id

,s_credit_auth

,s_credit_tran_type

chef423 SSCertifiable Points: 5279 More actions · Answer 7

steve.tarry (1/15/2015)
So does this not give you what you need?
--DROP TABLE #WorkTable;
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY i_ticket_id ORDER BY dt_when)
,i_transaction_id
,c_amount
,dt_when = CONVERT(DATETIME,dt_when)
,i_ticket_id
,s_credit_auth
,s_credit_tran_type
,b_cancel
INTO #WorkTable
FROM ( --=== This simulates the result from your query as an input to the temp table.
SELECT 4478,16.10,'2015-01-06 15:44:17.733',58475,'ALL IN ONE AUTO' ,'House Account', 1 UNION ALL
SELECT 4479,25.55,'2015-01-06 15:45:11.060',58476,'ADCO SHEET METAL','House Account', 1 UNION ALL
SELECT 4480,11.50,'2015-01-06 15:48:21.367',58475,'ALL IN ONE AUTO' ,'House Account', 0 --UNION ALL
--SELECT 4481,10.00,'2015-01-06 15:50:33.123',58475,'ALL IN ONE AUTO' ,'House Account', 0
)d(i_transaction_id,c_amount,dt_when,i_ticket_id,s_credit_auth,s_credit_tran_type,b_cancel)
;
SelectROW_NUMBER() OVER (PARTITION BY i_ticket_id ORDER BY i_ticket_id) Rowno,
i_ticket_id,
sum(case when b_cancel=1 then c_amount else 0 end) Amount,
sum(case when b_cancel=0 then c_amount else 0 end) CancelledAmt,
sum(case when b_cancel=1 then c_amount else 0 end) -
sum(case when b_cancel=0 then c_amount else 0 end) NewBalance,
s_credit_auth,
s_credit_tran_type
from #WorkTable
Group By i_ticket_id
,s_credit_auth
,s_credit_tran_type

No, the FROM part is hard coded (which would never work)....my data is query'd....from this code, which gives the first output.

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

steve.tarry SSC Enthusiast Points: 132 More actions · Answer 8

You will obviously need to adjust to use your table. I just don't have access to your data hence why it's hard coded. It's the bit underneath which sums up the values based on the cancel entity and then reports the adjustments by ticket id.

chef423 SSCertifiable Points: 5279 More actions · Answer 9

chef423

SSCertifiable

Points: 5279

January 15, 2015 at 4:05 pm

#1770589

Understood...thanks!

chef423 SSCertifiable Points: 5279 More actions · Answer 10

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

SelectROW_NUMBER() OVER (PARTITION BY i_ticket_id ORDER BY i_ticket_id) Rowno,

i_ticket_id,

sum(case when b_cancel=1 then c_amount else 0 end) -

sum(case when b_cancel=0 then c_amount else 0 end) Credit,

s_credit_auth,

s_credit_tran_type

FROM dbo.Transactions AS t INNER JOIN

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

WHERE s_credit_tran_type = 'HOUSE ACCOUNT' 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 i_ticket_id

,s_credit_auth

,s_credit_tran_type

This code has me super duper close! But I cannot see why ADCO SHEET METAL is coming up? There was no b_cancel = 1 ticket for that House Account...

Rownoi_ticket_idCredit s_credit_auth s_credit_tran_type

1 58475 4.60 ALL IN ONE AUTO House Account

1 58476 -25.55 ADCO SHEET METAL House Account

One other small caveat..the return (credit) will come into the POS system from the Cashier...then my initial query for only the previous work week executes for House Account charges, BUT if there is a b_cancel = 1, I need that refunded Ticket to look as far back as possible in the Transaction Table...not just for the previous week.

steve.tarry SSC Enthusiast Points: 132 More actions · Answer 11

try using CTE e.g.

with WeeklyQuery ( Select <your weekly query> )

,cancel_1_Query as ( Select <previous transactions> From weeklyquery

where cancel = 1 And date < weekly_startdate and ticket_id in (select ticket_id from WeeklyQuery))

Select * From WeeklyQuery left join Cancel_1_Query

I don't know what you need to know about the entire transactions but this would bring back all details for tickets within your weekly date range.

Or you could do this in all in one query but linking back to the transaction table (aliased) and picking the date less than the weekly start date

more than one way to skin a cat so to speak.

chef423 SSCertifiable Points: 5279 More actions · Answer 12

If I proved the database (.bak) file can someone assist here?

I still cannot get this to work. And I am past my deadline...

The problem is the language not my knowledge of the database.

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

chef423 (1/27/2015)
If I proved the database (.bak) file can someone assist here?
I still cannot get this to work. And I am past my deadline...
The problem is the language not my knowledge of the database.
Thanks.

Unless you scrub the database removing or anonymizing all personally identifying information, I would not provide a copy of the database.

What would help is the DDL (CREATE TABLE statements) for the table(s) involved, sample data representative of the problem domain (i.e. not production data) in the form of INSERT INTO statement(s) for the table(s) involved, and the expected results based on the sample data (this could also be done as INSERT INTO statements to an expected results table).

chef423 SSCertifiable Points: 5279 More actions · Answer 14

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

chef423 (1/27/2015)
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.

Please take the time to read the first article I have referenced below in my signature block. What you have posted isn't what I requested. Posting a picture of the column names (incomplete if you look closely) and data types is not posting the CREATE TABLE statement to create a table. Same with the sample data. Maybe someone else has the time to write all the SQL code to setup the environment, I don't where I currently work.

My asking for the expected results in a separate table does not mean we are going to write to a separate table. It provides us with a ready way to check our results to see if we got what you expected.