acces2003 verificare se compreso tra date inizio fine

[wallace&gromit]

ciao,
il mio problema è questo: ho una tabella che raccoglie tutte le ore dei collaboratori con i campi: (collab/data/mandato/ore)
dall'altra parte ho una tabella fatture (mandato/dataInizio/dataFine).
L'elemento in comune è "mandato".
Devo trovare le attività inserite che non rientrano nelle fatture.
Finora sono riuscito a crearmi una tabella che per ogni attività inserita dal collaboratore verifico se esistono fatture per quel mandato e se ve ne è una le cui date inizio e fine comprendono la data inserita do valore 1, altrimenti 0.
Ora ho una tabella con molti 0 e una serie di 1.
Le attività inserite sono ora ripetute: per esempio se tizio in data x ha svolto il mandato y vedrò ripetuto questi valori tante volte quante sono le fatture del mandato y, per tutte le fatture con data diversa ho 0 e, generalmente, ne ho una con valore 1.
Il mio scopo è ora estrarre quelle attività che non hanno mai un 1, significa che non sono mai state comprese in alcuna fattura.
Purtroppo mi sarà difficile inviare esempi, spero di essere stato chiaro.
Magari tutto l'approccio è sbagliato... qualunque consiglio è ben accetto.

[archimede]

ho una tabella che raccoglie tutte le ore dei collaboratori con i campi: (collab/data/mandato/ore)
dall'altra parte ho una tabella fatture (mandato/dataInizio/dataFine).
L'elemento in comune è "mandato".

Fin qui ci siamo, anche se non è chiaro se lo stesso mandato può essere presente più volte, magari con date sovrapposte (diciamo che potrebbe non essere rilevante).

Devo trovare le attività inserite che non rientrano nelle fatture.

Qui non ci siamo più: cos'è, una terza tabella?

sono riuscito a crearmi una tabella che per ogni attività inserita dal collaboratore

Parrebbe di sì, ma com'è fatta questa tabella?

Alessandro

[wallace&gromit]

cos'è, una terza tabella?

hai ragione, non sono stato chiaro su questo punto: si tratta di una query che confronta le due tabelle. Per ogni attività inserita (con un mandato specifico) cerca tutte le fatture emesse per quel mandato
e calcola una nuova espressione che ha valore 1 se le date coincidono, 0 se non coincidono.

non è chiaro se lo stesso mandato può essere presente più volte

un mandato può apparire più volte (questo mi genera il problema di presenze multiple nella query: ogni attività viene ripetuta tante volte quante sono le fatture emesse per quel mandato, ma solo per una, quella il cui range di date racchiude la data dell'attività inserita, il valore dell'espressione è 1, negli altri casi figura 0)

magari con date sovrapposte

questo non succede ed è importante, perchè ogni attività inserita deve essere attribuita al massimo ad una fattura.

Alla fine vorrei identificare quelle attività che non sono mai state attribuite ad una fattura (le possibilità sono due: o non è mai stata emessa una fattura per quel mandato o le fatture emesse hanno un range di date che non comprende quell'attività), questo mi serve per riuscire a ripescare tutte le ore "perse".

[archimede]

Per ogni attività inserita (con un mandato specifico) cerca tutte le fatture emesse per quel mandato

Quindi ogni riga della tabella contenente le ore dei collaboratori sarebbe quella che tu chiami Attività?

vorrei identificare quelle attività che non sono mai state attribuite ad una fattura

Quindi vuoi trovare tutte le righe della tabella contenente le ore dei collaboratori la cui data non rientra in nessuno dei range di date della tabella Fatture con lo stesso mandato?

Alessandro

PS: qual è la PK della tabella contenente le ore dei collaboratori?

[wallace&gromit]

sì quanto hai scritto è esatto.
cos'è la PK?

[archimede]

PK è la primary key, ma probabilmente non serve. Prova così:

Codice: Seleziona tutto

SELECT A.*
  FROM Attivita A, Fatture F
 WHERE A.mandato = F.mandato
   AND A.data NOT BETWEEN F.dataInizio AND F.dataFine;

HTH.

Alessandro

PS: come non detto, scusa; la query che ho postato non è corretta. Allora forse una PK può essere d'aiuto: ce l'ha quella tabella?

[wallace&gromit]

potevo arrivarci... la chiave primaria!
non sono molto solerte nell'utilizzarla ma temo sia stata applicata ad un ID numerico (che non avevo ancora menzionato perchè ritenevo irrilevante), ora non sono in ufficio e non vedo la tabella, domani ti aggiorno!

[archimede]

Allora puoi provare

Codice: Seleziona tutto

SELECT A.*
  FROM Attivita A
 WHERE A.ID NOT IN (SELECT A2.ID
                      FROM Attivita A2, Fatture F
                     WHERE A2.mandato = F.mandato
                       AND A2.data BETWEEN F.dataInizio AND F.dataFine)

Dove ID è il campo chiave primaria, ovviamente.

HTH.

Alessandro

[wallace&gromit]

molto bene!!!
la selezione per un singolo collaboratore funziona perfettamente.
Ora provo io a estendere a tutti i collaboratori in un colpo solo e poi dovrei essere a cavallo.
Vi terrò informati...

[wallace&gromit]

ciao, eccomi qua, il problema l'ho risolto con questa query:

Codice: Seleziona tutto

SELECT [ore marco].ID, [ore marco].COLLAB, [ore marco].DATA, [ore marco].ORE, [ore marco].NO_MAND, Year([DATA]) AS CheAnno
FROM [ore marco]
WHERE (((Year([DATA]))=[QualeAnno]) AND (([ore marco].NO_MAND)>30))
GROUP BY [ore marco].ID, [ore marco].COLLAB, [ore marco].DATA, [ore marco].ORE, [ore marco].NO_MAND, Year([DATA])
HAVING ((([ore marco].ID) Not In (SELECT [ore marco].[ID]
                      FROM [ore marco], [UnioneFattProvv]
                     WHERE [ore marco].[no_mand] = [UnioneFattProvv].[no_mand]
                       AND [ore marco].[data] BETWEEN [UnioneFattProvv].[PeriodoIniz] AND [UnioneFattProvv].[PeriodoFine])))

ripetuta con un UNION SELECT per tutti i collaboratori.
Visto che l'allestimento di questa sequenza per tutti i collaboratori era piuttosto lunga, io - furbissimo! - mi sono detto: perchè non prendo la query unione delle ore, che già esiste, e inserisco una sola volta questo procedimento?
Risultato: l'aggiornamento della query impiega più o meno un'era geologica!!!
Domanda 1: perchè?
Domanda 2: nel frattempo ho allestito la SQL per tutti i collaboratori (non è una scheggia ma si può avere un output in pochi minuti), c'è modo di compilare in modo più rapido un union select, oppure che ne so fare un ciclo for... next come si farebbe in una macro?

[archimede]

Mi scuserai, ma non ho la più pallida idea della relazione che ci sia tra la query che ho postato io e l'ultima da te inviata.

Alessandro

[wallace&gromit]

Dai, un po' ci assomiglia ancora...
nella parte "Where..." ho inserito i miei riferimenti reali al posto delle tue indicazioni e poi, in effetti, ho aggiunto un raggruppamento dati (io non mi trovo molto con i SQL e lavoro con la struttura, quindi il linguaggio che ne esce è quello di access), comunque funziona egregiamente.
Quella che mi preme di più è la seconda domanda: c'è modo di compilare in modo rapido una UNION SELECT senza dovere sostituire tutti i riferimenti alle singole tabelle d'origine?
Infatti ognuno compila le proprie attività in una tabella personale che si chiama "ore tizio"

[archimede]

Io sono partito da due tabelle: Attivita e Fatture.

Qui mi ritrovo una fantomatica [ore marco] (per favore, dimmi che non hai n query o tabelle tutte uguali per ogni collaboratore), mi si parla di union e di selezione per un singolo collaboratore... l'unica somiglianza che vedo è che sono entrambe in SQL.

Credo sia opportuno ripartire dal via: spiega ESATTAMENTE (nomi REALI degli oggetti, chiavi, relazioni tra le tabelle, ecc.) che tabelle hai, che dati contengono e cosa ci vuoi tirare fuori (magari con qualche pratico esempio di dati REALI).

Alessandro

[wallace&gromit]

wow, capisco la tua frustrazione: effettivamente avevo scritto che ho UNA tabella con le attività e una TABELLA con le fatture, mentre in realtà non è così: ho tante tabelle analoghe, una per collaboratore con le singole attività e ho una QUERY per le fatture, che mi permette di gestire quelle fatture (rare) che comprendono più mandati.

le tabelle attività hanno i campi ID/COLLAB/DATA/ORE/NO_MAND,
su tua indicazione ho reso ID chiave primaria in ogni tabella (ovviamente gli ID sono univoci per ogni tabella ma si ripetono confrontando più tabelle di collaboratori - non mi sembra che questo generi problemi).

Un primo filtro che opero è una ricerca sull'anno: ho inserito l'operatore [QualeAnno] per il quale al lancio della query mi chede il dato, così facendo filtro i dati per l'anno che mi serve. Inoltre scarto tutti i NO_MAND inferiori a 30, che sono vacanze, malattia, altre ore non fatturate.

Segue il GROUP, che come detto è stato aggiunto da access (non so bene che ruolo abbia).

Infine confronto il tutto con la query unioneFattProvv, che contiene i campi NO_MAND/PeriodoIniz/PeriodoFine.

Avevo sempre in mente la furbata di usare la query di unione delle ore invece delle singole tabelle, ma come detto, è troppo lenta.

[archimede]

ho tante tabelle analoghe, una per collaboratore con le singole attività

E questo disegno è definitivo o può essere ripensato? Come hai già sperimentato, è la ricetta più rapida per rendere inefficiente le query su un db. Consiglio vivamente di usare un'unica tabella.

Alessandro

[wallace&gromit]

eh questo proprio non lo posso cambiare, è l'impianto base di tutto il sistema, non solo le mie per la fatturazione, inoltre è utile per evitare che qualcuno possa pasticciare con le ore di un altro, visto che ognuno accede solo alla propria tabella delle ore.

[archimede]

Allora temo ci sarà ben poco da ottimizzare.

Intanto, visto che le GROUP BY non sembrano utili, direi di provare a toglierle:

Codice: Seleziona tutto

SELECT o.ID, o.COLLAB, o.DATA, o.ORE, o.NO_MAND, Year(o.DATA) AS CheAnno
  FROM [ore marco] o
 WHERE Year(o.DATA) = [QualeAnno]
   AND o.NO_MAND > 30
   AND o.ID Not In (SELECT o2.ID
                      FROM [ore marco] o2, [UnioneFattProvv] u
                     WHERE o2.no_mand = u.no_mand
                       AND o2.data BETWEEN u.PeriodoIniz AND u.PeriodoFine);

(ovviamente gli ID sono univoci per ogni tabella ma si ripetono confrontando più tabelle di collaboratori - non mi sembra che questo generi problemi).

Quanto ovvio non saprei; che non generi problemi neanche sono così sicuro, ma se lo dici tu, ok.

Avevo sempre in mente la furbata di usare la query di unione delle ore invece delle singole tabelle, ma come detto, è troppo lenta.

Se hai tabelle diverse, non vedo come tu possa evitare di fare union, se vuoi i dati di tutti i collaboratori nella stessa query. Ti direi di provare una UNION secca di tutte le tabelle dei collaboratori (senza where) e poi lavorare su quella, ma non ho capito come è gestito il campo ID.

HTH.

Alessandro

PS: se non puoi fondere le tabelle in una unica, potresti però creare una NUOVA tabella che contiene tutti i records dei vari collaboratori. Ti risparmieresti un bel po' di mal di testa, secondo me.

[wallace&gromit]

un piccolo passo avanti per te, un grande balzo per me!
con il trucco di o.[...] e o2.[...] mi semplifico già molto la vita! devo modificare solo due volte la tabella di riferimento per ogni collaboratore.

PS: se non puoi fondere le tabelle in una unica, potresti però creare una NUOVA tabella che contiene tutti i records

è possibile farlo in maniera diversa dalla query di unione?

[archimede]

è possibile farlo in maniera diversa dalla query di unione?

Non sto dicendo di creare una query, bensì una tabella fisica, facendo delle queries di inserimento. Certo, puoi fare una union query e usare quella come sorgente per la nuova tabella, ma non è obbligatorio.

Alessandro