topic Sort List by Date from and Date to in App Development

Sort List by Date from and Date to

wunderch — Thu, 15 May 2025 19:15:31 GMT

Hi guys,

I have a table like this:

product	date_from	date_to	price
A	18.02.2025	31.12.9999	89,99
A	08.03.2024	31.12.9999	79,99
A	29.01.2024	17.02.2024	69,99
A	12.01.2024	31.12.9999	75,99
A	21.11.2023	31.12.9999	84,99

and i want to sort the table like this:

product	date_from	date_to	price
A	18.02.2025	31.12.9999	89,99
A	08.03.2024	17.02.2025	79,99
A	18.02.2024	07.03.2024	75,99
A	29.01.2024	17.02.2024	69,99
A	12.01.2024	28.01.2024	75,99
A	21.11.2023	11.01.2024	84,99

Can anybody help me?

Thanks

Regards

Chris

Re: Sort List by Date from and Date to

PrashantSangle — Fri, 16 May 2025 04:59:42 GMT

what is the logic for 3rd line

A 18.02.2024 07.03.2024 75,99

Regards,

Prashant Sangle

Re: Sort List by Date from and Date to

Vegar — Fri, 16 May 2025 05:00:09 GMT

Can you try to put words to the logic you want? That is often helpful when trying to find a solution.

My first impression of your query is that you will need to do some kind of comparison and select between the date_to value and the date_from value on the row above.

Helpful function to consider are peek(), rangemin()/rangemax() and maybe coalesce()/alt().

Re: Sort List by Date from and Date to

wunderch — Fri, 16 May 2025 09:46:46 GMT

Sorry for my quick question. Here is my logic:

Yes, I would like to put the sentences into a time series by comparing the date from and the date to.

To get the "date_to" from the next "date_from-1" is not the challenge. I get this with peek().

My problem is how can i get the additional row "18.02.2024" - "07.03.2024" because the row from "29.01.2024" is only valid to 17.02.2024 and the next row is only valid from 08.03.2024.
So i have to create a row with the last row 12.01.2024.

I hope you unterstand my challenge?

Re: Sort List by Date from and Date to

wunderch — Sat, 17 May 2025 14:28:00 GMT

Sorry for my quick question. Here is my logic:

Yes, I would like to put the sentences into a time series by comparing the date from and the date to.

To get the "date_to" from the next "date_from-1" is not the challenge. I get this with peek().

I hope you unterstand my challenge?

Re: Sort List by Date from and Date to

PrashantSangle — Mon, 19 May 2025 06:43:52 GMT

& what is logic for price?

Re: Sort List by Date from and Date to

wunderch — Mon, 19 May 2025 07:54:22 GMT

It's actually the same logic.

Take the last valid price.

In this case, it's the rate from January 12, 2024, because it's valid until infinity, and the next price is only valid from March 8, 2024.

Re: Sort List by Date from and Date to

marcus_sommer — Wed, 21 May 2025 11:54:46 GMT

You need to apply the interrecord-stuff with peek() and previous() twice. The first one checked if a real and valid to-date exists and if this fits to it's previous from-date - if not you set a flag-value of 2 and otherwise it's 1.

The next step is performing a while-loop, like:

load * resident while iterno() <= MyFlag;

which adds an extra record for those records which have a date-gap and then the final from-to data are evaluated per interrecord-functions.

Re: Sort List by Date from and Date to

wunderch — Wed, 21 May 2025 14:16:57 GMT

Thank you for the answer.

I understand the logic, but unfortunately I can't convert it into the correct code.

Can you please give me an example of what the code should look like?

Re: Sort List by Date from and Date to

marcus_sommer — Wed, 21 May 2025 15:17:51 GMT

It may go in this direction whereby you may need some more if-loops especially for the extra records and/or further adjustments, for example the date-offset between the normal and extra records.

load *, if(product = previous(product), previous(from) - 1, ...) as to2;
load * while iterno() <= flag;
load *, if(product = previous(product) and to < '31.12.9999' and previous(from) - to > 1, 2, 1) as flag
resident X order by product, from desc;