[SQL]Fusione di 3 Query. Aiutino?

[Donbabbeo]

Ho 3 query che si occupano del calcolo dello stipendio di un ipotetico magazzino:
la prima calcola le ore di lavoro di ogni dipendente e mi restituisce lo stipendio fisso giornaliero di ogni lavoratore, la seconda calcola i depositi di ogni magazziniere e mi restituisce il bonus giornaliero di chi si occupa del deposito
e la terza calcola le consegne effettuate e mi restituisce il bonus giornaliero di ogni fattorino, questo viene fatto poichè lo stipendio di ogni dipendente è dato da una quota fissa che dipende dalle ore lavorative e una quota che dipende dalle consegne/depositi effettuati.

Calcolo costo fisso:

SELECT dipendenti.id, dipendenti.tipo, ((lavora.orafine - lavora.orainizio) / 10000) * dipendenti.costoorario AS Fisso
FROM dipendenti, lavora
WHERE dipendenti.id = lavora.iddipendente
AND lavora.data = 090111;

Da cui risulta:

+----+--------------+-------+
| id | Fisso        | Fisso |
+----+--------------+-------+
|  1 | Magazziniere |	 50 |
|  2 | Fattorino    |	 42 |
|  3 | Magazziniere |	 48 |
|  4 | Fattorino    |	 49 |
+----+--------------+-------+

Calcolo bonus magazzinieri:

SELECT dipendenti.id, dipendenti.tipo, count(deposita.idmagazziniere) * dipendente.costobonus AS Bonus
FROM dipendenti LEFT JOIN deposita
ON dipendenti.id = deposita.idmagazziniere
AND deposita.datadeposito = 090111
GROUP BY dipendenti.id;

Da cui risulta:

+----+--------------+-------+
| id | Fisso        | Bonus |
+----+--------------+-------+
|  1 | Magazziniere |  0.00 |
|  2 | Fattorino    |  0.00 |
|  3 | Magazziniere | 12.00 |
|  4 | Fattorino    |  0.00 |
+----+--------------+-------+

Calcolo bonus fattorini:

SELECT dipendenti.id, dipendenti.tipo, count(ordini.idfattorino) * dipendente.costobonus AS Bonus
FROM dipendenti LEFT JOIN ordini
ON dipendenti.id = ordini.idfattorino
AND ordini.datarichiesta = 090111
GROUP BY dipendenti.id;

Da cui risulta:

+----+--------------+-------+
| id | Fisso        | Bonus |
+----+--------------+-------+
|  1 | Magazziniere |  0.00 |
|  2 | Fattorino    |  3.00 |
|  3 | Magazziniere |  0.00 |
|  4 | Fattorino    |  5.00 |
+----+--------------+-------+

In pratica io vorrei ottenere come risultato questo:

+----+--------------+---------------+
| id | Fisso        | Fisso + Bonus |
+----+--------------+---------------+
|  1 | Magazziniere |  	      50.00 |
|  2 | Fattorino    |  	      45.00 |
|  3 | Magazziniere |  	      60.00 |
|  4 | Fattorino    |  	      54.00 |
+----+--------------+---------------+

E' tutto il giorno che ci provo e non riesco ad ottenere nulla di concreto. Qualche consiglio?

[!Piergiorgio]

Più che le query, è meglio se fai sapere qual'è lo schema del db

[gugoXX]

Originariamente inviato da !Piergiorgio

Più che le query, è meglio se fai sapere qual'è lo schema del db

Sicuramente aiuta.

Comunque puoi provare con qualcosa simile a:

SELECT Query1.id, Query1.tipo, Query1.Fisso + Query2.Bonus + Query3.Bonus
 FROM Query1 JOIN Query2 ON (Query1.id = Query2.id)
 JOIN Query3 ON (Query3.id=Query1.id)

Dove al posto di QueryX metterai

( il codice della X-esima Query che avevi scritto nel tuo post ) AS QueryX

PS: E' sicuramente semplificabile, senza coinvolgere tutto quella roba. (Es: La tabella dipendenti si dovrebbe scrivere una volta sola...)

[Donbabbeo]

Originariamente inviato da !Piergiorgio

Più che le query, è meglio se fai sapere qual'è lo schema del db

Eccolo:

Dipendente (ID, nome, cognome, telefono, tipo, costo orario, costo bonus)
Merci (ID, nome, costo unitario, ricavo unitario, quantità)
Ordini (ID, data richiesta, data consegna, ID cliente, ID fattorino, ricavo)
Cliente (ID, nome, partita IVA, indirizzo, telefono)
Deposita (ID dipendente, ID merce, data deposito, quantità)
Contiene (ID ordine, ID merce, quantità)
Lavora (ID dipendente, data, ora inizio, ora fine)

Ho sottolineato le chiavi. Per Deposita, Contiene e Lavora gli ID sono referenze rispetto alle altre tabelle (sebbene sembra che le referenze non funzionino).

In ogni caso grazie per la dritta, ora proverò la query che mi hai consigliato.

[!Piergiorgio]

Non ho capito bene il tuo obiettivo

Prova a descrivere meglio lo scopo delle tabelle ed esattamente quello che vuoi ottenere a parole

Comunque sia, tieni conto che ci sono certe elaborazioni che non possono essere computate in SQL normale con una sola query (per quanto annidata tu possa farla), ma necessitano estensioni operazionali di SQL (come il PL/SQL di Oracle, ad esempio) che ti permettono di dichiarare cursori e variabili temporanee.

[gugoXX]

Originariamente inviato da !Piergiorgio

Non ho capito bene il tuo obiettivo

Prova a descrivere meglio lo scopo delle tabelle ed esattamente quello che vuoi ottenere a parole

Comunque sia, tieni conto che ci sono certe elaborazioni che non possono essere computate in SQL normale con una sola query (per quanto annidata tu possa farla), ma necessitano estensioni operazionali di SQL (come il PL/SQL di Oracle, ad esempio) che ti permettono di dichiarare cursori e variabili temporanee.

Molte meno di quante possa sembrare.
Non dico che in assoluto non possa essere, ma non mi ricordo di averne incontrate di non risolvibili con il solo SQL.

[!Piergiorgio]

Originariamente inviato da gugoXX

Molte meno di quante possa sembrare.
Non dico che in assoluto non possa essere, ma non mi ricordo di averne incontrate di non risolvibili con il solo SQL.

Non so se è il caso in questione, in quanto non ho capito (posso solo intuirlo) cosa deve fare esattamente la query cercata.

Ma ce ne sono, e anche tante... Tutte quelle che richiedono dei controlli strutturati, ad esempio. Dipende da cosa ci si deve fare

Rimanendo sull'esempio in questione, la computazione che coinvolge query differenti a seconda che la riga in considerazione sia un "fattorino" piuttosto che un "magazziniere" non può essere risolto con una sola query, per quanto si voglia annidare e aggregare. Ci vuole un'esecuzione di query differenziate, una per ogni tipo da considerare, e salvarsi i risultati intermedi. Oppure si usa una delle tante esensioni operazionali di SQL.

http://www.orafaq.com/wiki/PL/SQL_FA...nd_PL.2FSQL.3F

[gugoXX]

Sono praticamente certo che la richiesta del thread si possa risolvere con una query abbastanza semplice.
Comunque come siano tante le interrogazioni strutturate per le quali si ha bisogno di programmazione come PL/SQL non riesco proprio a vederlo.
So bene cos'e' il PL/SQL, dato che sono certificato sia Oracle che SqlServer, ma tanto complesse da dover essere risolte per forza con PL/SQL, tranne che per esigenze tecniche, non ho mai dovuto affrontarle davvero.
Se hai un qualche esempio mi piacerebbe studiarlo.

[!Piergiorgio]

Mi viene in mente un tipico esempio:

CREATE OR REPLACE PROCEDURE DISCRIMINA_FATTURAZIONE IS
  CURSOR cursore_importi IS
      SELECT NUMF,sum(QTA*PREZZO) as IMPORTO
      FROM   dettagli
      group by NUMF;

      vr_importi cursore_importi%ROWTYPE;
      v_TotSmall NUMBER := 0;
      v_TotBig NUMBER := 0;
BEGIN
    open cursore_importi;
    LOOP 
       FETCH cursore_importi into vr_importi;
       EXIT WHEN cursore_importi%NOTFOUND;
       if vr_importi.IMPORTO < 1000 then
      	 v_TotSmall := v_TotSmall + vr_importi.IMPORTO;
       else
         v_TotBig := v_TotBig + vr_importi.IMPORTO;
       end if;
   END LOOP;
    close cursore_importi;
   DBMS_OUTPUT.PUT_LINE('Il totale delle fatture di importo ridotto e'': ' || v_TotSmall);
   DBMS_OUTPUT.PUT_LINE('Il totale delle fatture di importo elevato e'': ' || v_TotBig);
END;

Questa stored procedure calcola la somma di tutte le fatture con importo minore di 1.000 € e, separatamente, la somma di tutte le fatture con importo superiore o uguale a 1.000 €. Immagina poi che, anziché stampare in output, tu voglia avere in output una tabella con 2 colonne: "Tipo" e "Totale", e due righe, una per il tipo "minore di 1.000" e uno per l'altra, col relativo importo calcolato.

Non sono stato a modificare l'esempio per ottenere l'output in forma tabellare, ma è possibile farlo molto facilmente. In questo modo abbiamo lo stesso risultato di "una query che fa quel lavoro lì".

Ecco, questa "query che fa quel lavoro lì" non può essere codificata in SQL, non ci sono santi che tengano

[gugoXX]

Si fa, si fa (sempre se ho capito)

SELECT SUM( CASE WHEN Importo<1000 THEN Importo ELSE 0 END) Minori,
       SUM( CASE WHEN Importo>=1000 THEN Importo ELSE 0 END) Maggiori
FROM (SELECT NUMF,sum(QTA*PREZZO) as IMPORTO
      FROM   dettagli
      group by NUMF) tabtab

Funziona sia sotto Oracle che SQL Server e penso anche MySql.

[!Piergiorgio]

Il risultato è quello, ma non è SQL standard.

Originariamente inviato da gugoXX

Si fa, si fa (sempre se ho capito)

SELECT SUM( CASE WHEN Importo<1000 THEN Importo ELSE 0 END) Minori,
       SUM( CASE WHEN Importo>=1000 THEN Importo ELSE 0 END) Maggiori
FROM (SELECT NUMF,sum(QTA*PREZZO) as IMPORTO
      FROM   dettagli
      group by NUMF) tabtab

Funziona sia sotto Oracle che SQL Server e penso anche MySql.

Hai usato un'estensione operazionale di SQL compatibile con tutti gli ultimi dbms -che infatti capiscono SQL3- la quale però non è SQL standard.

SQL non è un linguaggio turing-completo per definizione

[gugoXX]

Originariamente inviato da !Piergiorgio

Il risultato è quello, ma non è SQL standard.



Hai usato un'estensione operazionale di SQL compatibile con tutti gli ultimi dbms -che infatti capiscono SQL3- la quale però non è SQL standard.

SQL non è un linguaggio turing-completo per definizione

Si', stavo cercando di confutare l'affermazione secondo la quale il PL/SQL o altri linguaggi procedurali erano necessari per risolvere qualche tipo di query che il motore non sarebbe in grado di risolvere con il suo solo SQL.

Comunque la precedente si puo' risolvere anche in SQL92, con scritture e performance meno efficienti, ma comunque corrette.

SELECT SUM(SELECT sum(QTA*PREZZO) 
      FROM   dettagli
      group by NUMF
      HAVING sum(STA*PREZZO) <1000) as MINORI,
    SUM(SELECT sum(QTA*PREZZO) 
      FROM   dettagli
      group by NUMF
      HAVING sum(STA*PREZZO) >=1000) as MAGGIORI
FROM dual

E in Sql-Server
simile, tranne la FROM dual, che occorre omettere.

[Donbabbeo]

Originariamente inviato da Donbabbeo

Eccolo:

Dipendente (ID, nome, cognome, telefono, tipo, costo orario, costo bonus)
Merci (ID, nome, costo unitario, ricavo unitario, quantità)
Ordini (ID, data richiesta, data consegna, ID cliente, ID fattorino, ricavo)
Cliente (ID, nome, partita IVA, indirizzo, telefono)
Deposita (ID dipendente, ID merce, data deposito, quantità)
Contiene (ID ordine, ID merce, quantità)
Lavora (ID dipendente, data, ora inizio, ora fine)

Dipendenti è formato da tutti i lavoratori che sono suddivisi in 2 gruppi: magazzinieri e fattorini.
Ordini sono gli ordini richiesti da un cliente, contiene pure l'id del fattorino che effettua la consegna.
Deposita collega i dipendenti alle merci ed indica quale magazziniere ha effettuato quale merce e in quale data.
Contiene collega le merci agli ordini ed indica quante merci ed in quale quantità fanno parte dell'ordine.
Lavora indica le ore lavorate per ogni data.

Quello che voglio ottenere è lo stipendio di ogni dipendente suddiviso per data. Questo viene calcolato dal numero di ore lavorate per il costo orario sommato al numero di depositi/consegne moltiplicato per il costo bonus.

Per ottenerlo con un'unica query dovrei utilizzare un left join dipendenti / ordini e dipendenti / deposita, in modo da ottenere come risultato del bonus il valore 0 nel caso il dipendente non sia del gruppo corretto. (non mi sono spiegato benissimo, il risultato è questo:

+----+--------------+-------+
| id | Fisso        | Bonus |
+----+--------------+-------+
|  1 | Magazziniere |  0.00 |
|  2 | Fattorino    |  0.00 |
|  3 | Magazziniere | 12.00 |
|  4 | Fattorino    |  0.00 |
+----+--------------+-------+

stiamo effettuando il calcolo del bonus per i magazzinieri, essendo in left join con dipendenti nel risultato avremo anche i 2 fattorini, ma ovviamente avranno bonus 0, poichè non appariranno nella tabella deposita; spero sia chiaro ora).

Questo è a grandi linee quello che vorrei ottenere.
Grazie

[gugoXX]

Puoi farci un esempio di un ipotetico risultato che vorresti ottenere alla fine, spiegando cosa sono i singoli pezzi?
Mi spiego, restano dubbi come: ti serve una data specifica da inserire nella query oppure no, oppure ti serve un insieme di date specificate nel risultato oppure no, quali sono gli input della query, se ce ne sono (es. costanti come ad esempio una data, oppure un periodo, oppure un mese, etc.etc.)

[Donbabbeo]

L'idea era quella di inserire il comando di query ed ottenere una tabella che mi facesse il riepilogo degli stipendi. Qualcosa del tipo:

"Comando query 10/01/09" ed ottenere:

+----+--------------+-------+-------+------------+--------+
| id | Tipo         | Fisso | Bonus |    Data    | TOTALE |
+----+--------------+-------+-------+------------+--------+
|  1 | Magazziniere |	 50  |    10 |  10-01-09  |     60 |
|  2 | Fattorino    |	 42  |    12 |  10-01-09  |     54 |
|  3 | Magazziniere |	 48  |    11 |  10-01-09  |     59 |
|  4 | Fattorino    |	 49  |    20 |  10-01-09  |     69 |
+----+--------------+-------+-------+------------+--------+

(le colonne sono messe un pò a casaccio). Dove:

Fisso è dato dal numero di ore lavorate nel giorno della query moltiplicato per lo stipendio orario.

Bonus è dato dal numero di consegne/depositi effettuati nel giorno della query moltiplicato per il bonus operazione del dipendente.

Totale è la somma di Fisso e Bonus.

Spero di essere stato chiaro.


----------------------------------------------------------------------


Altre due domandine:

sto cercando di inserire delle asserzioni e volevo inserirne una che impedesse l'inserimento di valori nella tabella contiene nel caso la quantità di merci richiesta sia superiore a quella disponibile e contenuta in merci. Ho pensato, invece di creare una ASSERTION, di inserire il check direttamente nella tabella contiene, qualcosa così:

CREATE TABLE contiene
(...
IDMerce ... CHECK(contiene.idmerce = (SELECT merci.id FROM merci))
Quantita ... CHECK(contiene.quantita < (SELECT merci.quantita FROM merci))
...

Il primo check in teoria sarebbe superfluo SE funzionasse il REFERENCES (cosa che non accade), mentre il secondo verifica che la quantità di merce contenuta in contiene sia inferiore a quella contenuta in merci.
Il codice è corretto visto che la tabella viene perfettamente creata senza errori, solo che non accade nulla inserendo sia un codice merce non esistente, sia una quantità superiore a quella disponibile. Come mai?

Ultima domanda (per ora ):

Ho creato un trigger che dovrebbe diminuire la quantità di merce disponibile dopo ogni inserimento in contiene. Il codice è questo:

CREATE TRIGGER diminuiscimerce
        AFTER INSERT ON contiene
FOR EACH ROW
BEGIN
        UPDATE merci
        SET merci.quantita = merci.quantita - contiene.quantita
        WHERE merci.id = contiene.idmerce
END

A me sembra corretto, però segnala un errore alla riga 8 (quella dell'END) perchè?

Ok, basta per ora

[gugoXX]

Originariamente inviato da Donbabbeo

L'idea era quella di inserire il comando di query ed ottenere una tabella che mi facesse il riepilogo degli stipendi. Qualcosa del tipo:

"Comando query 10/01/09" ed ottenere:

+----+--------------+-------+-------+------------+--------+
| id | Tipo         | Fisso | Bonus |    Data    | TOTALE |
+----+--------------+-------+-------+------------+--------+
|  1 | Magazziniere |	 50  |    10 |  10-01-09  |     60 |
|  2 | Fattorino    |	 42  |    12 |  10-01-09  |     54 |
|  3 | Magazziniere |	 48  |    11 |  10-01-09  |     59 |
|  4 | Fattorino    |	 49  |    20 |  10-01-09  |     69 |
+----+--------------+-------+-------+------------+--------+

(le colonne sono messe un pò a casaccio). Dove:

Fisso è dato dal numero di ore lavorate nel giorno della query moltiplicato per lo stipendio orario.

Bonus è dato dal numero di consegne/depositi effettuati nel giorno della query moltiplicato per il bonus operazione del dipendente.

Totale è la somma di Fisso e Bonus.

Spero di essere stato chiaro.

Ma non era la stessa domanda di prima?
Hai provato mettendo insieme le query come avevo proposto?

[Donbabbeo]

Originariamente inviato da gugoXX

Ma non era la stessa domanda di prima?
Hai provato mettendo insieme le query come avevo proposto?

Si è la stessa, ma !Piergiorgio aveva chiesto di descrivere meglio il tutto

Ho provato a fare come hai detto tu, ma c'è qualcosa che non va con i JOIN.

[gugoXX]

ma hai sostituito tutto correttamente, compresa la parentesi iniziale e quella finale?
Puoi postare il tutto, cosi' vediamo se c'e' qualche errore di sintassi?

[Donbabbeo]

Originariamente inviato da gugoXX

ma hai sostituito tutto correttamente, compresa la parentesi iniziale e quella finale?
Puoi postare il tutto, cosi' vediamo se c'e' qualche errore di sintassi?

Perdonami...
Mi cospargo il capo di cenere, avevo sbagliato a scrivere.
Funziona.
Vedrò di modificarla per ottenere il risultato senza inserire dentro la query la data. Speriamo di riuscirci...

Grazie ancora.