none
Query con campi calcolati sui valori precedenti RRS feed

  • Domanda

  • Ciao a tutti,
    non mi riesce di creare una Select che pescando i dati da una tabella di dettaglio,
    esegua un computo "al volo" basandosi sui valori presenti ma in righe diverse.

    Mi spiego, ho una tabella oraria così fatta:

    [TblOraria]
    Ora - Campo1
    0 - 8
    1 - 20
    2 - 15
    3 - 25
    4 - 18
    ..
    23 - 60


    Dovrei, con questa Select, fare ciò:


    SELECT Ora, Campo1, [(Campo1 - Campo1 dell'ora precedente) / 60]

    Ovviamente per la prima ora considero solo il Campo1 dell'ora in questione,
    mentre per l'ora 1, ad esempio, dovrei ottenere:

    1 - 20 - ((20-8)/60)

    cioè

    1 - 20 - 0.2


    ecc.


    Come è possibile realizzare una query del genere senza ricorrere (magari) ai cursori?

    Grazie in anticipo.

    Luigi


    • Modificato Ciupaz lunedì 21 gennaio 2013 13:12
    venerdì 18 gennaio 2013 11:05

Risposte

  • Scritto ora in 1 minuto perché non ho molto tempo ma rende comunque l'idea di quest'approccio :

    DECLARE @myTempTable TABLE
    (
    	uID INT IDENTITY(1,1) NOT NULL,
    	Ora TuoTipo,
    	Campo1 TuoTipo
    )
    
    INSERT INTO @myTempTable (Ora, Campo1)
    SELECT Ora, Campo1 FROM TBL_Oraria ORDER BY QuelCheVuoiTu
    
    DECLARE 
    	@ora TuoTipo,
    	@campo1 TuoTipo,
    	@campo1Precedente,
    	@loopCounter INT
    
    SET @loopCounter = 0
    
    WHILE Condizione
    BEGIN
    	
    	SELECT 
    		@ora = Ora,
    		@campo1 = Campo1
    	FROM @myTempTable
    	WHERE uID = @loopCounter
    
    	SELECT 
    		@campo1Precedente = Campo1
    	FROM @myTempTable
    	WHERE uID = @loopCounter - 1
    	
    	-- =========================================
    	-- Inserisci la tua logica di lavoro qui
    	-- =========================================
    	
    	@loopCounter = @loopCounter + 1
    END

    Puoi anche fare 2 tabelle variabili, aggiornare l'indice della seconda diminuendo di uno e metterle in join per poi fare un'update massiva del campo, così eviteresti direttamente i cicli.

    Ci sono vari approcci al problema, a te pensare al migliore in base alla quantità di dati su cui stai lavorando.

    Un saluto

    • Contrassegnato come risposta Ciupaz venerdì 18 gennaio 2013 13:37
    venerdì 18 gennaio 2013 13:32
  • Ciao,

    scusate se intervengo, ma, se ho capito bene, potrebbe bastarti la seguente query:

    CREATE TABLE #TblOraria (ora int, campo1 int);
    GO
    
    INSERT INTO #TblOraria ( ora, campo1 )
    VALUES
              ( 0, 8 )
    		, ( 1, 20 )
    		, ( 2, 15 )
    		, ( 3, 25 )
    		, ( 4, 18 )
    		, ( 5, 20 )
    		, ( 6, 15 )
    		, ( 7, 25 )
    		, ( 8, 18 )
    		, ( 9, 20 )
    		, ( 10, 20 )
    		, ( 11, 15 )
    		, ( 12, 25 )
    		, ( 13, 18 )
    		, ( 14, 20 )
    		, ( 15, 15 )
    		, ( 16, 25 )
    		, ( 17, 18 )
    		, ( 18, 20 )
    		, ( 19, 15 )
    		, ( 20, 25 )
    		, ( 21, 18 )
    		, ( 22, 20 )
    		, ( 23, 20 );
    GO
    
    SELECT 
    	  T1.ora
    	, T1.campo1
    	, CAST((T1.campo1 - T2.campo1) AS decimal(10,5))/60
    FROM
    	#TblOraria		T1
    	JOIN #TblOraria	T2 ON T1.ora = T2.ora + 1;
    GO
    
    -- pulizia
    DROP TABLE #TblOraria;
    GO

    Che ne dici?


    Alessandro Alpi SQL Server MVP

    • Proposto come risposta _ Luca Gaspari venerdì 18 gennaio 2013 18:34
    • Contrassegnato come risposta Ciupaz venerdì 18 gennaio 2013 19:48
    venerdì 18 gennaio 2013 14:40
    Moderatore
  • Eccolo, i numeri combaciano:

    CREATE TABLE #TblOraria (ora int, campo1 int);
    GO
    
    INSERT INTO #TblOraria ( ora, campo1 )
    VALUES
              ( 0, 8 )
    		, ( 1, 20 )
    		, ( 2, 15 )
    		, ( 3, 25 )
    		, ( 3, 30 )
    		, ( 4, 18 )
    		, ( 5, 20 )
    		, ( 6, 15 )
    		, ( 7, 25 )
    		, ( 8, 18 )
    		, ( 9, 20 )
    		, ( 11, 15 )
    		, ( 12, 25 )
    		, ( 13, 18 )
    		, ( 14, 20 )
    		, ( 15, 15 )
    		, ( 16, 25 )
    		, ( 17, 18 )
    		, ( 18, 20 )
    		, ( 19, 15 )
    		, ( 20, 25 )
    		, ( 21, 18 )
    		, ( 22, 20 )
    		, ( 23, 20 );
    GO
    
    ;WITH Ore (Ora, Valore, Indice) AS 
    (
    	SELECT 
    	   T1.ora
    	 , T1.campo1
    	 , ROW_NUMBER() OVER (ORDER BY T1.ora)
    	FROM
    	 #TblOraria  T1
    )
    
    
    	SELECT 
    	   T1.Ora
    	 , T1.Valore
    	 , ISNULL(CAST((T1.Valore - T2.Valore) AS decimal(10,5)), 0) AS Calcolato
    	FROM
    	 Ore			T1
    	 LEFT JOIN Ore	T2 ON T1.Indice = T2.Indice + 1;
    
    -- pulizia
    DROP TABLE #TblOraria;
    GO
    togli e metti le insert values se vuoi provare i casi. Per questo esempio c'è l'ora in più (sul 3).


    Alessandro Alpi SQL Server MVP

    • Contrassegnato come risposta Ciupaz lunedì 21 gennaio 2013 19:44
    lunedì 21 gennaio 2013 13:26
    Moderatore

Tutte le risposte

  • Potresti mettere tutto in una table variable con la stessa struttura ma con un campo ID autoincrementale, ciclare con un loop WHILE (che è velocissimo rispetto al cursore) e gestire i puntamenti dei calcoli con un contatore qualsiasi :)


    Un saluto

    venerdì 18 gennaio 2013 11:31
  • Avresti mica sottomano un esempio Luca?

    L

    venerdì 18 gennaio 2013 13:13
  • Scritto ora in 1 minuto perché non ho molto tempo ma rende comunque l'idea di quest'approccio :

    DECLARE @myTempTable TABLE
    (
    	uID INT IDENTITY(1,1) NOT NULL,
    	Ora TuoTipo,
    	Campo1 TuoTipo
    )
    
    INSERT INTO @myTempTable (Ora, Campo1)
    SELECT Ora, Campo1 FROM TBL_Oraria ORDER BY QuelCheVuoiTu
    
    DECLARE 
    	@ora TuoTipo,
    	@campo1 TuoTipo,
    	@campo1Precedente,
    	@loopCounter INT
    
    SET @loopCounter = 0
    
    WHILE Condizione
    BEGIN
    	
    	SELECT 
    		@ora = Ora,
    		@campo1 = Campo1
    	FROM @myTempTable
    	WHERE uID = @loopCounter
    
    	SELECT 
    		@campo1Precedente = Campo1
    	FROM @myTempTable
    	WHERE uID = @loopCounter - 1
    	
    	-- =========================================
    	-- Inserisci la tua logica di lavoro qui
    	-- =========================================
    	
    	@loopCounter = @loopCounter + 1
    END

    Puoi anche fare 2 tabelle variabili, aggiornare l'indice della seconda diminuendo di uno e metterle in join per poi fare un'update massiva del campo, così eviteresti direttamente i cicli.

    Ci sono vari approcci al problema, a te pensare al migliore in base alla quantità di dati su cui stai lavorando.

    Un saluto

    • Contrassegnato come risposta Ciupaz venerdì 18 gennaio 2013 13:37
    venerdì 18 gennaio 2013 13:32
  • Bene, grazie Luca.

    Luigi

    venerdì 18 gennaio 2013 13:37
  • You are wellcome (:
    venerdì 18 gennaio 2013 13:45
  • Ciao,

    scusate se intervengo, ma, se ho capito bene, potrebbe bastarti la seguente query:

    CREATE TABLE #TblOraria (ora int, campo1 int);
    GO
    
    INSERT INTO #TblOraria ( ora, campo1 )
    VALUES
              ( 0, 8 )
    		, ( 1, 20 )
    		, ( 2, 15 )
    		, ( 3, 25 )
    		, ( 4, 18 )
    		, ( 5, 20 )
    		, ( 6, 15 )
    		, ( 7, 25 )
    		, ( 8, 18 )
    		, ( 9, 20 )
    		, ( 10, 20 )
    		, ( 11, 15 )
    		, ( 12, 25 )
    		, ( 13, 18 )
    		, ( 14, 20 )
    		, ( 15, 15 )
    		, ( 16, 25 )
    		, ( 17, 18 )
    		, ( 18, 20 )
    		, ( 19, 15 )
    		, ( 20, 25 )
    		, ( 21, 18 )
    		, ( 22, 20 )
    		, ( 23, 20 );
    GO
    
    SELECT 
    	  T1.ora
    	, T1.campo1
    	, CAST((T1.campo1 - T2.campo1) AS decimal(10,5))/60
    FROM
    	#TblOraria		T1
    	JOIN #TblOraria	T2 ON T1.ora = T2.ora + 1;
    GO
    
    -- pulizia
    DROP TABLE #TblOraria;
    GO

    Che ne dici?


    Alessandro Alpi SQL Server MVP

    • Proposto come risposta _ Luca Gaspari venerdì 18 gennaio 2013 18:34
    • Contrassegnato come risposta Ciupaz venerdì 18 gennaio 2013 19:48
    venerdì 18 gennaio 2013 14:40
    Moderatore
  • Direi che è l'uovo di Colombo.
    L'ho solo leggermente modificata per fargli uscire anche il record relativo
    all'ora zero, in cui vale appunto 0, in questo modo:

    SELECT
       T1.ora
     , T1.campo1
     , ISNULL(CAST((T1.campo1 - T2.campo1) AS decimal(10,5)), 0) AS Calcolato
    FROM
     TblOraria  T1
     LEFT JOIN TblOraria T2 ON T1.ora = T2.ora + 1;

    Grazie mille Alessandro.


    Luigi

    venerdì 18 gennaio 2013 19:47
  • Grazie mille Alessandro.

    non c'è di che :)

    Alessandro Alpi SQL Server MVP

    venerdì 18 gennaio 2013 23:19
    Moderatore
  • Una piccola aggiunta (ma nemmeno tanto piccola).
    Dovrei gestire anche il cambio ora legale/solare.

    Nel caso di un'ora in più, questa mi arriverebbe come 3bis (dovrei
    allora cambiare il datatype da INT a VARCHAR), mentre con un'ora in meno ci
    sarebbe il passaggio diretto dall'ora 2 all'ora 4.

    Via codice C# potrei farmi passare alla stored procedure una variabile
    che mi indica quale caso devo gestire, ma a livello di stored
    non saprei come implementare la cosa.

    Qualcuno ha un'idea?

    Luigi

    sabato 19 gennaio 2013 14:37
  • Il fatto di cambiare da int a varchar non è così necessario, potresti fare un flag aggiuntivo che discrimini la ripetizione del campo senza cambiare tipo, che non serve, anzi, rischia di essere deleterio.

    La domanda però è:

    - se ci sono due ore uguali, cosa vuoi ottenere in output? La somma? Ripetute come se non fossero la stessa ora?

    - se c'è il gap dell'ora invece, vuoi fare la somma con un record fake? oppure vuoi sommare due ore staccate?

    ciao


    Alessandro Alpi SQL Server MVP

    lunedì 21 gennaio 2013 10:01
    Moderatore
  • Se mi arrivano 2 ore uguali, ad esempio:


    [TblOraria]
    Ora - Campo1
    0 - 8
    1 - 20
    2 - 15
    3 - 25
    3bis- 21
    4 - 18
    ...

    il campo calcolato dell'ora 4 dovrebbe essere la somma di 3 + 3bis (invece di 3+2).
    e il campo calcolato 3bis sarebbe la somma di 3+2.

     

    Nel caso di mancanza di un'ora:


    [TblOraria]
    Ora - Campo1
    0 - 8
    1 - 20
    2 - 15
    4 - 18
    ...


    il campo calcolato dell'ora 4 sarebbe la somma di 2+1 (invece di 3+2).

    Queste sono le regole. Beati i paesi che non hanno l'ora legale...

    L

    lunedì 21 gennaio 2013 10:14

  • il campo calcolato dell'ora 4 dovrebbe essere la somma di 3 + 3bis (invece di 3+2).
    e il campo calcolato 3bis sarebbe la somma di 3+2.


    il campo calcolato dell'ora 4 sarebbe la somma di 2+1 (invece di 3+2).

    Un secondo.. somma o sottrae? Nell'esempio prima parliamo di sottrazione.. scusa per capire come fare la query mi manca questo step..


    Alessandro Alpi SQL Server MVP

    lunedì 21 gennaio 2013 11:00
    Moderatore
  • Sottrazione, scusa, hai ragione, sempre sottrazione.

    Luigi

    lunedì 21 gennaio 2013 11:07
  • quindi 3bis - 3 e 2-1? 

    Alessandro Alpi SQL Server MVP

    lunedì 21 gennaio 2013 11:11
    Moderatore
  • Yes, esattamente.

    L

    lunedì 21 gennaio 2013 11:16
  • Sai che qualcosa non mi torna?

    Riesci a darmi un resultset di output che vorresti ottenere?

    Non vorrei che nel caso di un'ora in più non fosse più corretto fare (3bis + 3) - 2, ovvero:

    1, 10

    2, 15

    3, 20

    3, 15

    diventerebbe:

    1, 10, counter = 0

    2, 15, counter(15-10) = 5

    3, (20+15), counter(20+15)-15 = 20

    è così? invece nel caso dell'ora mancante (immagina il set senza il 2 e senza il 3 ripetuto, prendiamo il primo dei 3):

    1, 10, counter = 0

    3, 20, counter(20-10) = 10

    se per te è corretto così provo a chiudere la query, altrimenti posta proprio il risultato finale coi valori, così evitiamo ogni misunderstanding. Non vorrei nemmeno complicare troppo il giro..


    Alessandro Alpi SQL Server MVP

    lunedì 21 gennaio 2013 12:25
    Moderatore
  • Provo a basarmi sul primo esempio:


    [TblOraria]
    Ora - Campo1
    0 - 8
    1 - 20
    2 - 15
    3 - 25
    4 - 18
     ..
    23 - 60


    che nel caso "normale" diventerebbe (escludiamo per ora la divisione per 60, giusto per
    non complicarci troppo la vita):

    La regola è sempre la sottrazione, ovvero [Campo1 - Campo1 dell'ora precedente)]

    Ora - Campo1 - CampoCalcolato
    0 - 8 - 8
    1 - 20 - 12
    2 - 15 - (-5)
    3 - 25 - 10
    4 - 18 - (-7)
    5 - 10 - (-8)
     ..
    23 - 60


    Nel caso di un'ora in più avrei l'occorrenza 3bis:


    Ora - Campo1
    0 - 8
    1 - 20
    2 - 15
    3 - 25
    3bis - 30
    4 - 18
    ...
    23 - 60

    che mi farebbe ottenere:


    Ora - Campo1 - CampoCalcolato
    0 - 8 - 8
    1 - 20 - (-12)
    2 - 15 - (-5)
    3 - 25 - 10
    3bis - 30 - 5
    4 - 18 - (-12)
    ...
    23 - 60


    Mentre nel caso dell'ora mancante, in cui avrei:


    Ora - Campo1
    0 - 8
    1 - 20
    2 - 15
    4 - 18
    ...
    23 - 60


    Dovrei ottenere:


    Ora - Campo1 - CampoCalcolato
    0 - 8 - 8
    1 - 20 - (-12)
    2 - 15 - (-5)
    4 - 18 - 3
    ...
    23 - 60


    Purtroppo questa è la complicazione di avere le ore legali.

    Luigi

    lunedì 21 gennaio 2013 12:51

  • Nel caso di un'ora in più avrei l'occorrenza 3bis:


    Ora - Campo1
    0 - 8
    1 - 20
    2 - 15
    3 - 25
    3bis - 30
    4 - 18
    ...
    23 - 60

    che mi farebbe ottenere:


    Ora - Campo1 - CampoCalcolato
    0 - 8 - 8
    1 - 20 - (-12)
    2 - 15 - (-5)
    3 - 25 - 10
    3bis - 30 - 5
    4 - 18 - (-12)
    ...
    23 - 60

    il primo -12 è errato, mi aspettavo un 12 (non meno) dato da 20-8. Il 4, che è -12 da quanto indichi tu, non riesco a capire da dove lo ricavi. Ho provato 18-(3+3bis).. ma niente. Riesco solo a ricavarlo se faccio l'operazione come se fosse normale, ovvero:

    al 3 ho 25+15 = 10

    al 3bis ho 30-25 = 5

    al 4 ho 18-30 = -12

    in tal caso secondo me è come per il "normale" non trovi?


    Alessandro Alpi SQL Server MVP

    lunedì 21 gennaio 2013 13:04
    Moderatore
  • Sì non è -12, ma il primo trattino (se distaccato dal numero) indica semplicemente un posizionatore per mettere i campi sotto le colonne giuste.
    Quelli negativi li ho messi tra parentesi, così (forse) si capisce meglio.
    • Modificato Ciupaz lunedì 21 gennaio 2013 13:14
    lunedì 21 gennaio 2013 13:13
  • Eccolo, i numeri combaciano:

    CREATE TABLE #TblOraria (ora int, campo1 int);
    GO
    
    INSERT INTO #TblOraria ( ora, campo1 )
    VALUES
              ( 0, 8 )
    		, ( 1, 20 )
    		, ( 2, 15 )
    		, ( 3, 25 )
    		, ( 3, 30 )
    		, ( 4, 18 )
    		, ( 5, 20 )
    		, ( 6, 15 )
    		, ( 7, 25 )
    		, ( 8, 18 )
    		, ( 9, 20 )
    		, ( 11, 15 )
    		, ( 12, 25 )
    		, ( 13, 18 )
    		, ( 14, 20 )
    		, ( 15, 15 )
    		, ( 16, 25 )
    		, ( 17, 18 )
    		, ( 18, 20 )
    		, ( 19, 15 )
    		, ( 20, 25 )
    		, ( 21, 18 )
    		, ( 22, 20 )
    		, ( 23, 20 );
    GO
    
    ;WITH Ore (Ora, Valore, Indice) AS 
    (
    	SELECT 
    	   T1.ora
    	 , T1.campo1
    	 , ROW_NUMBER() OVER (ORDER BY T1.ora)
    	FROM
    	 #TblOraria  T1
    )
    
    
    	SELECT 
    	   T1.Ora
    	 , T1.Valore
    	 , ISNULL(CAST((T1.Valore - T2.Valore) AS decimal(10,5)), 0) AS Calcolato
    	FROM
    	 Ore			T1
    	 LEFT JOIN Ore	T2 ON T1.Indice = T2.Indice + 1;
    
    -- pulizia
    DROP TABLE #TblOraria;
    GO
    togli e metti le insert values se vuoi provare i casi. Per questo esempio c'è l'ora in più (sul 3).


    Alessandro Alpi SQL Server MVP

    • Contrassegnato come risposta Ciupaz lunedì 21 gennaio 2013 19:44
    lunedì 21 gennaio 2013 13:26
    Moderatore
  • Direi che funziona. Ci penso poi io, lato C#, ad eliminare il "bis" nel dato della terza ora doppia, e lato SQL la tua query va bene.

    Grazie mille Alessandro.

    Luigi

    lunedì 21 gennaio 2013 19:46