Calcolare un totale e scriverlo sulla riga successiva di un set di rec...

venerdì 07 luglio 2017 - 19.45

Elenco Threads

Stanze Forum

Aggiungi ai Preferiti

Cerca nel forum

danilo72h501 Profilo | Newbie

4 messaggi | Data Invio: ven 7 lug 2017 - 19:45

Buonasera, lavoro con access 2003 e ho delle tabelle collegate al server sql.2005 express, dovrei fare un update su una tabella fatta così

ditta agente data rimanenzainiziale affidato reso impcontanti ivacontanti impcredito ivacredito sconti omaggi impconsegne ivaconsegne differenza rimanenzafinale
0001 038 30/06/2017 € 12.282,85 € 2.953,39 € 0,00 € 1.512,90 € 151,43 € 33,98 € 3,40 € 95,53 € 216,35 € 0,00 € 0,00 € 0,00 € 13.377,48
0001 038 01/07/2017 € 13.377,48 € 3.952,53 € 0,00 € 2.497,81 € 247,16 € 221,86 € 22,19 € 327,30 € 52,72 € 188,64 € 18,86 € 0,00 € 14.041,68
0001 038 03/07/2017 € 14.041,68 € 0,00 € 0,00 € 945,89 € 80,35 € 354,50 € 33,73 € 138,65 € 101,77 € 0,00 € 0,00 € 0,00 € 12.500,87
0001 038 04/07/2017 € 12.500,87 € 4.203,67 € 0,00 € 2.838,89 € 289,75 € 0,00 € 0,00 € 173,87 € 100,65 € 0,00 € 0,00 € 0,00 € 13.591,13
0001 038 05/07/2017 € 13.591,13 € 3.956,33 € 31,90 € 2.747,53 € 298,60 € 228,69 € 22,87 € 192,93 € 270,55 € 36,40 € 3,64 € 0,00 € 14.039,46
0001 038 06/07/2017 € 14.039,46 € 3.344,20 € 0,00 € 2.169,41 € 227,62 € 546,57 € 52,66 € 263,33 € 106,64 € 0,00 € 0,00 € 0,00 € 14.297,71

devo aggiornare i campi rimanenza iniziale e finale nel caso vengano variati i dati di uscito e entrato

sono riuscito a farlo sia tramite recordset che con una funzione tipo questa

Public Function AllUam(V1 As String, V2 As String, rin As Double, calc As Double) As Double
If dtag <> V1 & V2 Then rinp = rin
dtag = V1 & V2
AllUam = rinp
rinp = rinp + calc
End Function

che utilizzo in questa query per aggiornare le rimanenze iniziali

UPDATE [SELECT uam.ditta, uam.agente, uam.data, uam.rimanenzainiziale, uam.affidato, uam.reso, uam.impcontanti, uam.ivacontanti, uam.impcredito, uam.ivacredito, uam.sconti, uam.omaggi, uam.rimanenzafinale, uam.differenza, uam.impconsegne, uam.ivaconsegne FROM uam ORDER BY uam.ditta, uam.agente, uam.data]. AS UAM0001 SET UAM0001.rimanenzainiziale = alluam([ditta],[agente],[rimanenzainiziale],[affidato]-[reso]-[impcontanti]-[impcredito]-[sconti]-[omaggi]+[differenza]-[impconsegne]) WHERE (((UAM0001.data)>=DateSerial(2017,1,1)));

dopodiché per aggiornare le rimanenze iniziali uso questa molto più semplice e il gioco è fatto

UPDATE uam SET uam.rimanenzafinale = [rimanenzainiziale]+[affidato]-[reso]-[impcontanti]-[impcredito]-[sconti]-[omaggi]+[differenza]-[impconsegne] WHERE (((uam.data)>=DateSerial(2017,1,1)));

finchè ho la tabella in un db jet risulta il tutto abbastanza veloce, se la metto nel db sql server 2005 la scrittura diventa molto più lenta, (le righe sono circa 20.000)

Volevo chiedervi se secondo voi è possibile fare la procedura direttamente sul server sql tramite query pass trough, in questo caso presumo dovrei creare la mia function alluam nel database sql, potete aiutarmi a farlo? Grazie infinite e scusate la confusione nel messaggio.

renarig Profilo | Expert

517 messaggi | Data Invio: sab 8 lug 2017 - 20:22

>Volevo chiedervi se secondo voi è possibile fare la procedura direttamente sul server sql tramite query pass trough,
>in questo caso presumo dovrei creare la mia function alluam nel database sql,
>potete aiutarmi a farlo? Grazie infinite e scusate la confusione nel messaggio.

E possibile e certamente meglio farlo con una PassTrought
oppure con una Stored che viene lanciata da Access

Postaci lo script di creazione della tabella con qualche dato per testare
in modo che si possa andare mirati alla meta

.

Post scritto
Pero mi sembra molto rindondante

.

danilo72h501 Profilo | Newbie

4 messaggi | Data Invio: dom 16 lug 2017 - 22:13

2056_TEST.zip

Grazie per la risposta, ho provato a creare nel db sql la stessa funzione che avevo creato in access ma non sono riuscito a creare variabili globali, (forse in sql server non è possibile farlo),

allego un file bak contenente la tabella in questione, detto in parole povere ogni riga ha due campi di importo iniziale e finale

la formula deve essere questa

[rimanenzainiziale]+[affidato]-[reso]-[impcontanti]-[impcredito]-[impconsegne]-[sconti]-[omaggi]+[differenza] = [rimanenza finale]

ogni riga ha un campo [agente] e [data]

la procedura dovrebbe ricalcolare cronologicamente le rimanenze iniziali e finali per ogni record, considerando i progressivi per ogni agente.

Spero di essere stato esaustivo. Grazie per l'aiuto.

renarig Profilo | Expert

517 messaggi | Data Invio: lun 17 lug 2017 - 19:45

Pero ripeto che è rindondante

Il campo di tabella che tu chiami "rimanenzainiziale" scommetterei che è anche lui frutto di un calcolo !!!!

Questa sera devo uscire e ho pochissimo tempo :(

cominciamo con una banalissima vista che esegue ti calcola la "rimanenzafinale" e vediamo se puo bastare
poi magari vediamo di ottimizzarla in versione PassTrought in modo che non vai a smanettare sul server

Facci sapere

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

danilo72h501 Profilo | Newbie

4 messaggi | Data Invio: lun 17 lug 2017 - 20:16

Eccomi qui, grazie di nuovo per l'interessamento.

Certo, rimanenzainiziale è frutto di un calcolo, ma della riga precedente in ordine cronologico dello stesso agente !!

il problema è ricalcolare tutti i campi di tutti i record in successione e scrivere i totali nei campi rimanenzainiziale e rimanenzafinale ( la finale è un attimo, l'iniziale deve essere il totale finale della riga precedente) lo so che alla fine sono dati ridondanti ma purtroppo mi ritrovo a lavorare con una tabella che anni fa era stata strutturata così, e non posso cambiarla.

Grazie di nuovo, vediamo se ti viene un'idea. Buona serata.

Guarda, io nel frattempo avevo risolto con qualcosa di simile a quello che mi hai suggerito tu

go
UPDATE UAM SET uam.rimanenzainiziale = case when sq2.rimanenzainiziale is not null then SQ2.rimanenzainiziale else uam.rimanenzainiziale end, uam.rimanenzafinale = case when sq2.rimanenzainiziale is not null then null else uam.rimanenzafinale end
FROM uam LEFT JOIN (SELECT uam.agente, uam.data, uam.rimanenzainiziale
FROM (SELECT Min.agente, Min.MinDidata, Max.MaxDidata, case when MaxDidata is Null then mindidata else maxdidata end AS MDD
FROM (SELECT uam.agente, Min(uam.data) AS MinDidata
FROM uam
GROUP BY uam.agente) AS [Min] LEFT JOIN (SELECT uam.agente, Max(uam.data) AS MaxDidata
FROM uam
WHERE (((uam.data)<CONVERT(DATETIME, '2017-01-01 00:00:00', 102)))
GROUP BY uam.agente) AS Max ON Min.agente = Max.agente) AS SQ1 INNER JOIN uam ON (SQ1.MDD = uam.data) AND (SQ1.agente = uam.agente)) AS SQ2 ON uam.agente = SQ2.agente and uam.data >= sq2.data

go
UPDATE uam set uam.rimanenzainiziale = uam.rimanenzainiziale + AR.espr1
from
(SELECT uam.agente, uam.data, Sum(uam_1.affidato-uam_1.reso-uam_1.impcontanti-uam_1.impcredito-uam_1.impconsegne-uam_1.sconti-uam_1.omaggi+uam_1.differenza) AS Espr1
FROM uam INNER JOIN uam AS uam_1 ON (uam.data> uam_1.data) AND (uam.agente = uam_1.agente)
where (uam_1.rimanenzafinale is null)
GROUP BY uam.agente, uam.data) as AR INNER JOIN uam ON (uam.data = AR.data) AND (uam.agente = AR.agente)

go
UPDATE uam SET uam.rimanenzafinale = rimanenzainiziale+affidato-reso-impcontanti-impcredito-impconsegne-sconti-omaggi+differenza

tre query update una dietro l'altra, con un where dove posso impostare la data di partenza degli aggiornamenti, visto che posso tranquillamente partire dall'anno in cui sto lavorando. La prima query che comprende due sottoquery che servono ad ottenere la data dell'ultimo movimento precedente al periodo impostato, mi copia il valore iniziale del record precedente al periodo impostato, in tutti quelli successivi, mettendo a null le rimanenze finali; la seconda query mi calcola i valori da aggiungere alle rimanenze iniziali dei record elaborati dalla precedente, filtrandoli per valore null della rimanenza finale (ho usato questo stratagemma del null per far diventare l'update velocissimo), poi la terza e ultima semplice query che mi ricalcola le rimanenza finali che trova a null.

complicato da spiegare ma risulta tutto abbastanza performante, se lavoro ad esempio nell'anno corrente in un secondo mi ricalcola tutto, per due anni invece ci mette 3 secondi.

Son diventato pazzo per tre giorni per ottenere questo risultato, ma quello che mi chiedevo inizialmente è se esiste un modo per processare i record uno alla volta e scrivere i valori come si fa con i recordset da vb.

Mi domando inoltre come mai quando sono nella rete aziendale la velocità di scrittura dei dati dai client al server sql è piuttosto lenta, scrivendo i dati in un mdb jet sempre in rete, la velocità è molto più alta, mi chiedevo se è forse colpa della versione express di sql server.

grazie, e scusa per il rompicapo, se ti va prova le tre query che ho scritto, vanno alla grande

Torna su

Stanze Forum

Elenco Threads

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 !