Realizzazione query con vincolo all'interno

martedì 10 aprile 2012 - 10.01

trinity Profilo | Guru

Salve ragazzi e buona Pasqua fatta,

allora ho una tabella movimenti in sql server devo realizzare una query che mi seleziona solo i movimenti che hanno massimo 7 giorni di presenza nella struttura in base al periodo di ricerca che gli passo.
Vi faccio degli esempio e carico dei dati.

Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

allora in base ai dati passati devo realizzare una query che mi prende solo i record che hanno massimo 7 giorni di presenza. La presenza ovviamente si calcola in base alla data di arrivo e a quella di partenza, dove la data è nulla si prende come riferimento la seconda data che passo nella where di selezione ossia se dovessi scegliere il periodo dal 1 gennaio al 31 la data di riferimento sarebbe ovviamente il 31 gennaio.
Detto questo il risultato che dovrei avere in base al periodo 1 Gennaio - 31 Gennaio è il seguente:

Rossi Mario presenze=2
Bianchi Luca presenze=6

non verrebbero presi Giallo Marco perchè le sue presenze sarebbero 31 e Nero Luigi perchè le sue presenze sarebbero 16.

Ecco non riesco a capire come dare il vincolo di selezionare massimo i record che hanno al massimo (scusate il gioco di parole) 7 presenze come totale.

Mi date una mano?

:)

Ciao e grazie

Cirillo Fabio
www.wondernet.biz
fabio@wondernet.biz
http://blogs.dotnethell.it/fabiocirillo/
http://wnetsoftware.blogspot.com

boccia75 Profilo | Junior Member

Ciao!
Ho solo una domanda da farti:
nel caso in cui una persona arrivasse in un giorno compreso nell'intervallo che hai indicato e se ne andasse dopo, ma rimarrebbe nel range di date che hai indicato (quindi <=7 giorni) andrebbe considerato?
Ok faccio un esempio che è piu' semplice...
Se la persona "Pallo Luca" arriva in albergo il giorno 25/01 e se ne va il giorno 13/04, questa persona risulterebbe con 6 presenze nel range di date 01/01-31/01, ma sarebbe rimasto piu' di 7 giorni.
In questo caso va conteggiato?
Io ho considerato di conteggiarlo e questa è la query che è venuta fuori:
le due date le passi come parametro

declare @start varchar(8)
declare @end varchar(8)
set @start='20120101'
set @end='20120131'

select codice_albergo,codice_scheda,cognome,Nome,
DATEDIFF("dd", data_arrivo, case when convert(varchar(8),data_partenza,112)<@end then data_partenza
else @end end )
from tab_alloggiati
where
convert(varchar(8),isnull(data_arrivo,@end),112) between @start and @end
group by codice_albergo,codice_scheda,cognome,Nome,
DATEDIFF("dd", data_arrivo, case when convert(varchar(8),data_partenza,112)<@end then data_partenza
else @end end )

having DATEDIFF("dd", data_arrivo, case when convert(varchar(8),data_partenza,112)<@end then data_partenza
else @end end )<=7


Con il tuo esempio questo è il risultato:

1 1 Rossi Mario 2
1 4 Bianchi Luca 6


Se poi aggiungi anche l'utente Pallo Luca in questo modo

Insert Into tab_alloggiati(codice_albergo, codice_scheda,data_arrivo,data_partenza,cognome,nome) Values (1,5,'25/01/2012','13/04/2012','Pallo','Luca')

ed esegui la query otterrai:

1 1 Rossi Mario 2
1 4 Bianchi Luca 6
1 5 Pallo Luca 6


Ciao!

trinity Profilo | Guru

Allora giustamente ho mancato di precisare questa cosa grazie di avermelo fatto notare...se esiste il caso del tuo esempio la persona arriva a gennaio ma parte ad aprile allora come data provvisoria di partenza va presa l'ultima che si indica nella ricerca ossia 31 gennaio pertanto se arriva il 25 gennaio e parte il 10 aprile come calcolo della presenza il range di date sarà 25 gennaio - 31 gennaio quindi rientrata perchè sono 6 giorni.

Ciao e grazie
Cirillo Fabio
www.wondernet.biz
fabio@wondernet.biz
http://blogs.dotnethell.it/fabiocirillo/
http://wnetsoftware.blogspot.com

boccia75 Profilo | Junior Member

E allora direi che ci siamo proprio....
Provala e fammi sapere.
Ciao!

trinity Profilo | Guru

Funziona perfettamente :D l'unica cosa che manca e che non avevo ricordato la regola impone dove ci sono record con presenze =0 perchè la data di partenza è uguale alla data di arrivo e quindi giustamente il datediff lo calcola come zero in quanto non ci sono giorni di differenza, io comunque in questo caso devo calcolarlo come una presenza. Pertanto dove devo inserire questo vincolo?

Ciao
Cirillo Fabio
www.wondernet.biz
fabio@wondernet.biz
http://blogs.dotnethell.it/fabiocirillo/
http://wnetsoftware.blogspot.com

boccia75 Profilo | Junior Member

Allora....dopo essermi ingarbugliato, ecco la risposta.
Se una persona che arriva il giorno 01/01 e se ne va il giorno 01/01 ha una presenza, una persona che arriva il giorno 01/01 e se ne va il giorno 02/01 ha 2 presenze.
Una persona che arriva il giorno 25/01 e va via il giorno 31/01 (o successivo) ha 7 presenze ( e non 6 come detto prima).
Quindi basta solamente aggiungere 1 al DATADIFF

declare @start varchar(8)
declare @end varchar(8)
set @start='20120101'
set @end='20120131'

select codice_albergo,codice_scheda,cognome,Nome,
DATEDIFF("dd", data_arrivo, case when convert(varchar(8),data_partenza,112)<@end then data_partenza
else @end end )+1
from tab_alloggiati
where
convert(varchar(8),isnull(data_arrivo,@end),112) between @start and @end
group by codice_albergo,codice_scheda,cognome,Nome,
DATEDIFF("dd", data_arrivo, case when convert(varchar(8),data_partenza,112)<@end then data_partenza
else @end end )

having DATEDIFF("dd", data_arrivo, case when convert(varchar(8),data_partenza,112)<@end then data_partenza
else @end end )<=7

In questo modo Bianchi avrà 7 presenze, mentre chi è arrivato e se n'è andato nello stesso giorno ne avrà 1.
Ciao.

trinity Profilo | Guru

Ciao scusa se ti disturbo ancora ma ho trovato un anomalia nel mio codice quello che mi avevi aiutato a fare ti posto il codice:


Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

in pratica mi succede che se ho un arrivo supponiamo il 01/04/2012 ma nessuna partenza, il numero di presenze che deve essere massimo considerato è pari a 7 non di più ovviamente ti faccio degli esempi:

arrivo 01/04/2012 partenza =NUll --------------quindi presenze 7
arrivo 01/04/2012 partenza 13/04/2012-------presenze 7
arrivo 25/04/2012 partenza null ---------------presenze 6

in quanto le presenze vanno calcolate in base alle notti che il clienti resta nella struttura.

Dove sbaglio in quanto nel mio db c'è un record di prova con questi dati: arrivo 11/04/2012 partenza null e mi segna 19 presenze.

Ciao e grazie

Cirillo Fabio
www.wondernet.biz
fabio@wondernet.biz
http://blogs.dotnethell.it/fabiocirillo/
http://wnetsoftware.blogspot.com

boccia75 Profilo | Junior Member

Mi puoi postare nuovamente struttura e righe di inserimento, che faccio una prova?
ciao

trinity Profilo | Guru

Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

allora stando ai dati che ti ho inserito abbiamo quanto segue:

la scheda nr. 1 ha 4 presenze (in quanto le presenze come detto si calcolano in base alle notti effettive di presenza quindi il 5 mattina parte e di conseguenza non viene calcolato)
la scheda nr. 2 ha 7 presenze (in quanto la calcolando con la data di partenza supera il totale di 7 presenze e quindi bisogna riportare il massimo consentito nel calcolo dell'imposta ossia come detto 7)
la scheda nr. 3 ha 7 presenze
la scheda nr. 4 ha 6 presenze

adesso questa selezione si esegue sempre per mese quindi il range di selezione nel caso postato è dal 01/04/2012 al 30/04/2012 compreso

spero di esserti stato utile e grazie mille


Cirillo Fabio
www.wondernet.biz
fabio@wondernet.biz
http://blogs.dotnethell.it/fabiocirillo/
http://wnetsoftware.blogspot.com

boccia75 Profilo | Junior Member

Ma allora quello che cerchi non sono le schede(persone) che hanno soggiornato al max 7 giorni, ma vuoi visualizzare qualunue scheda che abbia soggiornato nell'albergo e, qualora abbia soggiornato piu' di 7 giorni, visualizzare il numero 7 per le presenze, corretto?

In questo caso è inutile la clausola having, io farei in questo modo (questa è la select relativa alla insert che hai postato):

select allog.codice_albergo,
alb.descrizione,
alb.indirizzo,
categoria.descrizione as Categoria,
comune.descrizione as Comune,
provincia.descrizione as Provincia,
Sum(case when DATEDIFF("dd", data_arrivo, case when convert(varchar(8),data_partenza,112)<@end then data_partenza
else @end end)=0
then 1 else


(case when DATEDIFF("dd", data_arrivo, case when convert(varchar(8),(case when data_partenza='1900-01-01' then NULL else data_partenza END),112)<@end then data_partenza
else @end end)>7 then 7 else
DATEDIFF("dd", data_arrivo, case when convert(varchar(8),(case when data_partenza='1900-01-01' then NULL else data_partenza END),112)<@end then data_partenza
else @end end) END) end)

as Presenze,
allog.codice_scheda,
imposta.ImportoTassa,
Sum(case when DATEDIFF("dd", data_arrivo, case when convert(varchar(8),(case when data_partenza='1900-01-01' then NULL else data_partenza END),112)<@end then data_partenza
else @end end)=0
then 1 * imposta.importotassa else
DATEDIFF("dd", data_arrivo, case when convert(varchar(8),(case when data_partenza='1900-01-01' then NULL else data_partenza END),112)<@end then data_partenza
else @end end) * imposta.importotassa END) as ImpostaSoggiorno
from tab_alloggiati as allog
Join
tab_alberghi as alb
On allog.codice_albergo=alb.codice
Join
tab_comuni as comune
On Alb.comune_codice=comune.codice
Join
tab_provincie as provincia
On Alb.provincia_codice=provincia.codice
Join
tab_categorie as categoria
On Alb.categoria_codice=categoria.codice
Join
tab_imposta_soggiorno as imposta
On categoria.codice_istat=imposta.idcategoria
where
convert(varchar(8),isnull(data_arrivo,@end),112) between @start and @end
group by
allog.codice_albergo,
alb.descrizione,
alb.indirizzo,
categoria.descrizione,
comune.descrizione,
provincia.descrizione,
allog.codice_scheda,
imposta.importotassa


e questo è il set di risultati ottenuto (sono solo i campi relativi alla tabella tab_alloggiati, le altre non le avevo):
codice_albergo Presenze codice_scheda
1 4 1
1 7 2
1 7 3
1 5 4
1 7 5

Le schede sono quelle inserite con le query che mi hai postato.
La scheda 5 è stata alimentata in questo modo (l'esempio che avevi postato):
Insert into tab_alloggiati (codice_albergo,codice_scheda,data_arrivo,data_partenza,cognome,nome) Values (1,5,'11/04/2012',Null,'Rossi','Mario')


Spero di esserti stato utile.
Ciao!

trinity Profilo | Guru

Allora il tuo codice funziona benissimo ma ti devo chiedere un'ultimissima cosa...in pratica mi hanno detto che la tabella su cui devo applicare questa stored è composta anche da altri 2 campi in quanto nella struttura (albergo) possono arriva ospiti singoli ma anche famiglie o gruppi e pertanto il calcolo dell'imposta deve essere ordinato per queste categorie, ti posto un esempio di dati e la nuova struttura della tabella:

Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

in pratica ti spiego questa cosa...
allora esistono 5 categorie di clienti:
ospite singolo
capo famiglia
familiare
capo gruppo
membro

queste categorie sono rappresentate dalla colonna tipo_alloggiato e precisamente quanto segue:

ospite singolo=1
capo famiglia=2
familiare=4
capo gruppo=3
membro=5

adesso questi fenomeni che hanno creato questa tabella che mi tocca gestire ora a me hanno messo nella tabella la colonna tipo_alloggiato poi il progressivo della scheda e poi per collegare i capo famiglia e campo gruppo ai loro rispettivi componenti hanno messo la colonna appartenenza, pertanto se avremo una cosa del genere:

Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

Io nella query che mi hai postato devo ottenere tutti i record ordinati per ospite singolo e poi i capo famiglia con di seguito i loro famigliari e se ci fossero i capo gruppo con a seguito i loro membri.

Spero di essere stato chiaro e preciso nel farti capire in che situazione mi trovo..a mio avviso se avessi fatto io il db lo avrei studiato in modo diverso ma purtroppo molta gente noto che non sa fare l'analisi.

Ciao e grazie ancora

Cirillo Fabio
www.wondernet.biz
fabio@wondernet.biz
http://blogs.dotnethell.it/fabiocirillo/
http://wnetsoftware.blogspot.com

boccia75 Profilo | Junior Member

Ciao!
ho capito il "porcaro" che c'è....pero' non ho capito quale set di risultati vuoi ottenere.
non capisco il significato di questa frase:
"Io nella query che mi hai postato devo ottenere tutti i record ordinati per ospite singolo e poi i capo famiglia con di seguito i loro famigliari e se ci fossero i capo gruppo con a seguito i loro membri."
Mi fai un esempio (dai dati di prova che mi hai passato) del set di risultati che vuoi ottenere?
Ciao!

trinity Profilo | Guru

Prima di tutto scusami tanto che ti sto facendo perdere del tempo ma almeno imparo cose nuove ;) e comunque ho scritto prima delle insert errate. Comunque in base a questi dati che ti posto:

Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

Adesso può succedere che un familiare o membro di un gruppo venga inserito anche successivamente quindi per collegare i componenti ai loro campo famiglia o campo gruppo si deve prendere in esame la colonna appartenenza nella quale viene salvato il numero della scheda del capo famiglia o capo gruppo. Tanto è vero che nell'esempio c'è una famiglia ed il capo famiglia ha come appartenenza sempre = 0 mentre i familiari hanno come appartenenza = 2 ossia il numero della scheda del campo famiglia.

quindi nella tua select deve essere effettuata in base a questi criteri appena citati ovviamente non ho incluso gli altri campi che stano nella select perchè li ci penso io a gestire il tutto.

Ciao e grazie



Cirillo Fabio
www.wondernet.biz
fabio@wondernet.biz
http://blogs.dotnethell.it/fabiocirillo/
http://wnetsoftware.blogspot.com

boccia75 Profilo | Junior Member

Ciao!
Mmhhh non so se ti perdono per il tempo che mi stai facendo perdere.....
tranquillo, se non avevo voglia di risponderti non lo facevo...
Allora, ho bisogno di sapere qualcosa di piu'....

Tu dici
"in pratica vorrei che vorrei il set risultati prima di tutto ordinato per tipo_alloggiato e poi per data di arrivo nel senso prima tutti gli ospiti singoli,
poi le famiglie e poi i gruppi..." : ok ma la data di arrivo che ci azzecca? o meglio, occorre ordinare anche sulla data?

Se non occorre ordinare sulla data, per ottenere quello che chiedi dal set di dati che hai postato, basta solamente mettere una order by allog.codice_albergo,allog.tipo_alloggiato,allog.appartenenza

e questo è il set di risultati ottenuto:
N.Cogn. albergo scheda presenze cod_scheda tipo_allogg appartenenza
Rossi Mario 1 2 1 1 0
Giallo Marco 1 7 2 2 0
Nero Luigi 1 7 3 4 2
Bianchi Luca 1 6 4 4 2


Pero' voglio farti una domanda:
qualora 2 capifamiglia (/ capigruppo, sono la stessa cosa, vero?) arrivassero insieme e poi arrivassero gli altri membri, come bisognerebbe ordinarli?
faccio un esempio (tralascio le date di arrivo/partenza e la sintassi SQL)

Insert Into tab_alloggiati (cognome,scheda,appartenenza,tipo_alloggiato) values Rossi,1,0,2
Insert Into tab_alloggiati (cognome,scheda,appartenenza,tipo_alloggiato) values Bianchi,2,0,2
Insert Into tab_alloggiati (cognome,scheda,appartenenza,tipo_alloggiato) values di_Bianchi_1,3,2,4
Insert Into tab_alloggiati (cognome,scheda,appartenenza,tipo_alloggiato) values di_Bianchi_2,4,2,4
Insert Into tab_alloggiati (cognome,scheda,appartenenza,tipo_alloggiato) values di_Bianchi_3,5,2,4
Insert Into tab_alloggiati (cognome,scheda,appartenenza,tipo_alloggiato) values di_Rossi_1,6,1,4
Insert Into tab_alloggiati (cognome,scheda,appartenenza,tipo_alloggiato) values di_Rossi_2,7,1,4

In pratica è arrivato per primo il capofamiglia Rossi, poi il capo Bianchi poi i 3 familiari di Bianchi e poi i due di Rossi.

In questo caso, quale risultato vorresti ottenere?


Ciao!

trinity Profilo | Guru

ecco questa tua domanda è l'esempio che volevo farti ma che mi è sfuggito in pratica bisogna ordinarli per appartenenza ossia come risultato dovrà uscire il capofamiglia e poi subito i suoi famigliari poi il capogruppo ed i suoi membri....
Cirillo Fabio
www.wondernet.biz
fabio@wondernet.biz
http://blogs.dotnethell.it/fabiocirillo/
http://wnetsoftware.blogspot.com

boccia75 Profilo | Junior Member

Ciao!
Questa è la query partorita...(mi fa schifo, pero' di meglio non ho trovato....)
Ho commentato tutta la parte relativa alle tabelle che non avevo.
Le query di insert sono queste (le date sono tutte uguali, non era quello che interessava, ma l'ordinamento):
Insert Into tab_alloggiati(codice_albergo, codice_scheda,data_arrivo,data_partenza,cognome,nome,appartenenza,tipo_alloggiato) Values (1,1,'01/01/2012',Null,'Giallo','capofamiglia',0,2)
Insert Into tab_alloggiati(codice_albergo, codice_scheda,data_arrivo,data_partenza,cognome,nome,appartenenza,tipo_alloggiato) Values (1,2,'01/01/2012',Null,'Rosso','capofamiglia',0,2)
Insert Into tab_alloggiati(codice_albergo, codice_scheda,data_arrivo,data_partenza,cognome,nome,appartenenza,tipo_alloggiato) Values (1,3,'01/01/2012',Null,'Rosso','Rosso1',2,4)
Insert Into tab_alloggiati(codice_albergo, codice_scheda,data_arrivo,data_partenza,cognome,nome,appartenenza,tipo_alloggiato) Values (1,4,'01/01/2012',Null,'Giallo','Giallo1',1,4)
Insert Into tab_alloggiati(codice_albergo, codice_scheda,data_arrivo,data_partenza,cognome,nome,appartenenza,tipo_alloggiato) Values (1,5,'01/01/2012',Null,'Giallo','Giallo2',1,4)
Insert Into tab_alloggiati(codice_albergo, codice_scheda,data_arrivo,data_partenza,cognome,nome,appartenenza,tipo_alloggiato) Values (1,6,'01/01/2012',Null,'Rosso','Rosso2',2,4)
Insert Into tab_alloggiati(codice_albergo, codice_scheda,data_arrivo,data_partenza,cognome,nome,appartenenza,tipo_alloggiato) Values (1,7,'01/01/2012',Null,'Giallo','Giallo3',1,4)

Questa è la query:

declare @start varchar(8)
declare @end varchar(8)
set @start='20120101'
set @end='20120131'

select allog.codice_albergo,
allog.codice_scheda,
allog.tipo_alloggiato,
allog.appartenenza,
allog.nome,
allog.cognome,
--alb.descrizione,
--alb.indirizzo,
--categoria.descrizione as Categoria,
--comune.descrizione as Comune,
--provincia.descrizione as Provincia,
Sum(case when DATEDIFF("dd", data_arrivo, case when convert(varchar(8),data_partenza,112)<@end then data_partenza
else @end end)=0
then 1 else


(case when DATEDIFF("dd", data_arrivo, case when convert(varchar(8),(case when data_partenza='1900-01-01' then NULL else data_partenza END),112)<@end then data_partenza
else @end end)>7 then 7 else
DATEDIFF("dd", data_arrivo, case when convert(varchar(8),(case when data_partenza='1900-01-01' then NULL else data_partenza END),112)<@end then data_partenza
else @end end) END) end)

as Presenze
--imposta.ImportoTassa,
--Sum(case when DATEDIFF("dd", data_arrivo, case when convert(varchar(8),(case when data_partenza='1900-01-01' then NULL else data_partenza END),112)<@end then data_partenza
--else @end end)=0
--then 1 * imposta.importotassa else
--DATEDIFF("dd", data_arrivo, case when convert(varchar(8),(case when data_partenza='1900-01-01' then NULL else data_partenza END),112)<@end then data_partenza
--else @end end) * imposta.importotassa END) as ImpostaSoggiorno
from tab_alloggiati as allog
--Join
--tab_alberghi as alb
--On allog.codice_albergo=alb.codice
--Join
--tab_comuni as comune
--On Alb.comune_codice=comune.codice
--Join
--tab_provincie as provincia
--On Alb.provincia_codice=provincia.codice
--Join
--tab_categorie as categoria
--On Alb.categoria_codice=categoria.codice
--Join
--tab_imposta_soggiorno as imposta
--On categoria.codice_istat=imposta.idcategoria
where
convert(varchar(8),isnull(data_arrivo,@end),112) between @start and @end
group by
allog.codice_albergo,
--alb.descrizione,
--alb.indirizzo,
--categoria.descrizione,
--comune.descrizione,
--provincia.descrizione,
allog.codice_scheda,
--imposta.importotassa
allog.tipo_alloggiato,
allog.nome,
allog.cognome,
allog.appartenenza

order by
case when convert(varchar(1),appartenenza)='0' then convert(varchar(1),codice_scheda) else
convert(varchar(1),appartenenza)+(
case when codice_scheda>9 then left(convert(varchar(100),codice_scheda),1) else convert(varchar(1),codice_scheda) end) end,
cognome,nome


E questo il risultato:


albergo scheda tipo_alloggiato appartenenza nome cognome presenze
1 1 2 0 capofamiglia Giallo 7
1 4 4 1 Giallo1 Giallo 7
1 5 4 1 Giallo2 Giallo 7
1 7 4 1 Giallo3 Giallo 7
1 2 2 0 capofamiglia Rosso 7
1 3 4 2 Rosso1 Rosso 7
1 6 4 2 Rosso2 Rosso 7

Così dovrebbe andare.
Ciao!

trinity Profilo | Guru

Grazie mille funziona :D

spero solo di concludere questo argomento....grazie ancora
Cirillo Fabio
www.wondernet.biz
fabio@wondernet.biz
http://blogs.dotnethell.it/fabiocirillo/
http://wnetsoftware.blogspot.com
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2017
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5