Timeline for Is there a more efficient way to run this query for first/max transaction?
Current License: CC BY-SA 3.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 27, 2014 at 1:50 | history | edited | Thomas Kejser | CC BY-SA 3.0 |
Modified based on edge cases described in comments
|
| Aug 27, 2014 at 1:46 | comment | added | Thomas Kejser | @Nicholai: I made the modification as it was rather quick. This returns the desired result (?) of 365 days. However, in the case where there is only one transaction for the account Ex: ('QD3', 2011-01-01, 42) it returns NULL in the interval length. If the desired result is 0 instead, just put a COALESCE in there. | |
| Aug 27, 2014 at 1:45 | history | edited | Thomas Kejser | CC BY-SA 3.0 |
Modified based on edge cases described in comments
|
| Aug 27, 2014 at 1:34 | comment | added | Thomas Kejser | @Nicholai: I see where you are coming from. You want the interval to be the shortest one between the first transaction and the highest amount right? In other words, if there is more than one record with the highest amount, you want the smaller of the intervals? I will look at the query again in the morning and modify it to work like that. | |
| Aug 26, 2014 at 18:44 | comment | added | Nicholai | Thanks Thomas; however, I still find cases using those three sample records where the wrong date is pulled (returning 731 for Days_Passed). To duplicate, try inserting the 2012 record first, then 2013, then 2011. We can't guarantee the order of evaluation of the OR operator, or the order in which it will search records. Replacing the OR with a CASE in the WHERE clause will let me set order of evaluation and hope that the earliest gift comes first, but even that doesn't guarantee the order in which records will be searched. | |
| Aug 26, 2014 at 13:46 | history | edited | Thomas Kejser | CC BY-SA 3.0 |
added 28 characters in body
|
| Aug 26, 2014 at 13:41 | comment | added | Thomas Kejser | A simple modification (done now) will fix that in the query above. | |
| Aug 26, 2014 at 13:40 | history | edited | Thomas Kejser | CC BY-SA 3.0 |
Updated based on comment
|
| Aug 26, 2014 at 12:19 | comment | added | Nicholai | A person can have multiple transactions, so a buying pattern like ('QD1','2011-01-01', 20), ('QD1','2012-01-01', 100), ('QD1','2013-01-01', 100) returns 3 results. It should return 1 result of ('QD1', 365, 100). I will speak with the vendor to see about the possibility of adding indexes; it's a kind of legacy export procedure that creates new tables each time. | |
| Aug 26, 2014 at 10:56 | history | edited | Thomas Kejser | CC BY-SA 3.0 |
Prettifying the answer
|
| Aug 26, 2014 at 10:48 | history | answered | Thomas Kejser | CC BY-SA 3.0 |