February 12, 2009 at 3:48 pm
I'm writing a query for a receipt table. Whenever a return is processed, the current system inserts another record with the exact same information except with a negative amount and another entry stating which row entry is it's parent.
Now the statement I'm trying to write is for a report that shows which entries for the day have been reverted and which have not. The problem is in the Grouping. Right now this is what I've come up with:
SELECT R.QuoteId, R.ReferenceNumber, R.CustomerName, CASE WHEN COUNT(*) > 1 THEN 'Reverted' ELSE 'SALE' END AS SaleType FROM Receipt R GROUP BY R.QuoteId, R.ReferenceNumber, R.CustomerName
I want to select the original total amount into the query, however if I add that extra column then I have to add it to the Group clause. If I add it to the group clause I'll get two separate entries which breaks my SaleType column.
Any help would be appreciated.
Thanks in advance.
February 12, 2009 at 3:56 pm
You don't state what your total amout column name is, but assuming it is TotalAmount then does the following work?
SELECT R.QuoteId,
R.ReferenceNumber,
R.CustomerName,
MAX(R.TotalAmount),
CASE WHEN COUNT(*) > 1 THEN 'Reverted' ELSE 'SALE' END AS SaleType
FROM Receipt R
GROUP BY R.QuoteId, R.ReferenceNumber, R.CustomerName
February 12, 2009 at 4:07 pm
Now I've tried using MAX and it works great. But unfortunately we have payments entered that start out being negative. If I use MAX I can't tell the difference between a payment that was entered as a sale or initially entered as a refund. Soo close though.
February 12, 2009 at 4:16 pm
I'm writing a query for a receipt table. Whenever a return is processed, the current system inserts another record with the exact same information except with a negative amount and another entry stating which row entry is it's parent.
The other entry which references its parent row:
What is the name of this column?
Does it reference QuoteId?
February 12, 2009 at 4:30 pm
andrewd.smith (2/12/2009)
I'm writing a query for a receipt table. Whenever a return is processed, the current system inserts another record with the exact same information except with a negative amount and another entry stating which row entry is it's parent.
The other entry which references its parent row:
What is the name of this column?
Does it reference QuoteId?
ParentId references it's parent on the column named Id which is a standard auto incrementing PK
February 12, 2009 at 4:40 pm
OK, will this work?
I've assumed that the ParentId column is NULL if the row is the original entry for that quote
SELECT R.QuoteId, R.ReferenceNumber, R.CustomerName, R.TotalAmount,
SaleType = CASE WHEN EXISTS (
SELECT * FROM Receipt RR WHERE (RR.ParentId = R.Id) AND (RR.TotalAmount = -R.TotalAmount)
) THEN 'Reverted SALE' ELSE 'SALE' END
FROM Receipt R
WHERE (R.TotalAmount > 0)
AND (R.ParentId IS NULL)
UNION ALL
SELECT
R.QuoteId, R.ReferenceNumber, R.CustomerName, R.TotalAmount,
SaleType = CASE WHEN EXISTS (
SELECT * FROM Receipt RR WHERE (RR.ParentId = R.Id) AND (RR.TotalAmount = -R.TotalAmount)
) THEN 'Reverted REFUND' ELSE 'REFUND' END
FROM Receipt R
WHERE (R.TotalAmount < 0)
AND (R.ParentId IS NULL)
I haven't tested the query against any test data.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply