RETRIVE price based TIPO and PREZZO

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

RETRIVE price based TIPO and PREZZO

Post by sal21 »

With the join TIPO>TIPO and between from DAL AL in PRENOTAZIONI.
NRGG is the numbers of reserved days

note:
STRUTTURA is MARE
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: RETRIVE price based TIPO and PREZZO

Post by HansV »

Have you tried creating a query, and if so, what was the problem?
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: RETRIVE price based TIPO and PREZZO

Post by sal21 »

HansV wrote:
22 Jun 2021, 18:35
Have you tried creating a query, and if so, what was the problem?
between dates

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: RETRIVE price based TIPO and PREZZO

Post by HansV »

What was the query that you tried?
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: RETRIVE price based TIPO and PREZZO

Post by sal21 »

HansV wrote:
22 Jun 2021, 18:53
What was the query that you tried?

SELECT PREZZI.IDTIP, PREZZI.TIPO, PREZZI.PREZZO
FROM PRENOTAZIONI INNER JOIN PREZZI ON PRENOTAZIONI.TIPO = PREZZI.TIPO
WHERE PRENOTAZIONI.IDCLI=16

but,
1) intercept price based DAL to AL in PRENOTAZIONI
2) if date DAL to AL of PRENOTAZIONE, are in multi range in PREZZI in DAL and AL?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: RETRIVE price based TIPO and PREZZO

Post by HansV »

That's going to be complicated because of 2).
I'd need to see a sample database.
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: RETRIVE price based TIPO and PREZZO

Post by sal21 »

HansV wrote:
22 Jun 2021, 20:02
That's going to be complicated because of 2).
I'd need to see a sample database.
I can send in pvt?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: RETRIVE price based TIPO and PREZZO

Post by HansV »

Sure.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: RETRIVE price based TIPO and PREZZO

Post by HansV »

I'll work on it later today.
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: RETRIVE price based TIPO and PREZZO

Post by sal21 »

HansV wrote:
23 Jun 2021, 07:37
I'll work on it later today.
no prob

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: RETRIVE price based TIPO and PREZZO

Post by HansV »

You can use

SELECT PRENOTAZIONI.IDCLI, PRENOTAZIONI.IDTIP, PRENOTAZIONI.TIPO, PREZZI.STRUTTURA, PRENOTAZIONI.DAL, PRENOTAZIONI.AL, IIf([PRENOTAZIONI].[DAL]>[PREZZI].[DAL],[PRENOTAZIONI].[DAL],[PREZZI].[DAL]) AS PREZZO_DAL, IIf([PRENOTAZIONI].[AL]<[PREZZI].[AL],[PRENOTAZIONI].[AL],[PREZZI].[AL]) AS PREZZO_AL, PREZZI.PREZZO
FROM PRENOTAZIONI INNER JOIN PREZZI ON PRENOTAZIONI.TIPO = PREZZI.TIPO
WHERE (((PREZZI.STRUTTURA)="MARE") AND ((PRENOTAZIONI.DAL)<=[PREZZI].[AL]) AND ((PRENOTAZIONI.AL)>=[PREZZI].[DAL]))
ORDER BY PRENOTAZIONI.IDCLI, PRENOTAZIONI.IDTIP, IIf([PRENOTAZIONI].[DAL]>[PREZZI].[DAL],[PRENOTAZIONI].[DAL],[PREZZI].[DAL]);
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: RETRIVE price based TIPO and PREZZO

Post by sal21 »

HansV wrote:
23 Jun 2021, 10:20
You can use

SELECT PRENOTAZIONI.IDCLI, PRENOTAZIONI.IDTIP, PRENOTAZIONI.TIPO, PREZZI.STRUTTURA, PRENOTAZIONI.DAL, PRENOTAZIONI.AL, IIf([PRENOTAZIONI].[DAL]>[PREZZI].[DAL],[PRENOTAZIONI].[DAL],[PREZZI].[DAL]) AS PREZZO_DAL, IIf([PRENOTAZIONI].[AL]<[PREZZI].[AL],[PRENOTAZIONI].[AL],[PREZZI].[AL]) AS PREZZO_AL, PREZZI.PREZZO
FROM PRENOTAZIONI INNER JOIN PREZZI ON PRENOTAZIONI.TIPO = PREZZI.TIPO
WHERE (((PREZZI.STRUTTURA)="MARE") AND ((PRENOTAZIONI.DAL)<=[PREZZI].[AL]) AND ((PRENOTAZIONI.AL)>=[PREZZI].[DAL]))
ORDER BY PRENOTAZIONI.IDCLI, PRENOTAZIONI.IDTIP, IIf([PRENOTAZIONI].[DAL]>[PREZZI].[DAL],[PRENOTAZIONI].[DAL],[PREZZI].[DAL]);
:clapping:

...HUMMMM
But in PRENOTAZIONI have

IDTIP TIPO DAL AL MM AAAA NRGG AGG IDCLI CANCL DCANCL PAX
10 SINGOLA 15/06/2021 31/08/2021 6 2021 7 17/06/2021 15 17/06/2021 1

have a different price, one in range from 01/06/2021 to 31/07/2021 80,00 and one in range from 01/08/2021 31/08/2021 95,00

???

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: RETRIVE price based TIPO and PREZZO

Post by sal21 »

sal21 wrote:
23 Jun 2021, 11:51
HansV wrote:
23 Jun 2021, 10:20
You can use

SELECT PRENOTAZIONI.IDCLI, PRENOTAZIONI.IDTIP, PRENOTAZIONI.TIPO, PREZZI.STRUTTURA, PRENOTAZIONI.DAL, PRENOTAZIONI.AL, IIf([PRENOTAZIONI].[DAL]>[PREZZI].[DAL],[PRENOTAZIONI].[DAL],[PREZZI].[DAL]) AS PREZZO_DAL, IIf([PRENOTAZIONI].[AL]<[PREZZI].[AL],[PRENOTAZIONI].[AL],[PREZZI].[AL]) AS PREZZO_AL, PREZZI.PREZZO
FROM PRENOTAZIONI INNER JOIN PREZZI ON PRENOTAZIONI.TIPO = PREZZI.TIPO
WHERE (((PREZZI.STRUTTURA)="MARE") AND ((PRENOTAZIONI.DAL)<=[PREZZI].[AL]) AND ((PRENOTAZIONI.AL)>=[PREZZI].[DAL]))
ORDER BY PRENOTAZIONI.IDCLI, PRENOTAZIONI.IDTIP, IIf([PRENOTAZIONI].[DAL]>[PREZZI].[DAL],[PRENOTAZIONI].[DAL],[PREZZI].[DAL]);
:clapping:

...HUMMMM
But this query consider if in PRENOTAZIONI have

IDTIP TIPO DAL AL MM AAAA NRGG AGG IDCLI CANCL DCANCL PAX
10 SINGOLA 15/06/2021 31/08/2021 6 2021 7 17/06/2021 15 17/06/2021 1

have a different price, one in range from 01/06/2021 to 31/07/2021 80,00 and one in range from 01/08/2021 31/08/2021 95,00

???

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: RETRIVE price based TIPO and PREZZO

Post by HansV »

Is this a question, or a remark, or a complaint or ...?
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: RETRIVE price based TIPO and PREZZO

Post by sal21 »

remark / dubt...

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: RETRIVE price based TIPO and PREZZO

Post by HansV »

Does the query not do what you want? Please try to be specific.
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: RETRIVE price based TIPO and PREZZO

Post by sal21 »

HansV wrote:
23 Jun 2021, 13:36
Does the query not do what you want? Please try to be specific.
i have see with attention, the query work!

i have changed this line:
IDTIP TIPO DAL AL MM AAAA NRGG AGG IDCLI CANCL DCANCL PAX
11 SINGOLA 15/06/2021 21/09/2021 6 2021 7 20/06/2021 16 1

and the result is:
IDCLI IDTIP TIPO STRUTTURA DAL AL PREZZO_DAL PREZZO_AL PREZZO
16 11 SINGOLA MARE 15/06/2021 21/09/2021 15/06/2021 31/07/2021 80,00
16 11 SINGOLA MARE 15/06/2021 21/09/2021 01/08/2021 31/08/2021 95,00
16 11 SINGOLA MARE 15/06/2021 21/09/2021 01/09/2021 21/09/2021 70,00

ok but if i want to multiply the real total price for each step of dates, i need to calculate via code, or possible via query?
for example:

16 11 SINGOLA MARE 15/06/2021 21/09/2021 15/06/2021 31/07/2021 80,00 * number of days from 15/06/2021 to 31/07/2021=???
16 11 SINGOLA MARE 15/06/2021 21/09/2021 01/08/2021 31/08/2021 95,00 * number of days from 01/08/2021 to 31/08/2021=???
16 11 SINGOLA MARE 15/06/2021 21/09/2021 01/09/2021 21/09/2021 70,00 * number of days from 01/09/2021 to 21/09/2021=???

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: RETRIVE price based TIPO and PREZZO

Post by HansV »

In the query design, add this column:

PREZZO_TOTALE: [PREZZO]*([PREZZO_AL]-[PREZZO_DAL]+1)
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: RETRIVE price based TIPO and PREZZO

Post by sal21 »

HansV wrote:
23 Jun 2021, 14:28
In the query design, add this column:

PREZZO_TOTALE: [PREZZO]*([PREZZO_AL]-[PREZZO_DAL]+1)
:cheers: :cheers: :cheers: :cheers:

OPS...
i need to calculate with dataediff the number of days in code or possible to have in SQL?
For example:

16 11 SINGOLA MARE 15/06/2021 21/09/2021 15/06/2021 31/07/2021 80,00 * 45 = tot
16 11 SINGOLA MARE 15/06/2021 21/09/2021 01/08/2021 31/08/2021 95,00 * 31 = tot
.....
ecc...

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: RETRIVE price based TIPO and PREZZO

Post by HansV »

the number of days is [PREZZO_AL]-[PREZZO_DAL]+1
Best wishes,
Hans