none
[sql server] Estrarre righe consecutive con minimo n righe RRS feed

  • Domanda

  • Ciao a tutti
    Grazie al vostro aiuto sono riuscito ad estrarre tutte le tariffe per le strutture presenti nel mio db:

    ALTER FUNCTION [dbo].[Tariffe] 
    (
    	@CodiceStruttura NVarchar(30),
    	@CodiceCamera NVarchar(30),
    	@StartDate DATE, 
    	@EndDate DATE,
    	@StatusStruttura NVarchar(5)
    )
    RETURNS 
    @Risultato TABLE 
    (
    	codice_struttura NVarchar(30),
    	struttura NVarchar(250),
    	codice_camera NVarchar(30),
    	camera NVarchar(250),
    	modello Varchar(250),
    	livello Varchar(250),	
    	dal DATETIME2 NOT NULL,
    	al DATETIME2 NOT NULL,
    	tariffa Numeric(18,2),
    	n_min int,
    	n_max int
    )
    AS
    BEGIN
    	-- Tabella temporanea
     DECLARE @T2 AS TAble
    (
    	codice_struttura Varchar(50),
    	struttura Varchar(250),
    	codice_camera Varchar(50),
    	camera Varchar(250),
    	data Datetime,
    	modello Varchar(250),
    	livello Varchar(250),	
    	tariffa Numeric(18,2),
    	n_camere int, 
    	diffGiorni int,
    	gruppo Int
     )
    
     -- Gruppo all'interno di stessa Camera,Livello,Tariffa con date consecutive
     DECLARE @Gruppo Int = 0,
             @Righe Int = 0;
    
    		--Inserisco dati in tabella temporanea calcolando la differenza giorni dalla riga precedente
    		WITH CTE_Tariffe (codice_struttura, struttura, codice_camera, camera, data, modello, livello, tariffa, n_camere) AS
    		(
    		SELECT tb6.codice_struttura, tb6.struttura, tb1.codice_camera, tb2.camera, tb3.giorno as Data, tb4.modello, tb5.livello, tb5.costo as tariffa, tb4.camere as n_camere
    
    		FROM tbStrutture tb6 
    		INNER JOIN tbCamere tb1 ON tb6.codice_struttura=tb1.fk_struttura AND tb6.status<>'CANC' AND tb1.status<>'CANC'
    		INNER JOIN tbCamere_Lingua tb2 ON tb1.codice_camera=tb2.fk_id AND tb2.lingua='it'
    		INNER JOIN tbCamereGiorni tb3 ON tb3.fk_camera=tb1.codice_camera
    		INNER JOIN tbCamereDisponibilita tb4 ON tb4.fk_giorno=tb3.id
    		INNER JOIN tbCamereTariffe tb5 ON tb5.fk_disponibilita=tb4.id AND tb5.status='ON'
    		WHERE (codice_struttura=@CodiceStruttura OR COALESCE(@CodiceStruttura, '')='') AND (codice_camera=@CodiceCamera OR COALESCE(@CodiceCamera, '')='') AND (tb6.status=@StatusStruttura OR COALESCE(@StatusStruttura, '')='') AND (tb3.giorno >= @StartDate AND tb3.giorno <= @EndDate OR COALESCE(@StartDate, '')='') 
    		)
    		INSERT INTO @T2 (codice_struttura, struttura, codice_camera, camera, data, modello, livello, tariffa, n_camere, diffGiorni, gruppo)
    		SELECT codice_struttura, struttura, codice_camera, camera, data, modello, livello, tariffa, n_camere
    			  ,DATEDIFF(d,lag(Data,1,DATEADD(d,-1,Data)) OVER (ORDER BY codice_struttura, camera, modello, livello,Tariffa,Data),Data)
    			  ,0
    		FROM   CTE_Tariffe 
    		ORDER BY codice_struttura, camera, modello, livello,Tariffa,Data;
    
    		SET @Righe=@@ROWCOUNT;
    
    		-- Incremento il gruppo se la data non è consecutiva
    		UPDATE T
    		   SET @Gruppo = Gruppo = @Gruppo  + CASE WHEN DiffGiorni=1 THEN 0 ELSE 1 END
    		  FROM (SELECT TOP (@Righe) Gruppo,DiffGiorni 
    				FROM @T2 
    				ORDER BY codice_struttura, camera, modello, livello,Tariffa,Data) AS T
    
    	INSERT INTO @Risultato
    
    	  SELECT codice_struttura, struttura, codice_camera, camera, modello, livello,MIN(Data)as Dal,MAX(Data) as Al,Tariffa, MIN(n_camere), MAX(n_camere)
    		FROM @T2
    		GROUP BY codice_struttura, struttura, codice_camera, camera, modello, livello,Tariffa,Gruppo
    		ORDER BY codice_struttura, struttura, codice_camera, camera, modello, livello, dal, al,Tariffa;
    	
    	RETURN 
    END

    Ora, partendo da questa funzione o da una nuova vorrei estrarre tutte le tariffe comprese in un intervallo di tempo, non importa se hanno un valore di 'Tariffa' diverso l'mportante che il numero di giorni consecutivi sia uguale ad 'n'.

    Con la funzione precedente ottengo:
    Supponendo di avere:

    Camera | Livello | Data | Tariffa
    Standard | rimborsabile | 01/01/2017 | 50
    Standard | rimborsabile | 02/01/2017 | 50
    Standard | rimborsabile | 03/01/2017 | 50
    Standard | rimborsabile | 04/01/2017 | 60
    Standard | rimborsabile | 05/01/2017 | 60
    Standard | rimborsabile | 06/01/2017 | 60
    Standard | rimborsabile | 08/01/2017 | 60
    Standard | rimborsabile | 09/01/2017 | 60
    Standard | rimborsabile | 10/01/2017 | 60
    Standard | no rimborsabile | 01/01/2017 | 70
    Standard | no rimborsabile | 02/01/2017 | 70
    Standard | no rimborsabile | 03/01/2017 | 70
    Standard | no rimborsabile | 04/01/2017 | 80
    Standard | no rimborsabile | 05/01/2017 | 80
    Standard | no rimborsabile | 06/01/2017 | 80
    Standard | no rimborsabile | 08/01/2017 | 90
    Standard | no rimborsabile | 09/01/2017 | 90
    Standard | no rimborsabile | 10/01/2017 | 90

    Ottengo come risultato questo:
    Camera | Livello | Dal | Al | Tariffa

    Standard | rimborsabile | 01/01/2017 | 03/01/2017 | 50
    Standard | rimborsabile | 04/01/2017 | 06/01/2017 | 60
    Standard | rimborsabile | 08/01/2017 | 10/01/2017 | 60
    Standard | no rimborsabile | 01/01/2017 | 03/01/2017 | 70
    Standard | no rimborsabile | 04/01/2017 | 06/01/2017 | 80
    Standard | no rimborsabile | 08/01/2017 | 10/01/2017 | 90

    Ora invece vorrei:
    imposto n=5

    Standard | rimborsabile | 01/01/2017 | 50
    Standard | rimborsabile | 02/01/2017 | 50
    Standard | rimborsabile | 03/01/2017 | 50
    Standard | rimborsabile | 04/01/2017 | 60
    Standard | rimborsabile | 05/01/2017 | 60

    Standard | no rimborsabile | 01/01/2017 | 70
    Standard | no rimborsabile | 02/01/2017 | 70
    Standard | no rimborsabile | 03/01/2017 | 70
    Standard | no rimborsabile | 04/01/2017 | 80
    Standard | no rimborsabile | 05/01/2017 | 80

    Ottengo per una camera tutte le tariffe consecutive per uno stesso livello tariffario

    Grazie mille

    venerdì 5 maggio 2017 07:03

Risposte

  • Ciao.

    Considerando un dataset iniziale così formato:

    DECLARE @tbl TABLE (Camera VARCHAR(50), Livello VARCHAR(50), Data VARCHAR(50), Tariffa  VARCHAR(50))

    io farei:
    DECLARE @n INT = 5

    SELECT
     mainq.Camera,
     mainq.Livello,
     mainq.Data,
     mainq.Tariffa
    FROM (SELECT
     ROW_NUMBER() OVER (PARTITION BY Camera, Livello ORDER BY Camera, Livello) AS wn,
     Camera,
     Livello,
     Data,
     Tariffa
    FROM @tbl) AS mainq
    WHERE mainq.wn <= @n

    lunedì 8 maggio 2017 13:41