I'm fairly new in SQL and am wanting a query.
I have a table that might look like this
Store Order Date Transaction Type Amount
1 12345 10/29/17 1 $0
1 66666 10/30/17 1 $10
1 66666 10/30/17 2 -$10
I'm trying to sum the results for a given store/order/date combination and then state whether or not there was a '2' transaction type (refund). So I'm looking for results like this:
Store Order Date Amount Refund
1 12345 10/29/17 $0 No
1 66666 10/30/17 $0 Yes
So I believe to get the sum I can to a sum() over partition by using the store/order/date to ignore the transaction type but where I don't understand is how I can make a 'yes'/'no' type result if a '2' exists or not. Here a '2' means there was a refund and a '1' means there was a sale. So really for any amount that sums to zero I'm trying to figure out if it was sold for zero or whether there was a refund that has made it zero. (though I realize if it was first sold for zero and refunded the results will make it appear the refund caused the zero). The date doesn't mean the refund date so while refunds might occur on a different day it is still aligned under the same date as the sale.
I don't completely know the data so I didn't want to count the number of orders and then assume whever the count was greater than 1 there must have been a refund involved. I guess I could make an initial quey without the transaction type then join it to a sub-query that looks for only transaction type = '2' and if it exists then return a true/false type response but there are millions of records and I was looking for the most efficient way. Any thoughts on the best way to accomplish this?
Solved! Go to Solution.
Select Store, Order, Date,
Sum(Amount) over( partition by Store, Order, Date ) as Amount
CASE Max(Transaction Type) over( partition by Store, Order, Date )
When 1 then 'No' Else 'Yes' END as Refund
Perfect, after looking through some of the results and anomolies like values that also have refunds, I think this works nicely!