sulla scorta di quanto gia' ti avevo indicato in http://forum.aspitalia.com/forum/post/415503/Raggruppare-Date-Consecutive.aspx, e' facile ragionare in maniera simile per trovare il compartizionamento in isole di dati non per "prezzo" ma per data NON consecutiva...
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.t (
Camera varchar(20),
Livello varchar(20),
Data date,
Tariffa decimal(18,4)
);
GO
INSERT INTO dbo.t
VALUES ('Standard', 'rimborsabile', '2017-01-01', 50),
('Standard', 'rimborsabile', '2017-01-02', 50),
('Standard', 'rimborsabile', '2017-01-03', 50),
('Standard', 'rimborsabile', '2017-01-04', 60),
('Standard', 'rimborsabile', '2017-01-05', 60),
('Standard', 'rimborsabile', '2017-01-06', 60),
('Standard', 'rimborsabile', '2017-01-08', 60),
('Standard', 'rimborsabile', '2017-01-09', 60),
('Standard', 'rimborsabile', '2017-01-10', 60),
('Standard', 'rimborsabile', '2017-01-11', 50),
('Standard', 'rimborsabile', '2017-01-12', 40),
('Standard', 'no rimborsabile', '2017-01-01', 70),
('Standard', 'no rimborsabile', '2017-01-02', 70),
('Standard', 'no rimborsabile', '2017-01-03', 70),
('Standard', 'no rimborsabile', '2017-01-04', 80),
('Standard', 'no rimborsabile', '2017-01-05', 80),
('Standard', 'no rimborsabile', '2017-01-06', 80),
('Standard', 'no rimborsabile', '2017-01-08', 90),
('Standard', 'no rimborsabile', '2017-01-09', 90),
('Standard', 'no rimborsabile', '2017-01-10', 90),
('Standard', 'no rimborsabile', '2017-01-12', 90),
('Standard', 'no rimborsabile', '2017-01-13', 90),
('Standard', 'no rimborsabile', '2017-01-14', 90);
GO
DECLARE @minDays int = 5;
DECLARE @dStart date = '2017-01-01';
DECLARE @dEnd date = '2017-01-20';
PRINT 'a titolo di esempio, ottengo le compartimentazioni di isole di dati senza gap compresi nell''intervallo';
SELECT i.Camera, i.Livello, i.Data, i.Tariffa
-- individuazione del discriminante di data con gap
, SUM([Discriminante]) OVER (ORDER BY i.[Camera], i.[Livello], i.[Data]) AS [Discriminante]
FROM
(
SELECT t.Camera, t.Livello, t.Data, t.Tariffa
-- discriminante di compartimentazione
, CASE WHEN DATEADD(DAY, 1, LAG(t.[Data], 1) OVER (ORDER BY t.[Camera], t.[Livello], t.[Data])) <> t.[Data] THEN 1 ELSE 0 END [Discriminante]
FROM dbo.t t
-- filtro di finestra temporale generica
WHERE t.[Data] BETWEEN @dStart AND @dEnd
) I
ORDER BY i.Camera, i.Livello, i.Data;
PRINT 'proiezione vera e propria filtrando il risultato precedente per i soli';
PRINT ' gruppi che abbiano almeno @minDays consecutivi di soluzione';
WITH cte AS (
-- impongo una data massima pari a Min(data) + [giorni richiesti] -1,
-- quindi se la sequenza e' di 6 o piu' righe, prendo solo le prime 5
SELECT o.Camera, o.Livello, MIN(o.Data) AS [Dal], DATEADD(DAY, @minDays-1, MIN(o.Data)) AS [Al]
FROM (
SELECT i.Camera, i.Livello, i.Data, i.Tariffa
-- individuazione del discriminante di data con gap
, SUM([Discriminante]) OVER (ORDER BY i.[Camera], i.[Livello], i.[Data]) AS [Discriminante]
FROM
(
SELECT t.Camera, t.Livello, t.Data, t.Tariffa
-- discriminante di compartimentazione
, CASE WHEN DATEADD(DAY, 1, LAG(t.[Data], 1) OVER (ORDER BY t.[Camera], t.[Livello], t.[Data])) <> t.[Data] THEN 1 ELSE 0 END [Discriminante]
FROM dbo.t t
-- filtro di finestra temporale generica
WHERE t.[Data] BETWEEN @dStart AND @dEnd
) I
) O
GROUP BY o.Camera, o.Livello, [Discriminante]
-- solo quelli che abbiano una sequenza di almeno @minDays giorni
HAVING COUNT([Discriminante]) >= @minDays
)
-- SELECT DISTINCT t.* --- distinct NON serve
SELECT t.*
FROM dbo.t t
JOIN cte c ON t.[Data] BETWEEN c.[Dal] AND c.[Al]
AND c.[Camera] = t.[Camera] AND c.[Livello] = t.[Livello]
ORDER BY t.[Camera], t.[Livello], t.[Data];
GO
DROP TABLE dbo.t;
--<---------
a titolo di esempio, ottengo le compartimentazioni di isole di dati senza gap compresi nell'intervallo
Camera Livello Data Tariffa Discriminante
---------- -------------------- ---------- ---------- -------------
Standard no rimborsabile 2017-01-01 70.0000 0
Standard no rimborsabile 2017-01-02 70.0000 0
Standard no rimborsabile 2017-01-03 70.0000 0
Standard no rimborsabile 2017-01-04 80.0000 0
Standard no rimborsabile 2017-01-05 80.0000 0
Standard no rimborsabile 2017-01-06 80.0000 0
Standard no rimborsabile 2017-01-08 90.0000 1
Standard no rimborsabile 2017-01-09 90.0000 1
Standard no rimborsabile 2017-01-10 90.0000 1
Standard no rimborsabile 2017-01-12 90.0000 2
Standard no rimborsabile 2017-01-13 90.0000 2
Standard no rimborsabile 2017-01-14 90.0000 2
Standard rimborsabile 2017-01-01 50.0000 3
Standard rimborsabile 2017-01-02 50.0000 3
Standard rimborsabile 2017-01-03 50.0000 3
Standard rimborsabile 2017-01-04 60.0000 3
Standard rimborsabile 2017-01-05 60.0000 3
Standard rimborsabile 2017-01-06 60.0000 3
Standard rimborsabile 2017-01-08 60.0000 4
Standard rimborsabile 2017-01-09 60.0000 4
Standard rimborsabile 2017-01-10 60.0000 4
Standard rimborsabile 2017-01-11 50.0000 4
Standard rimborsabile 2017-01-12 40.0000 4
proiezione vera e propria filtrando il risultato precedente per i soli
gruppi che abbiano almeno @minDays consecutivi di soluzione
Camera Livello Data Tariffa
----------- -------------------- ---------- ----------
Standard no rimborsabile 2017-01-01 70.0000
Standard no rimborsabile 2017-01-02 70.0000
Standard no rimborsabile 2017-01-03 70.0000
Standard no rimborsabile 2017-01-04 80.0000
Standard no rimborsabile 2017-01-05 80.0000
Standard rimborsabile 2017-01-01 50.0000
Standard rimborsabile 2017-01-02 50.0000
Standard rimborsabile 2017-01-03 50.0000
Standard rimborsabile 2017-01-04 60.0000
Standard rimborsabile 2017-01-05 60.0000
Standard rimborsabile 2017-01-08 60.0000
Standard rimborsabile 2017-01-09 60.0000
Standard rimborsabile 2017-01-10 60.0000
Standard rimborsabile 2017-01-11 50.0000
Standard rimborsabile 2017-01-12 40.0000
dove ho espresso una finestra temporale iniziale compresa tra @dStart e @dEnd, proponendo quindi una richiesta specifica di almeno @minDays consecutivi
se ho ben capito la tua richiesta, nel caso la finestra comprenda piu' di @minDays righe, ho richiesto comunque espressamente SOLO @minDays righe...
