Remove From My Forums

SQL 2005 - 2000. Query di aggiornamento con funzioni di aggregazione

Domanda
0

Registrati per votare
Ho 3 tabelle: TabellaA, TabellaB e TabellaC. Semplificando in base al problema, le tabelle sono così strutturate:

TabellaA: IdOrdineProduzione|DataConsegnaOrdine

TabellaB: IdOrdineProduzione|IdDoc|IdRiga

TabellaC: IdDoc, IdRiga e DataConsegna

In pratica la TabellaB fa da JOIN tra TabellaA e TabellaC

Il mio obiettivo è aggiornare il campo DataConsegnaOrdine di TabellaA, con la minore tra le DataConsegna della TabellaC, ovviamente sfruttando i legami tra IdOrdineProduzione, IdDoc e IdRiga.

Come posso fare?

C'è da tenere conto inoltre che se tra le varie date ce n'è una a null, la data in TabellaA deve rimanere NULL. Usando la funzione MIN, una data non compilata viene presa come valore minore?

Spero di aver spiegato bene il problema.

Io ho scrit query, ma credo che mi possa andar bene anche una stored procedure. Deve girare sia su sql2000 che sql2005.

Grazie a tutti.
- Modificato patapatty mercoledì 14 settembre 2011 10:51
mercoledì 14 settembre 2011 10:49

Risposta

|

Citazione

Tutte le risposte

Registrati per votare

Il mio obiettivo è aggiornare il campo DataConsegnaOrdine di TabellaA, con la minore tra le DataConsegna della TabellaC, ovviamente sfruttando i legami tra IdOrdineProduzione, IdDoc e IdRiga.

Come posso fare?

Ciao,

Prova a dare un'occhiata al seguente esempio:

USE tempdb;

CREATE TABLE dbo.A(
IdOrdineProduzione int NOT NULL,
DataConsegnaOrdine datetime NULL,
CONSTRAINT PK_A PRIMARY KEY(IdOrdineProduzione)
);

CREATE TABLE dbo.C(
IdDoc int NOT NULL,
IdRiga int NOT NULL,
DataConsegna datetime NULL,
CONSTRAINT PK_C PRIMARY KEY(IdDoc, IdRiga)
);

CREATE TABLE dbo.B(
IdOrdineProduzione int NOT NULL,
IdDoc int NOT NULL,
IdRiga int NOT NULL,
CONSTRAINT PK_B PRIMARY KEY(IdOrdineProduzione, IdDoc, IdRiga),
CONSTRAINT FK_BA FOREIGN KEY(IdOrdineProduzione)
REFERENCES dbo.A(IdOrdineProduzione),
CONSTRAINT FK_BC FOREIGN KEY(IdDoc, IdRiga)
REFERENCES dbo.C(IdDoc, IdRiga)
);

INSERT dbo.A(IdOrdineProduzione)
VALUES (1), (2), (3);

INSERT dbo.C
VALUES (10, 1, '2011-01-01'), (10, 2, '2011-02-02'), (10, 3, '2011-03-03')
     , (20, 1, '2011-04-04'), (20, 2, NULL)
     , (30, 1, '2011-05-05');

INSERT dbo.B
VALUES (1, 10, 1), (1, 10, 2), (1, 10, 3)
     , (2, 20, 1), (2, 20, 2)
     , (3, 30, 1);

UPDATE A
SET DataConsegnaOrdine = Q.DataConsegna
FROM dbo.A
JOIN (
    SELECT B.IdOrdineProduzione
         , MIN(COALESCE(C.DataConsegna, '1753-01-01')) AS DataConsegna
    FROM dbo.B
    JOIN dbo.C
    ON B.IdDoc = C.IdDoc
    AND B.IdRiga = C.IdRiga
    GROUP BY B.IdOrdineProduzione
) AS Q
ON A.IdOrdineProduzione = Q.IdOrdineProduzione
WHERE Q.DataConsegna <> '1753-01-01';

SELECT *
FROM dbo.A;

/* Output:

IdOrdineProduzione DataConsegnaOrdine
------------------ -----------------------
1                  2011-01-01 00:00:00.000
2                  NULL
3                  2011-05-05 00:00:00.000

(3 row(s) affected)

*/

DROP TABLE dbo.B, dbo.A, dbo.C;

In sostanza la tabella derivata calcola la DataConsegna minima per ogni IdOrdineProduzione e nel caso sia NULL assegna il valore minimo per il data type datetime. Il comando di UPDATE aggiorna la colonna DataConsegnaOrdine della tabella A prendendo in esame le righe corrispondenti la cui DataConsegna sia diversa dal valore minimo (ovvero NULL).

Ciao!

Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo
http://social.technet.microsoft.com/Forums/it-IT/sqlserverit

mercoledì 14 settembre 2011 12:37

Moderatore

Registrati per votare

Ciao,

se non devi per forza usare funzioni di aggregazione, puoi usare una SELECT TOP 1 ... ordinata per DataConsegna.
Guarda questo esempio in cui ho preso in prestito parte del codice postato da Lorenzo :-)

USE tempdb;

CREATE TABLE dbo.A(
IdOrdineProduzione int NOT NULL,
DataConsegnaOrdine datetime NULL,
CONSTRAINT PK_A PRIMARY KEY(IdOrdineProduzione)
);

CREATE TABLE dbo.C(
IdDoc int NOT NULL,
IdRiga int NOT NULL,
DataConsegna datetime NULL,
CONSTRAINT PK_C PRIMARY KEY(IdDoc, IdRiga)
);

CREATE TABLE dbo.B(
IdOrdineProduzione int NOT NULL,
IdDoc int NOT NULL,
IdRiga int NOT NULL,
CONSTRAINT PK_B PRIMARY KEY(IdOrdineProduzione, IdDoc, IdRiga),
CONSTRAINT FK_BA FOREIGN KEY(IdOrdineProduzione)
REFERENCES dbo.A(IdOrdineProduzione),
CONSTRAINT FK_BC FOREIGN KEY(IdDoc, IdRiga)
REFERENCES dbo.C(IdDoc, IdRiga)
);

INSERT dbo.A(IdOrdineProduzione)
VALUES (1);
INSERT dbo.A(IdOrdineProduzione)
VALUES (2);
INSERT dbo.A(IdOrdineProduzione)
VALUES (3);

INSERT dbo.C
VALUES (10, 1, '2011-01-01');
INSERT dbo.C
VALUES (10, 2, '2011-02-02');
INSERT dbo.C
VALUES (10, 3, '2011-03-03');
INSERT dbo.C
VALUES (20, 1, '2011-04-04');
INSERT dbo.C
VALUES (20, 2, NULL);
INSERT dbo.C
VALUES (30, 1, '2011-05-05');

INSERT dbo.B
VALUES (1, 10, 1);
INSERT dbo.B
VALUES (1, 10, 2);
INSERT dbo.B
VALUES (1, 10, 3);
INSERT dbo.B
VALUES (2, 20, 1);
INSERT dbo.B
VALUES (2, 20, 2);
INSERT dbo.B
VALUES (3, 30, 1);

UPDATE A
SET DataConsegnaOrdine = (SELECT TOP 1 C.DataConsegna
                          FROM dbo.B
                          JOIN dbo.C
                          ON B.IdDoc = C.IdDoc
                          AND B.IdRiga = C.IdRiga
                          WHERE B.IdOrdineProduzione = A.IdOrdineProduzione
                          ORDER BY C.DataConsegna)
FROM dbo.A

SELECT *
FROM dbo.A;

/* Output:

IdOrdineProduzione DataConsegnaOrdine
------------------ -----------------------
1                  2011-01-01 00:00:00.000
2                  NULL
3                  2011-05-05 00:00:00.000

(Righe interessate: 3)

*/

DROP TABLE dbo.B,dbo.C,dbo.A;

Ciao
Giorgio Rancati

mercoledì 14 settembre 2011 14:35

Moderatore

0

Registrati per votare

Grazie mille a entrambi per le risposte.

Purtroppo il cliente non mi aveva spiegato bene la situazione e c'è un accorgimento in più.

Solo nel caso in cui TUTTE le date siano NULL allora la data in TabellaA deve essere impostata con NULL. Se invece ci sono alcune date Null e altre impostate, allora devo prendere la minore di queste.

Usando la soluzione di Giorgio, a questo punto non ottengo quello che mi serve, in quanto se ci fosse anche solo una data NULL, verrebbe presa per prima e quindi usata per l'aggiornamento. Ma credo che non funzioni nemmeno quella di Lorenzo.

A proposito, la funzione COALESCE cosa fa in pratica? Se ho capito bene non sostituisce il valore NUll con quello indicato, ma lo considera come tale.

Se mi potete aiutare anche se vi ho fatto perdere tempo non avendovi dato da subito tutte le specifiche corrette, mi fareste un grosso favore.

Un'idea, anche se penso sia un po' grossolana, è quella di usare la funzione Min senza il COLAESCE (oppure SELECT TOP 1 ... ordinata per DataConsegna escludendo quelle a NULL) e poi in un secondo momento, aggiornare a NULL solo se TUTTE le date sono NULL. Ma come faccio a sapere se tutte le date legate a IdOrdineProduzione sono a Null?

Grazie

mercoledì 14 settembre 2011 17:19

Risposta

|

Citazione

Registrati per votare

Ciao,

come hai già intuito, per il nuovo scenario basta escludere le date Null dalla subquery

UPDATE A
SET DataConsegnaOrdine = (SELECT TOP 1 C.DataConsegna
                          FROM dbo.B
                          JOIN dbo.C
                          ON B.IdDoc = C.IdDoc
                          AND B.IdRiga = C.IdRiga
                          WHERE B.IdOrdineProduzione =  A.IdOrdineProduzione
                          AND C.DataConsegna IS NOT NULL
                          ORDER BY C.DataConsegna)
FROM dbo.A

in questo modo se tutte le date di un IdOrdineProduzione
sono null la subquery non restituisce record e il campo viene aggiornato a NULL, altrimenti viene aggiornato con la data più bassa.

Per la funzione coalesce qui trovi tutte le info. COALESCE (Transact-SQL)

Ciao
Giorgio Rancati

mercoledì 14 settembre 2011 18:07

Moderatore

Registrati per votare

Solo nel caso in cui TUTTE le date siano NULL allora la data in TabellaA deve essere impostata con NULL. Se invece ci sono alcune date Null e altre impostate, allora devo prendere la minore di queste.

Con una piccola modifica otterrai il risultato desiderato:

USE tempdb;

CREATE TABLE dbo.A(
IdOrdineProduzione int NOT NULL,
DataConsegnaOrdine datetime NULL,
CONSTRAINT PK_A PRIMARY KEY(IdOrdineProduzione)
);

CREATE TABLE dbo.C(
IdDoc int NOT NULL,
IdRiga int NOT NULL,
DataConsegna datetime NULL,
CONSTRAINT PK_C PRIMARY KEY(IdDoc, IdRiga)
);

CREATE TABLE dbo.B(
IdOrdineProduzione int NOT NULL,
IdDoc int NOT NULL,
IdRiga int NOT NULL,
CONSTRAINT PK_B PRIMARY KEY(IdOrdineProduzione, IdDoc, IdRiga),
CONSTRAINT FK_BA FOREIGN KEY(IdOrdineProduzione)
REFERENCES dbo.A(IdOrdineProduzione),
CONSTRAINT FK_BC FOREIGN KEY(IdDoc, IdRiga)
REFERENCES dbo.C(IdDoc, IdRiga)
);

INSERT dbo.A(IdOrdineProduzione)
VALUES (1), (2), (3);

INSERT dbo.C
VALUES (10, 1, '2011-01-01'), (10, 2, '2011-02-02'), (10, 3, '2011-03-03')
     , (20, 1, '2011-04-04'), (20, 2, NULL)
     , (30, 1, NULL), (30, 2, NULL);

INSERT dbo.B
VALUES (1, 10, 1), (1, 10, 2), (1, 10, 3)
     , (2, 20, 1), (2, 20, 2)
     , (3, 30, 1), (3, 30, 2);

UPDATE A
SET DataConsegnaOrdine = Q.DataConsegna
FROM dbo.A
JOIN (
    SELECT B.IdOrdineProduzione
         , MIN(C.DataConsegna) AS DataConsegna
    FROM dbo.B
    JOIN dbo.C
    ON B.IdDoc = C.IdDoc
    AND B.IdRiga = C.IdRiga
    WHERE C.DataConsegna IS NOT NULL
    GROUP BY B.IdOrdineProduzione
) AS Q
ON A.IdOrdineProduzione = Q.IdOrdineProduzione;

SELECT *
FROM dbo.A;

/* Output:

IdOrdineProduzione DataConsegnaOrdine
------------------ -----------------------
1                  2011-01-01 00:00:00.000
2                  2011-04-04 00:00:00.000
3                  NULL

(3 row(s) affected)

*/

DROP TABLE dbo.B, dbo.A, dbo.C;

Come già indicato da Giorgione è sufficiente escludere le righe con DataConsegna valorizzata a NULL.

Ciao!

Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo
http://social.technet.microsoft.com/Forums/it-IT/sqlserverit

Contrassegnato come risposta patapatty giovedì 15 settembre 2011 07:24
Contrassegno come risposta annullato patapatty giovedì 15 settembre 2011 07:32

mercoledì 14 settembre 2011 19:27

Moderatore

0

Registrati per votare
Di nuovo grazie mille a entrambi.

C'è ancora un problema:

La soluzione di Giorgio aggiorna a NULL la data della TabellaA nel caso in cui ci siano solo date NULL, ma lo fa anche se NON ci sono link con le altre tabelle. Invece in questo caso quella data non dovrebbe essere modificata

La soluzione di Lorenzo invece fa l'aggiornamento solo se trova legami, però se le date sono tutte a NULL, non aggiorna a NULL la data in TabellaA.

Nessuna idea per riuscire a ottenere entrambe le cose?

Come idea a me viene:

- uso la soluzione di Lorenzo

- poi faccio un'ulteriore aggiornamento x aggiornare a NULL la data in TabellaA solo se TUTTE le date sono NULL. Ma come faccio a sapere se tutte le date legate a IdOrdineProduzione sono a Null? Pensavo di verificare con una funzione di aggregazione se il numero di date per ogni IdOrdineProduzione coincide con il numero di date a NULL. E' fattibile?

Grazie

Patrizia
- Modificato patapatty giovedì 15 settembre 2011 07:47
giovedì 15 settembre 2011 07:24

Risposta

|

Citazione
0

Registrati per votare

Forse ho trovato la soluzione.

Uso la soluzione di Giorgio aggiungendo alla fine

WHERE A.IdOrdineProduzione IN (SELECT IdOrdineProduzione FROM TabellaB)

Così mi sembra funzioni.

Patrizia

giovedì 15 settembre 2011 07:53

Risposta

|

Citazione

Prodotti

Resources

Solutions

Aggiornamenti

Valutazioni

Siti correlati

Formazione

Certificazioni

Ulteriori risorse

Per prodotto

Altri collegamenti

Non professionisti IT

Con più domande

SQL 2005 - 2000. Query di aggiornamento con funzioni di aggregazione

Domanda

Tutte le risposte