Select price by date between two dates

Giuseppe88 · 01-16-2022 03:19 AM

Hello
I hope you can help me, I am developing a booking system for my business.

I have created an EXCURSION DATA table where I enter the excursion name, the date a price starts from, the date where the price ends and the price.
Prices vary per month.

in the EXCURSION table I enter the reservation data and the reservation date.

I would like to obtain the excursion price between the dates of the EXCURSION DATA table.

I tried two formulas:

[NUMERO PASSEGGERI]*
SELECT(
DATI ESCURSIONI[PREZZO ADULTI],
AND([_THISROW].[ESCURSIONE]=[ESCURSIONE],
[DATA ESCURSIONE]>=DATI ESCURSIONI[DATA INIZIO],
[DATA ESCURSIONE]<=DATI ESCURSIONI[DATA FINE]
)

)

IF(
AND([_THISROW].[ESCURSIONE]=[ESCURSIONE],
[DATA ESCURSIONE]>=DATI ESCURSIONI[DATA INIZIO],
[DATA ESCURSIONE]<=DATI ESCURSIONI[DATA FINE]
),
[NUMERO PASSEGGERI]*DATI ESCURSIONI[PREZZO ADULTI],
“”)

the result is always:
Cannot compare Date with List in ([DATA ESCURSIONE] >= DATI ESCURSIONI[DATA INIZIO])

can you help me?

Suvrutt_Gurjar

The above expression will result into a list. The error is because a list is compared with [DATA ESCURSIONE] , a single value.

If the “DATI ESCURSION” is a single data row table, you could convert the list into a single value by using ANY() such that ANY(DATI ESCURSIONI[DATA INIZIO]). If it is a multirow data table, you will need to use SELECT() with ANY() go get the desired column value from the desired row.

Giuseppe88

Thanks for the reply
I tried but the result is a list with all the prices. I have to select only 1 price ([PREZZO ADULTI]) between the dates [DATA INIZIO] E [DATA FINE]

how can I do?

Giuseppe88

Goodmorning

i have try this:
SELECT(
DATI ESCURSIONI[PREZZO ADULTI],
AND([_THISROW].[ESCURSIONE]=[ESCURSIONE],

[DATA ESCURSIONE] >= ANY(SELECT( DATI ESCURSIONI[DATA INIZIO],
[DATA ESCURSIONE]>=ANY(DATI ESCURSIONI[DATA INIZIO]))),

[DATA ESCURSIONE] <= ANY(SELECT( DATI ESCURSIONI[DATA FINE],
[DATA ESCURSIONE]<=ANY(DATI ESCURSIONI[DATA FINE])))
))

but the restult is always all the valum of the column [PREZZO ADULTI]

Suvrutt_Gurjar

Could you update what row’s result exactly you are expecting? Somehow your requirement is not clear? Could you please explain without referring expressions?

Giuseppe88

Thanks for the reply
I only want to get an adult price for the date range and for the same escursione

example if the date is today the price 70 as in the attachment

Suvrutt_Gurjar

Oh okay , thank you. [Data INIZIO] and [Data FINE] are date ranges. But when you say say TODAY(), Where this comparison date field will come from? Is it in the same table or different table?

Or do you want the [PREZZO ADULTI] always with respect to today’s date where today is between [Data INIZIO] and [Data FINE]?

Giuseppe88

The date is selected in another table and is chosen by the user

Ex if user selects the date 15/08/2022 the price is 100

Suvrutt_Gurjar

Okay, thank you. is the other table a single row table or multi row table, meaning is there obly one row in the other table for the user or are there one row for each user in the other table to store each user’s selections?

Also ould you update from where the [ESCURSIONE] field will come? Will the user select it?

Giuseppe88

in the other table there is a row for each reservation with all the reservation data

ESCURSIONE is an enum field from the table with the booking data, the user will select it among several with different prices per period

Suvrutt_Gurjar

Thank you. I am afraid, your data structure is yet not clear. It is not clear from which table you are getting all the search data and which table you need to lookup for that data and the which table the column contains the expression you are trying to build.

In table “DATI ESCURSIONI”, the column ESCURSIONE is a ref type column and in the the other table it is TEXT type column. Any specific reason why it is populating ref ID in one column and text , maybe label value in the other?

Giuseppe88

Hello
my structure is this:
in the INSERIMENTO ESCURSIONI table I insert the name of the various ESCURSIONI services to have the excursion id

the second table: TABELLA PREZZI I insert the prices per period of the multiple ESCURSIONI services

the third table ESCURSIONI in this register the various customer bookings and I should get the price from the price table in relation to the date and service

I hope to be clear
I have to get the price of the service [PREZZO ADULTiI from the price table when selecting the type of excursion and the date in the excursion table.

Suvrutt_Gurjar

Thank you for all the details. I think still the details mentioned do not tie up. For example the second table name you have mentioned as TABELLA PREZZI but in the table picture from the editor it is DATI ESCURSIONI. In the third table ESCURSIONI , the column ESCURSIONE is a ref type column but it seems to be populating label values instead of key values.

Due to above descripancies, the expression suggested may need a change. Also SELECT() expressions are sync expensive, so you may wish to reevaluate your data model, for example shifting prices to ESCURSIONI table along with applicable dates for those prices.

Please try below

ANY(
SELECT(
DATI ESCURSIONI[PREZZO ADULTI],

AND([_THISROW].[ESCURSIONE]=[ESCURSIONE],

[_THISROW].[DATA ESCURSIONE] >=[DATA INIZIO],
[_THISROW].[DATA ESCURSIONE]<=[DATA FINE]))
)

Giuseppe88

Perfect ,now it works thank you very much you have been a true friend.
thanks thanks thanks