April 11, 2011 at 12:45 pm
Hi there,
I'm kind of new on this and I was wondering if you could help me.
I have a trable with sales and annulations.
fields: transactiontype and transactionStatus
Transacion Type
3 = Sale
Transacion status value
1 = Sale,
3 = Canceled Sale
Example: When someone makes a sale and then cancele the sale the table recieves first the sale then the annulation (2 rows). One with the value 1 and the other with value 3.
Row TransactionStatus TransactionType Value
1 1 3 1,15
2 1 3 2,35
3 3 3 2,35
4 1 3 1,15
5 1 3 1,15
6 1 3 5,30
7 3 3 5,30
8 1 3 5,30
I need to retrieve the sales (positive values) and for that first I need to merge the sales with the annulations. After merge it into one row then I need to set the amount to 0 (zero).
To get something like this:
1 1 3 1,15
2/3 1 3 0
4 1 3 1,15
5 1 3 1,15
6/7 1 3 0
8 1 3 5,30
How can I do it?
All the fields are exaclty the same except the Hour of the transaction and of course the transactionstatus field.
Thanks,
Ana
April 11, 2011 at 12:57 pm
do you have a transaction ID that is a common link between the cancel and the sale. If so than this is rather easy. IF not then it is fairly impossible. With out an identifier to create a join from the cancel to the sale then any query you would right would be based on assumption at best. you can;t go solely based on amount as I am sure it is possible for the amount to repeat in any given business day.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 11, 2011 at 1:09 pm
The common field available is the clienteID. Is it possible to do it with the ClientID?
April 11, 2011 at 1:33 pm
will every sale be unique based on Client ID?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 11, 2011 at 1:39 pm
Not an efficient solution, but does what you requested for
; WITH CTE AS
(
SELECT T1.Row
, T1.TransactionStatus
, T1.TransactionType
, CASE WHEN OutApp.TransactionStatus = 3 THEN 0
ELSE T1.VALUE
END Value
FROM #Tempo T1
OUTER APPLY
(SELECT TransactionStatus
FROM #Tempo T2
WHERE T2.ROW = T1.ROW + 1
) OutApp
)
SELECT Row
, TransactionStatus
, TransactionType
, Value
FROM CTE
WHERE CTE.TransactionStatus <> 3
April 11, 2011 at 1:44 pm
If your answer is YES for Dan's question, you might want to add AND T1.CilentID = T2.ClientID along with the T1.ROW = T2.Row+1 clause afterOUTER APPLY statement.
Hope that helps 🙂
April 11, 2011 at 2:47 pm
Yes there will be only one transaction (sale) per Client, at least by month. For example this month the client Y will have a transaction and on the next month this same client (Y) will have another transaction. But I suppose I can filter by date. (using date parameter)
Thank you two for your reply and explanation. I'm going to try the script tomorrow, I'm out of work right now. 🙂
Thanks again,
Ana
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply