Prendere la scadenza di ogni ufficio con relativo tipo di rinnovo

mercoledì 29 febbraio 2012 - 16.35
Tag Elenco Tags  SQL Server 2008 R2

Jok83 Profilo | Junior Member

Ciao a tutti

nel mio DB ho una tabella Rinnovi con i seguenti campi e valori d' esempio:

CodUfficio CodRinnovo TipoRinnovo DataScadenza
1 1 test 01/05/2011
1 2 annuale 01/05/2012
2 1 test 18/11/2012
3 1 test 06/06/2010
3 2 annuale 06/06/2011
3 3 triennale 06/06/2012

Per ottenere la scadenza di ciascun ufficio seleziono la massima scadenza raggruppando per coduffico:

select codUfficio,max(DataScadenza) from rinnovi group by codUfficio

ma vorrei crearmi una stampa mettendoci anche il tipo di rinnovo che corrisponde alla scadenza, non posso aggiungere un max(TipoRinnovo) alla query precedente perchè ad
esempio con questi dati avrei:

CodUfficio Scadenza TipoRinnovo
1 01/05/2012 test (mentre qui dovrebbe esserci ANNUALE ma con max ordina alfabeticamente)
2 18/11/2012 test
3 06/06/2012 triennale

So che si può fare ma al momento non trovo la via giusta....qualcuno mi aiuta?
metto le istruzioni per creare e popolare una tabella come ho tentato di spiegare sopra:
Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

lbenaglia Profilo | Guru

>So che si può fare ma al momento non trovo la via giusta....qualcuno
>mi aiuta?

Ciao,

Ti illustro quattro possibili soluzioni:

USE tempdb; CREATE TABLE dbo.Rinnovi( CodPT int NOT NULL, CodRinnovo int NOT NULL, DataScadenza date NULL, TipoRinnovo nvarchar(50) NULL, CONSTRAINT PK_Rinnovi PRIMARY KEY(CodPT, CodRinnovo) ); INSERT dbo.Rinnovi VALUES (1, 1, '20110501', 'test') , (1, 2, '20120501', 'annuale') , (2, 1, '20121118', 'test') , (3, 1, '20100606', 'test') , (3, 2, '20110606', 'annuale') , (3, 3, '20120606', 'triennale'); /* Prima soluzione: tabella derivata che restituisce la chiave di ricerca Funziona in pratica con tutte le versioni di SQL Server */ SELECT R.CodPT, R.DataScadenza, R.TipoRinnovo FROM dbo.Rinnovi AS R JOIN ( SELECT CodPT, MAX(DataScadenza) AS DataScadenza FROM dbo.Rinnovi GROUP BY CodPT ) AS Q ON R.CodPT = Q.CodPT AND R.DataScadenza = Q.DataScadenza; /* Seconda soluzione: analoga alla precedente ma basata su una CTE Funziona con SQL Server 2005 e successivi */ WITH CTE_GetKey(CodPT, DataScadenza) AS ( SELECT CodPT, MAX(DataScadenza) FROM dbo.Rinnovi GROUP BY CodPT ) SELECT R.CodPT, R.DataScadenza, R.TipoRinnovo FROM dbo.Rinnovi AS R JOIN CTE_GetKey AS C ON R.CodPT = C.CodPT AND R.DataScadenza = C.DataScadenza; /* Terza soluzione: tabella derivata basata sul ranking del partizionamento Funziona con SQL Server 2005 e successivi */ WITH CTE_GetRank AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY CodPT ORDER BY DataScadenza DESC) AS RankCol FROM dbo.Rinnovi ) SELECT CodPT, DataScadenza, TipoRinnovo FROM CTE_GetRank WHERE RankCol = 1; /* Quarta soluzione: Utilizzo della funzione FIRST_VALUE Funziona con SQL Server 2012 e successivi */ SELECT DISTINCT CodPT , MAX(DataScadenza) OVER (PARTITION BY CodPT) AS DataScadenza , FIRST_VALUE(TipoRinnovo) OVER (PARTITION BY CodPT ORDER BY DataScadenza DESC) AS TipoRinnovo FROM dbo.Rinnovi; /* Output: CodPT DataScadenza TipoRinnovo ----------- ------------ ------------ 1 2012-05-01 annuale 2 2012-11-18 test 3 2012-06-06 triennale (3 row(s) affected) */ DROP TABLE dbo.Rinnovi;

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/

Jok83 Profilo | Junior Member

Ciao Lorenzo

ti ringrazio sono tutte è quattro validissime e una più elegante dell'altra (e naturalmente fanno quel che volevo)!

Grazie
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 !
Copyright © dotNetHell.it 2002-2017
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5