Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

1 ACCEPTED SOLUTION

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

Try:

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

From ...

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

Perfect, after looking through some of the results and anomolies like values that also have refunds, I think this works nicely!