ciao ragazzi,
chiedo un vostro consiglio.
Ho questa tabella sql
supponiamo di avere i seguenti record:
allora ecco io devo creare una query di selezione che mi prende tutti i giorni del mese di gennaio ma potrebbero essere anche altri mesi, comunque sempre un mese alla volta, e visualizzare per ogni giorno il numero di arrivi e partenze e presenza senza calcolare l'idprovenienza.
Nel caso dei dati postati il risultato sarebbe:
data - arrivi - partenze - presenze
01/01/2015 - 3 - 0 - 3
02/01/2015 - 0 - 0 - 3
03/01/2015 - 2 - 0 - 5
04/01/2015 - 3 - 0 - 8
07/01/2015 - 0 - 1 - 7
ho creato questa stored pare che in base a dei dati reali del db funziona, potete vedere se la sintassi si può sistemare o migliorare per aumentare le prestazioni ossia la velocità di elaborazione?
CREATE PROCEDURE [dbo].[sp_get_movimenti_mese]
(
@idstruttura int,
@fromdata date,
@todata date
)
AS
DECLARE @codstruttura Int
DECLARE @arrivo date
DECLARE @partenza date
DECLARE @nmovimenti int
CREATE TABLE #tblTemp1
(
id INT ,
data_elaborazione date,
arr int,
par int
)
DECLARE ShiftCursorValue CURSOR FOR
SELECT
idstruttura,
arrivo,
partenza,
count(idrecord) as movimenti
FROM
Tab_movimenti
WHERE
Idstruttura=1 and arrivo between '2015/01/01' and '2015/01/31'
group by Idstruttura,arrivo,partenza
order by arrivo
OPEN ShiftCursorValue
FETCH NEXT FROM ShiftCursorValue INTO
@codstruttura,@arrivo,@partenza,@nmovimenti
WHILE @@FETCH_STATUS = 0
BEGIN
if @partenza IS NULL
BEGIN
insert into #tblTemp1 (id,data_elaborazione,arr,par)
values
(@codstruttura,@arrivo,@nmovimenti,0)
END
if @partenza IS NOT NULL AND @partenza<>@arrivo
BEGIN
Update #tblTemp1 set par=0 where data_elaborazione=@arrivo
insert into #tblTemp1 (id,data_elaborazione,arr,par)
values
(@codstruttura,@arrivo,@nmovimenti,0)
insert into #tblTemp1 (id,data_elaborazione,arr,par)
values
(@codstruttura,@partenza,0,@nmovimenti)
END
if @partenza IS NOT NULL and @partenza=@arrivo
BEGIN
insert into #tblTemp1 (id,data_elaborazione,arr,par)
values
(@codstruttura,@arrivo,@nmovimenti,@nmovimenti)
END
FETCH NEXT FROM ShiftCursorValue INTO @codstruttura,@arrivo,@partenza,@nmovimenti
END
CLOSE ShiftCursorValue
DEALLOCATE ShiftCursorValue
Select id,data_elaborazione,sum(arr) arrivi,sum(par) partiti
from #tblTemp1
group by id,data_elaborazione
Order by data_elaborazione
[/code]
ciao e grazie
Cirillo Fabio
www.trycontact.com
www.wondernet.biz
fabio@wondernet.biz
http://blogs.dotnethell.it/fabiocirillo/http://wnetsoftware.blogspot.com
renarig 
Profilo | Expert 
517 messaggi | Data Invio: gio 22 gen 2015 - 02:34
A costo di dire una scemata ( da inesperto quele sono )
....... a me non piacciono le tabelle temporanee,
valuterei di farlo con le CTE
(magari spostandola anche su lato applicazione)
quindi sulla tabella che ci hai descritto verrebbe più o meno cosi,
ho fatto tutti i passaggi ma certamente si puo ancora semplificare
USE TuoDataBase
GO
DECLARE @fromdata AS date
DECLARE @todata AS date
SET @fromdata = '2015/01/01'
SET @todata = '2015/02/01'
;
WITH
v01 AS (
SELECT TOP (100) PERCENT
arrivo AS data
FROM dbo.Tab_movimenti
WHERE (arrivo > @fromdata) OR (arrivo < @todata)
),
v02 AS (
SELECT TOP (100) PERCENT
partenza AS data
FROM dbo.Tab_movimenti
WHERE (partenza > @fromdata) OR (partenza < @todata)
),
v04 AS (
SELECT data FROM v01
UNION ALL
SELECT data FROM v02
),
v05 AS (
SELECT DISTINCT data FROM v04
),
v10 AS (
SELECT
v05.data,
COUNT(TMA.arrivo) AS arrivi,
COUNT(TMP.partenza) AS partenze
FROM
dbo.Tab_movimenti AS TMP
RIGHT OUTER JOIN
v05
ON TMP.partenza = v05.data
LEFT OUTER JOIN
dbo.Tab_movimenti AS TMA
ON v05.data = TMA.arrivo
GROUP BY v05.data
),
v15 AS (
SELECT
data, arrivi, partenze,
arrivi - partenze AS variazione
FROM v10
),
v20 AS (
SELECT TOP (100) PERCENT
data, arrivi, partenze, variazione,
(SELECT SUM(variazione) AS pres FROM v15 AS yy WHERE (data <= v15.data)) AS presenze
FROM v15
),
v22 AS (
SELECT TOP (100) PERCENT
data, arrivi, partenze, presenze
FROM v20
ORDER BY data
)
SELECT * FROM v22
data arrivi partenze presenze
2015-01-01 3 0 3
2015-01-03 2 0 5
2015-01-04 3 0 8
2015-01-07 0 1 7
Facci sapere
8.814 messaggi | Data Invio: gio 22 gen 2015 - 15:25
Ciao a tutti,
>A costo di dire una scemata ( da inesperto quele sono )
>....... a me non piacciono le tabelle temporanee, valuterei di farlo con le CTE
le tabelle temporanee, usate correttamente, sono uno degli strumenti più potenti che hai con SQL Server per ragionare a set.
Le CTE sono veramente valide quando si parla di ricorsione, mentre, usate come resultset temporanei o subquery, a volte si comportano un po' stranamente (va sempre controllato il piano 
non è che non vadano bene eh).
Per tabelle temporanee io intendo ANCHE (ma non solo) le variabili tabella, veramente molto importanti con resultset piccoli come questi. Considera che una temp table in variabile (es: DECLARE @ttt tABLE) lavora SOLO in memoria, e, solamente nel caso di memory pressure, viene trasformata in una temporary reale (#ttt). Quindi le valuterei quando ho a che fare con spezzettamenti di set necessari.
>(magari spostandola anche su lato applicazione)
bravo, torno a ripetermi sempre, queste logiche non sono da database a mio avviso. La logica di business dovrebbe ricevere il set di dominio su cui lavorare e poi applicare le logiche NON SET invece che forzare il database a farne troppe. Un relazionale è fatto per "girare" su SET, JOIN, WHERE, GROUP BY, SORT, ecc.. non su cicli o procedure troppo impegnative.
Questa è la mia proposta, logicamente simile alla tua @renarig (no, non sei una variabile ), ma con l'utilizzo delle temp var. La struttura è:
- creazione di una tabella delle giornate del mese disponibili (prettamente una tabella di lavoro)
- creazione del set di dominio per le variazioni
- aggregazione e risultati finali
Eccola:
DECLARE @DataRiferimentoFrom date = '20150101';
DECLARE @DataRiferimentoTo date = DATEADD(MONTH, 1, @DataRiferimentoFrom);
DECLARE @DataRiferimentoProgressiva date = @DataRiferimentoFrom;
DECLARE @DateDisponibili table
(
DataRiferimento date
);
DECLARE @Variazioni table
(
DataRiferimento date PRIMARY KEY
, Partenze smallint
, Arrivi smallint
, Variazione smallint
);
WHILE @DataRiferimentoProgressiva <= @DataRiferimentoTo
BEGIN
INSERT INTO @DateDisponibili (DataRiferimento) VALUES (@DataRiferimentoProgressiva);
SET @DataRiferimentoProgressiva = DATEADD(DAY, 1, @DataRiferimentoProgressiva);
END;
INSERT INTO @Variazioni (DataRiferimento, Partenze, Arrivi, Variazione)
SELECT
DD.DataRiferimento
, ArriviGiornalieri = COALESCE(PA.Arrivi, 0)
, PartenzeGiornaliere = COALESCE(PA.Partenze, 0)
, Variazione = COALESCE(PA.Arrivi, 0) - COALESCE(PA.Partenze, 0)
FROM
@DateDisponibili DD
LEFT JOIN
(
SELECT
DataRiferimento = arrivo
, Arrivi = COUNT(arrivo)
, Partenze = 0
FROM
dbo.Tab_movimenti
WHERE
arrivo >= @DataRiferimentoFrom AND arrivo < @DataRiferimentoTo
GROUP BY
arrivo
UNION
SELECT
DataRiferimento = partenza
, Arrivi = 0
, Partenze = COUNT(partenza)
FROM
dbo.Tab_movimenti
WHERE
partenza >= @DataRiferimentoFrom AND partenza < @DataRiferimentoTo
GROUP BY
partenza
) PA
ON PA.DataRiferimento = DD.DataRiferimento;
SELECT
V.DataRiferimento
, V.Partenze
, V.Arrivi
, V.Variazione
, Presenze = (SELECT SUM(Variazione) FROM @Variazioni WHERE DataRiferimento <= V.DataRiferimento)
FROM
@Variazioni V;
Noterai che i risultati sono molti di più, questo perchè ho notato che nel resultset inizialmente voluto c'è anche il 2 di gennaio, data che non ha nessuna partenza o arrivo. Quindi ho optato per sviluppare tutto il mese. Basta togliere la left join o cambiare la where per fare toggle della funzionalità.
Spero possa andare bene come quella di @renarig.
ciao
Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT
http://blogs.dotnethell.it/suxstellino
http://suxstellino.wordpress.com
http://mvp.microsoft.com/en-us/mvp/Alessandro%20Alpi-4014222
trinity Profilo | Guru 
3.465 messaggi | Data Invio: gio 22 gen 2015 - 15:44
Prima di tutto grazie ad entrambi per il tempo dedicato e gli esempi postati.
Prima di tutto volevo dire a questo punto il codice da me fatto è errato?
Secondo analizzerò ed eseguire entrambi i vostri codici per capire come avete fatto e così potrò metterlo in pratica
Alx_81, nel tuo codice quando lo eseguo mi da questo errore:
rispondendo a renarig
in effetti ho pensato di far fare qualche operazione lato applicazione pertanto la mia stored esegue solamente il controllo per tutto il periodo indicato degli arrivi e partenze, ovviamente mettendo sullo stesso giorno arrivi e partenze qualora ci fossero ed infine il calcolo delle presenze lo eseguo sulla applicazione
grazie ;)
Cirillo Fabio
www.trycontact.com
www.wondernet.biz
fabio@wondernet.biz
http://blogs.dotnethell.it/fabiocirillo/
http://wnetsoftware.blogspot.com
8.814 messaggi | Data Invio: gio 22 gen 2015 - 16:05
>Prima di tutto volevo dire a questo punto il codice da me fatto è errato?
Se quanto ottieni è quello che ti serve direi di no, ma eviterei i cursori per una cosa così.
>Alx_81, nel tuo codice quando lo eseguo mi da questo errore:
usando i tuoi dati di esempio e lo script non cambiato non mi sembra avere errori.
Come l'hai cambiato?
Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT
http://blogs.dotnethell.it/suxstellino
http://suxstellino.wordpress.com
http://mvp.microsoft.com/en-us/mvp/Alessandro%20Alpi-4014222
trinity Profilo | Guru
3.465 messaggi | Data Invio: gio 22 gen 2015 - 16:19
Il tuo script non l'ho cambiato copiato su sql ed eseguito la query, aspe ti posto i dati reali che ci sono nel db:
ecco la tabella
Cirillo Fabio
www.trycontact.com
www.wondernet.biz
fabio@wondernet.biz
http://blogs.dotnethell.it/fabiocirillo/
http://wnetsoftware.blogspot.com
8.814 messaggi | Data Invio: gio 22 gen 2015 - 17:00
>Il tuo script non l'ho cambiato copiato su sql ed eseguito la
>query, aspe ti posto i dati reali che ci sono nel db:
ok, c'era un caso non definito nei primi dati di esempio che non avevo considerato 
Eccolo, fammi sapere:
DECLARE @DataRiferimentoFrom date = '20150101';
DECLARE @DataRiferimentoTo date = DATEADD(MONTH, 1, @DataRiferimentoFrom);
DECLARE @DataRiferimentoProgressiva date = @DataRiferimentoFrom;
DECLARE @DateDisponibili table
(
DataRiferimento date
);
DECLARE @Variazioni table
(
DataRiferimento date PRIMARY KEY
, Partenze smallint
, Arrivi smallint
, Variazione smallint
);
WHILE @DataRiferimentoProgressiva <= @DataRiferimentoTo
BEGIN
INSERT INTO @DateDisponibili (DataRiferimento) VALUES (@DataRiferimentoProgressiva);
SET @DataRiferimentoProgressiva = DATEADD(DAY, 1, @DataRiferimentoProgressiva);
END;
INSERT INTO @Variazioni (DataRiferimento, Arrivi, Partenze, Variazione)
SELECT
DD.DataRiferimento
, ArriviGiornalieri = SUM(COALESCE(PA.Arrivi, 0))
, PartenzeGiornaliere = SUM(COALESCE(PA.Partenze, 0))
, Variazione = SUM(COALESCE(PA.Arrivi, 0) - COALESCE(PA.Partenze, 0))
FROM
@DateDisponibili DD
LEFT JOIN
(
SELECT
DataRiferimento = arrivo
, Arrivi = COUNT(arrivo)
, Partenze = 0
FROM
dbo.Tab_movimenti
WHERE
arrivo >= @DataRiferimentoFrom AND arrivo < @DataRiferimentoTo
GROUP BY
arrivo
UNION ALL
SELECT
DataRiferimento = partenza
, Arrivi = 0
, Partenze = COUNT(partenza)
FROM
dbo.Tab_movimenti
WHERE
partenza >= @DataRiferimentoFrom AND partenza < @DataRiferimentoTo
GROUP BY
partenza
) PA
ON PA.DataRiferimento = DD.DataRiferimento
GROUP BY
DD.DataRiferimento;
SELECT
V.DataRiferimento
, V.Partenze
, V.Arrivi
, V.Variazione
, Presenze = (SELECT SUM(Variazione) FROM @Variazioni WHERE DataRiferimento <= V.DataRiferimento)
FROM
@Variazioni V;
Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT
http://blogs.dotnethell.it/suxstellino
http://suxstellino.wordpress.com
http://mvp.microsoft.com/en-us/mvp/Alessandro%20Alpi-4014222
trinity Profilo | Guru
3.465 messaggi | Data Invio: gio 22 gen 2015 - 17:30
Funziona eccetto una piccola modifica, con il tuo codice viene caricato anche il giorno 01/02/2015 invece deve finire al 31/01/2015, ho modificato io questo facendo:
DECLARE @DataRiferimentoTo date = DATEADD(day, -1, DATEADD(month, 1, CONVERT(char( 6),@DataRiferimentoFrom, 112) + '01'))
funziona come visualizzazione dei giorni ma al 3101/2015 non mi calcola un arrivo e quindi le presenze che dovrebbero essere in totale 34 sono 33
inoltre, perdonami, mi sono dimenticato di dire che può accadere che in un mese ci siano più partenze che arrivi, la tua procedura prendere bene in questo caso i dati ma le presenze sono negative, pertanto si può fare qualcosa per portarsi il numero totale delle presenze del mese precedente e quindi poi lavorare con questo dato aggiungendolo alle presenze che si generano nel mese di lavorazione.
Ti spiego
ti faccio vedere un result con il tuo codice del mese di febbraio ovviamente simulato
2015-02-01 2 4 -2 -2
2015-02-02 1 3 -2 -4
come puoi vedere ci sono a febbraio -2 presenze, ecco servirebbe che se gennaio ha un totale di 30 presenze, tale valore sarebbe da riportare nel mese di febbraio al primo giorno così poi tutto il resto si allinea correttamente come in questo esempio:
2015-02-01 2 4 -2 28
2015-02-02 1 3 -2 26
stavo facendo anche un ampliamento al tuo codice nel senso di inserire accanto alla colonna arrivi e partenze che tipologia sono ossia se sono italiani o stranieri e ho scritto così:
DECLARE @DataRiferimentoFrom date = '20150101';
DECLARE @DataRiferimentoTo date = DATEADD(MONTH, 1, @DataRiferimentoFrom);
DECLARE @DataRiferimentoProgressiva date = @DataRiferimentoFrom;
DECLARE @DateDisponibili table
(
DataRiferimento date
);
DECLARE @Variazioni table
(
DataRiferimento date PRIMARY KEY
, Partenze smallint
, Arrivi smallint
, Variazione smallint
, TipoArrivi Varchar(20)
, TipoPartenze Varchar(20)
);
WHILE @DataRiferimentoProgressiva <= @DataRiferimentoTo
BEGIN
INSERT INTO @DateDisponibili (DataRiferimento) VALUES (@DataRiferimentoProgressiva);
SET @DataRiferimentoProgressiva = DATEADD(DAY, 1, @DataRiferimentoProgressiva);
END;
INSERT INTO @Variazioni (DataRiferimento, Arrivi, TipoArrivi, Partenze, TipoPartenze, Variazione)
SELECT
DD.DataRiferimento
, ArriviGiornalieri = SUM(COALESCE(PA.Arrivi, 0))
, TipoArrivi
, PartenzeGiornaliere = SUM(COALESCE(PA.Partenze, 0))
, TipoPartenze
, Variazione = SUM(COALESCE(PA.Arrivi, 0) - COALESCE(PA.Partenze, 0))
FROM
@DateDisponibili DD
LEFT JOIN
(
SELECT
DataRiferimento = arrivo
, Arrivi = COUNT(arrivo)
, Partenze = 0
, TipoArrivi = case when p.codistat=0 then 'STRANIERI' else 'ITALIANI' end
FROM
dbo.Tab_movimenti
JOIN
dbo.Tab_comuni_nazioni p
ON idprovenienza=p.idrecord
WHERE
arrivo >= @DataRiferimentoFrom AND arrivo < @DataRiferimentoTo
GROUP BY
arrivo,p.codistat
UNION ALL
SELECT
DataRiferimento = partenza
, Arrivi = 0
, Partenze = COUNT(partenza)
, TipoPartenze =case when p.codistat=0 then 'STRANIERI' else 'ITALIANI' end
FROM
dbo.Tab_movimenti
JOIN
dbo.Tab_comuni_nazioni p
ON idprovenienza=p.idrecord
WHERE
partenza >= @DataRiferimentoFrom AND partenza < @DataRiferimentoTo
GROUP BY
partenza,p.codistat
) PA
ON PA.DataRiferimento = DD.DataRiferimento
GROUP BY
DD.DataRiferimento, PA.TipoArrivi;
SELECT
V.DataRiferimento
, V.Arrivi
, V.TipoArrivi
, V.Partenze
, V.TipoPartenze
, V.Variazione
, Presenze = (SELECT SUM(Variazione) FROM @Variazioni WHERE DataRiferimento <= V.DataRiferimento)
FROM
@Variazioni V;
solo che mi da questo messaggio:
Messaggio 207, livello 16, stato 1, riga 33
Il nome di colonna 'TipoPartenze' non è valido.
quindi non potendo eseguire la query non so neanche se il codice da me aggiunto sia corretto e funzionante
Dove sbaglio?
Ciao e grazie mille
Cirillo Fabio
www.trycontact.com
www.wondernet.biz
fabio@wondernet.biz
http://blogs.dotnethell.it/fabiocirillo/
http://wnetsoftware.blogspot.com
renarig Profilo | Expert
517 messaggi | Data Invio: sab 24 gen 2015 - 02:35
>...... Considera che una temp table in variabile (es: DECLARE @ttt tABLE) lavora SOLO in memoria, .......
Mi ricredo sulle temporanee, non conoscevo ancora questo tipo
e mi piace molto perché lavora solo in memoria 
.
8.814 messaggi | Data Invio: mer 4 feb 2015 - 14:58
Ciao
>solo che mi da questo messaggio:
>Messaggio 207, livello 16, stato 1, riga 33
>Il nome di colonna 'TipoPartenze' non è valido.
certo, nelle union all il primo resultset (la prima query) ha la colonna TipoArrivi, il secondo "sovrascrive" il nome della colonna che userà il compilatore.
Se vuoi averle entrambe, dovrai creare un campo fittizio a null nella prima parte (prima di union all) chiamato TipiPartenze ed uno chiamato TipiArrivo a null nel resultset successivo alla union all.
Poi dovrai decidere come aggregare il campo nella select esterna alla subquery (quindi dovrai scegliere tra funzioni come MAX, SUM oppure fare GROUP BY).
Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT
http://blogs.dotnethell.it/suxstellino
http://suxstellino.wordpress.com
http://mvp.microsoft.com/en-us/mvp/Alessandro%20Alpi-4014222