January 8, 2015 at 4:54 pm
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
This SQL code above Pulls any House Account charges for a customer and I export those into Quickbooks for them. Ther reference is the Ticket ID...
Now I need to help them with the paper trail when Items are returned and a refund is due. They reopen the Ticket...and remove the items from the historical ticket, then they close the ticket.
i_transaction_idc_amount dt_when i_ticket_id s_credit_auth s_credit_tran_type b_cancel
4478 16.102015-01-06 15:44:17.73358475ALL IN ONE AUTO House Account 1
4479 25.552015-01-06 15:45:11.06058476ADCO SHEET METAL House Account 0
4480 11.502015-01-06 15:48:21.36758475ALL IN ONE AUTO House Account 0
Above is an example of the House Account credit...as you can see, Ticket ID 58475 is equal to Ticket 58475 with a difference of 4.60
So I need to put that into code that SQL can understand...
If i_ticket_id1 = i_ticket_id2 and dt_when(i_ticket_id2) > dt_when(i_ticket_id1) and i_ticket_id1(b_cancel = 1) then c_amount1 - c_amount2
Please help I have been attempting this for 2 weeks now on my own with no luck, Google, no help...
Thank you again!
January 8, 2015 at 5:53 pm
Look into using CASE/WHEN/ELSE/END.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 11, 2015 at 2:22 pm
Any other help, please?
January 11, 2015 at 2:30 pm
I have no idea what this is trying to do:
If i_ticket_id1 = i_ticket_id2 and dt_when(i_ticket_id2) > dt_when(i_ticket_id1) and i_ticket_id1(b_cancel = 1) then c_amount1 - c_amount2
No idea what I_ticket_id1 or I_ticket_id2 represents.
What is this: dt_when(i_ticket_id2) > dt_when(i_ticket_id1).
And this: i_ticket_id1(b_cancel = 1).
January 11, 2015 at 2:33 pm
hmmm, that's why I put the output of the above code in the thread...
No worries...Ill figure it out on my own then.
If you don't understand the original code then there is no chance you can assist with my solution.
January 11, 2015 at 2:45 pm
chef423 (1/11/2015)
hmmm, that's why I put the output of the above code in the thread...No worries...Ill figure it out on my own then.
If you don't understand the original code then there is no chance you can assist with my solution.
O yea of little faith. I can't help you if you can't provide a clear and concise explanation of what you are working on.
January 11, 2015 at 4:11 pm
chef423 (1/11/2015)
hmmm, that's why I put the output of the above code in the thread...No worries...Ill figure it out on my own then.
If you don't understand the original code then there is no chance you can assist with my solution.
There is nothing complex in the code itself, the complexity comes when deciphering the question with the sparse information provided. Dwain has already suggested a way to solve the problem, did you look into that?
Here is an example of how such a solution could look like (given the information provided)
😎
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
,CASE
WHEN i_ticket_id1 = i_ticket_id2 and dt_when(i_ticket_id2) > dt_when(i_ticket_id1) and i_ticket_id1(b_cancel = 1) then c_amount1 - c_amount2
ELSE c_amount1
END AS TheHouseAccountCredit
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;
January 11, 2015 at 4:22 pm
Ok sorry...
So, the original code above pulls sales from Tickets in a POC that have a customer name attached to them, call a 'House Account'
So they needed a better way to 'credit' these accounts when something it returned to them for a refund....now they are having to pass a receipt to their CPA and shes not happy with that work flow...
When a credit is issued, the Cashier re-opens the original Guest Check (i_ticket_ID) and then simply removes the item/s from the ticket and then closes it...the system then cancels (b_cancel) the original ticket (i_ticket_id) and gives a new value (c_amount) for that Ticket ID.
So I need to write the code, in SQL, to recognize, when an older ticket ID is re-opened and something is removed, the new balance will end up showing a credit...so if the original ticket was $20.00 and you remove $5..new balance is !5.00 BUT I need the output to actually be the difference (-5.00)
This is all used in Quickbooks...and for now the original SQL code outputs a .CSV file that QB uses to import all the house account charges...so they want to remove the paper trail of a refund for House Account Ticket and let the .CSV file handle that (-5.00)
Hope I made that clear enough...
Thank you!
January 11, 2015 at 5:29 pm
Eirikur Eiriksson (1/11/2015)
chef423 (1/11/2015)
hmmm, that's why I put the output of the above code in the thread...No worries...Ill figure it out on my own then.
If you don't understand the original code then there is no chance you can assist with my solution.
There is nothing complex in the code itself, the complexity comes when deciphering the question with the sparse information provided. Dwain has already suggested a way to solve the problem, did you look into that?
Here is an example of how such a solution could look like (given the information provided)
😎
SELECTc.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
,CASE
WHEN i_ticket_id1 = i_ticket_id2 and dt_when(i_ticket_id2) > dt_when(i_ticket_id1) and i_ticket_id1(b_cancel = 1) then c_amount1 - c_amount2
ELSE c_amount1
END AS TheHouseAccountCredit
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;
Credits to Navy beans says that there is no i_ticket_id1 or i_ticket_id2 columns in any of the tables.
--Jeff Moden
January 11, 2015 at 5:37 pm
How do you want to handle 58475 when the following happens?
i_transaction_id c_amount dt_when i_ticket_id s_credit_auth s_credit_tran_type b_cancel
---------------- -------- ----------------------- ----------- ---------------- ------------------ --------
4478 16.10 2015-01-06 15:44:17.733 58475 ALL IN ONE AUTO House Account 1
4479 25.55 2015-01-06 15:45:11.060 58476 ADCO SHEET METAL House Account 0
4480 11.50 2015-01-06 15:48:21.367 58475 ALL IN ONE AUTO House Account 1
4481 12.00 2015-01-06 15:50:33.123 58475 ALL IN ONE AUTO House Account 0
(4 row(s) affected)
--Jeff Moden
January 11, 2015 at 9:20 pm
Credits to Navy beans says that there is no i_ticket_id1 or i_ticket_id2 columns in any of the tables.
That was my interpretation of the same [i_ticket_id] but in different instances.
January 11, 2015 at 9:22 pm
Jeff Moden (1/11/2015)
@chef423,How do you want to handle 58475 when the following happens?
i_transaction_id c_amount dt_when i_ticket_id s_credit_auth s_credit_tran_type b_cancel
---------------- -------- ----------------------- ----------- ---------------- ------------------ --------
4478 16.10 2015-01-06 15:44:17.733 58475 ALL IN ONE AUTO House Account 1
4479 25.55 2015-01-06 15:45:11.060 58476 ADCO SHEET METAL House Account 0
4480 11.50 2015-01-06 15:48:21.367 58475 ALL IN ONE AUTO House Account 1
4481 12.00 2015-01-06 15:50:33.123 58475 ALL IN ONE AUTO House Account 0
(4 row(s) affected)
Well, the ticket, when re-opened can only remove/deduct items (c_amount) not increment them. If they want more goods, the cashier will start a new Guest Check (ticket).
Thanks Jeff, I really appreciate the time you are taking here.
January 11, 2015 at 9:22 pm
Yep... that's why I was willing to put up credits to Navy beans.:-D
See my previous post where I ask a question on what happens if...
--Jeff Moden
January 11, 2015 at 9:24 pm
chef423 (1/11/2015)
Jeff Moden (1/11/2015)
@chef423,How do you want to handle 58475 when the following happens?
i_transaction_id c_amount dt_when i_ticket_id s_credit_auth s_credit_tran_type b_cancel
---------------- -------- ----------------------- ----------- ---------------- ------------------ --------
4478 16.10 2015-01-06 15:44:17.733 58475 ALL IN ONE AUTO House Account 1
4479 25.55 2015-01-06 15:45:11.060 58476 ADCO SHEET METAL House Account 0
4480 11.50 2015-01-06 15:48:21.367 58475 ALL IN ONE AUTO House Account 1
4481 12.00 2015-01-06 15:50:33.123 58475 ALL IN ONE AUTO House Account 0
(4 row(s) affected)
Well, the ticket, when re-opened can only remove/deduct items (c_amount) not increment them. If they want more goods, the cashier will start a new Guest Check (ticket).
Thanks Jeff, I really appreciate the time you are taking here.
Ok... in that case, what about for this...
i_transaction_id c_amount dt_when i_ticket_id s_credit_auth s_credit_tran_type b_cancel
---------------- -------- ----------------------- ----------- ---------------- ------------------ --------
4478 16.10 2015-01-06 15:44:17.733 58475 ALL IN ONE AUTO House Account 1
4479 25.55 2015-01-06 15:45:11.060 58476 ADCO SHEET METAL House Account 0
4480 11.50 2015-01-06 15:48:21.367 58475 ALL IN ONE AUTO House Account 1
4481 10.00 2015-01-06 15:50:33.123 58475 ALL IN ONE AUTO House Account 0
(4 row(s) affected)
--Jeff Moden
January 11, 2015 at 9:29 pm
Jeff Moden (1/11/2015)
chef423 (1/11/2015)
Jeff Moden (1/11/2015)
@chef423,How do you want to handle 58475 when the following happens?
i_transaction_id c_amount dt_when i_ticket_id s_credit_auth s_credit_tran_type b_cancel
---------------- -------- ----------------------- ----------- ---------------- ------------------ --------
4478 16.10 2015-01-06 15:44:17.733 58475 ALL IN ONE AUTO House Account 1
4479 25.55 2015-01-06 15:45:11.060 58476 ADCO SHEET METAL House Account 0
4480 11.50 2015-01-06 15:48:21.367 58475 ALL IN ONE AUTO House Account 1
4481 12.00 2015-01-06 15:50:33.123 58475 ALL IN ONE AUTO House Account 0
(4 row(s) affected)
Well, the ticket, when re-opened can only remove/deduct items (c_amount) not increment them. If they want more goods, the cashier will start a new Guest Check (ticket).
Thanks Jeff, I really appreciate the time you are taking here.
Ok... in that case, what about for this...
i_transaction_id c_amount dt_when i_ticket_id s_credit_auth s_credit_tran_type b_cancel
---------------- -------- ----------------------- ----------- ---------------- ------------------ --------
4478 16.10 2015-01-06 15:44:17.733 58475 ALL IN ONE AUTO House Account 1
4479 25.55 2015-01-06 15:45:11.060 58476 ADCO SHEET METAL House Account 0
4480 11.50 2015-01-06 15:48:21.367 58475 ALL IN ONE AUTO House Account 1
4481 10.00 2015-01-06 15:50:33.123 58475 ALL IN ONE AUTO House Account 0
(4 row(s) affected)
The new addition to the .csv file should show (-1.50) as a credit in Quickbooks.
So same output in the original code expect the c_amount would be (-1.50) for the ALL IN ONE AUTO house account...
The ticket ID plus the CAST of CH (that is the location code) is the House Charge 'invoice' number...so CH58475 would be a line item for a credit of a (1.50)
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply