Forum Discussion
DATEDIFF across twotables
Hi chris_staines,
From your description, I could not undestand the sntence "if it is possible to select the first outcome *after* the transaction date", if I selecet the first data like the picture below (2018/1/25), the related tranaction data should be with the Item name? What about the second date(the second 2018/1/25) shoud be related to which dat? Could you please post your desired result if possible?
Regards,
Daniel He
Hello Daniel,
I realise my description wasn't ideal, please let me try again.
What I would like to do is report against all items in the transactions table, and where an outcome exists for that item.
For example if Item 1 has had 3 transactions, but only 2 outcomes from those transactions I would expect to see something like this:
Item | Creation Date | Transaction Type |Transaction Date | Outcome Date | Outcome
I00000001 | 01/01/2018 | Trans A | 16/01/2018 | 25/01/2018 | POS
I00000001 | 01/01/2018 | Trans B | 22/03/2018 | 02/01/2018 | NEG
I00000001 | 01/01/2018 | Trans C | 01/06/2018 | 25/06/2018 |
Now the problem is, there is no obvious way to relate the transaction date to the outcome date as there is no transaction ID recorded in the outcome table, so I hoped there may be a way to summarize the data for an item so the first outcome date that occurs AFTER the current transaction date is displayed.
Does that make any more sense?
Thanks again for looking at my question.
Regards,
Chris