Problema conteggio intervallo date

[simo2508]

Salve ,
ho un problema riguardante il calcolo dei prezzi per strutture in diversi periodi dell'anno. Il mio obbiettivo è : inserite le due date ; arrivo e partenza , effettuare un conteggio dei giorni e riuscire a capire in quali periodi cascano e quanti giorni sono trascorsi in ogni periodo per poter effettuare correttamente il calcolo del prezzo per quella determinata struttura .
Le mie tabelle sono così strutturate :

CREATE TABLE `PERIODI` (
  `id_periodo` int(11) NOT NULL,
  `data_inizio` date NOT NULL,
  `data_fine` date NOT NULL,
  `descrizione` varchar(50) NOT NULL,
  PRIMARY KEY  (`id_periodo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `PREZZI` (
  `id_periodo` int(11) NOT NULL default '0',
  `id_struttura` int(11) NOT NULL default '0',
  `prezzo_giornaliero` float default NULL,
  `descrizione` varchar(500) NOT NULL,
  PRIMARY KEY  (`id_periodo`,`id_struttura`),
  KEY `id_struttura` (`id_struttura`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `STRUTTURA` (
  `id_struttura` int(11) NOT NULL auto_increment,
  `tipologia` varchar(50) NOT NULL,
  `numeroposti` int(11) NOT NULL,
  `climatizzato` varchar(10) NOT NULL,
  `descrizione` varchar(1000) NOT NULL,
  PRIMARY KEY  (`id_struttura`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

Grazie per l'aiuto .

 

[marino51]

ti va di fare qualche prova con la query che segue ?
è "in linea" con la tabella "periodi" che hai postato, se ti "ritrovi" possiamo fare altri passi,
ciao
Marino

$sql = "
declare @data_arrivo   AS datetime;
declare @data_partenza AS datetime;
set @data_arrivo   = '".$data_arrivo."';
set @data_partenza = '".$data_partenza."';
SELECT * FROM (
SELECT
  1 AS tipo
, id_periodo
, @data_arrivo AS data_arrivo
, @data_partenza AS data_partenza
, data_inizio
, data_fine
, DATEDIFF(@data_arrivo, @data_partenza) AS giorni
  FROM PERIODI
 WHERE @data_arrivo>=data_inizio AND @data_partenza<=data_fine
UNION 
SELECT
  2 AS tipo
, id_periodo
, @data_arrivo AS data_arrivo
, @data_partenza AS data_partenza
, data_inizio
, data_fine
, 1 + DATEDIFF(@data_arrivo, data_fine) AS giorni
  FROM PERIODI
 WHERE @data_arrivo BETWEEN data_inizio AND data_fine
   AND @data_partenza NOT BETWEEN data_inizio AND data_fine
UNION 
SELECT
  3 AS tipo
, id_periodo
, @data_arrivo AS data_arrivo
, @data_partenza AS data_partenza
, data_inizio
, data_fine
, DATEDIFF(data_inizio, @data_partenza) AS giorni
  FROM PERIODI
 WHERE @data_partenza>=data_inizio AND @data_partenza<=data_fine
   AND @data_arrivo NOT BETWEEN data_inizio AND data_fine
UNION 
SELECT
  4 AS tipo
, id_periodo
, @data_arrivo AS data_arrivo
, @data_partenza AS data_partenza
, data_inizio
, data_fine
, 1 + DATEDIFF(data_inizio, data_fine) AS giorni
  FROM PERIODI
 WHERE @data_arrivo<data_inizio AND @data_partenza>data_fine
) a ORDER BY a.data_inizio;
";

ps. nella tabella periodi manca il codice della struttura, quindi non puoi differenziare i periodi come differenzi i prezzi

il risultato di un esempio

 

[marino51]

ho scritto una query che può essere usata facilmente sia in phpmyadmin che in uno script php

in particolare i parametri esterni di ricerca sono
la data di arrivo
la data di partenza


essendo le due date, richiamate molte volte nelle select, le ho definite come parametri
(attenzione alle punteggiature presenti) con le seguenti dichiarazioni,

declare @data_arrivo   AS datetime;
declare @data_partenza AS datetime;

ora assegno alle due date il loro valore, lo faccio una volta sola con le seguenti set

set @data_arrivo   = '2014/03/15';	( set @data_arrivo   = '".$data_arrivo."'; )
set @data_partenza = '2014/04/14';	( set @data_partenza = '".$data_partenza."'; )

vi è poi una select "esterna" che serve a gestire i i risultati delle select "interne"
quando sei sicuro che il metodo proposto funziona, va modificata per ottenere i valori sommati

SELECT * FROM (
....
) a ORDER BY a.data_inizio;

le select "interne", sono identificate da una colonna "tipo" che può assumere i valori 1, 2, 3 o 4
questi valori permettono di capire quali select hanno "lavorato"
perchè le select sono specifiche per questi 4 casi,

1 arrivo e partenza interni ad un periodo ( giorni = @data_partenza - @data_arrivo )
2 arrivo nel periodo, partenza in un periodo successivo ( giorni = data_fine - @data_arrivo )
3 partenza nel periodo, arrivo in un periodo precedente ( giorni = @data_partenza - data_inizio )
4 arrivo e partenza debordano il periodo ( giorni = data_fine - data_inizio )

quindi ciascuna select conta i giorni con le regole sopra esposte
( la somma dei giorni ottenuti da ciascuna select è pari al periodo di soggiorno complessivo )

SELECT
  1 AS tipo		// arrivo e partenza interni ad un periodo
....
, DATEDIFF(@data_arrivo, @data_partenza) AS giorni
  FROM PERIODI
 WHERE @data_arrivo>=data_inizio AND @data_partenza<=data_fine

SELECT
  2 AS tipo		// arrivo nel periodo, partenza in un periodo successivo
....
, 1 + DATEDIFF(@data_arrivo, data_fine) AS giorni
  FROM PERIODI
 WHERE @data_arrivo BETWEEN data_inizio AND data_fine
   AND @data_partenza NOT BETWEEN data_inizio AND data_fine

SELECT
  3 AS tipo		// partenza nel periodo, arrivo in un periodo precedente
....
, DATEDIFF(data_inizio, @data_partenza) AS giorni
  FROM PERIODI
 WHERE @data_partenza>=data_inizio AND @data_partenza<=data_fine
   AND @data_arrivo NOT BETWEEN data_inizio AND data_fine

SELECT
  4 AS tipo		// arrivo e partenza debordano il periodo
....
, 1 + DATEDIFF(data_inizio, data_fine) AS giorni
  FROM PERIODI
 WHERE @data_arrivo<data_inizio AND @data_partenza>data_fine

se hai capito le 4 select, dedurrai che la numero 1 è alternativa alle altre
perchè se il soggiorno è all'interno di un periodo, non possono sussistere gli altri casi

con "UNION" otteniamo un unico set con i record selezionati, che sarà poi lavorato dalla select "esterna" come detto

ti consiglio di fare casi di prova, prendendo familiarità con il metodo,
cercando anche di "estremizzare" con le date di arrivo partenza coincidenti con le date di inizio e fine del periodo

se c'è altro da spiegare, fammi sapere,
ciao
Marino