Forum Discussion

Frequent Visitor

7 years ago

Difference between dates by earliest date

Hi everyone, Could someone please help me with this: I’m creating a report in which I would like to show the difference in days between the date a product has become available for customers a...

Community Support

7 years ago

Hi JamieLee,

Could you show your expected result or clarify more details about your requirement?

Regards,

Jimmy Tao

JamieLee
Frequent Visitor
7 years ago
Hi Jimmy,

Thanks for your reaction. The expected result would look like the 3rd column 'DaysTillOrder' in the Products table:

Products
ProductID CreationDate DaysTillOrder
001 01/01/2018 2
002 01/01/2018 blank
003 13/04/2018 blank
004 07/06/2018 31

Orders
OrderID ProductID OrderDate
021 001 03/01/2018
022 004 05/07/2018
023 001 02/08/2018
024 001 25/08/2018

Product 001, was ordered several times as we can see in the Order table, but the earliest order date was 03/01/2018, so only two days after the CreationDate in the Products table. This is why DaysTillOrder = 2.
Product 002 and 003 have not been ordered, so there is no data to retrieve from the Order table. This returns a blank (or a value like 'not applicable').
Product 004 was ordered slightly over a month after its creation date and is thus returning the value of 31 days.

Hope this clarifies?

Kind regards,

JamieLee