UGISS Community

Ciao a tutti,
ho una tabella fatta in questo modo:


Id,Giorno
1,01/01/2008
1,02/01/2008
1,03/01/2008
1,04/01/2008
1,05/01/2008
1,10/01/2008
1,11/01/2008
1,12/01/2008
1,13/01/2008
1,14/01/2008
2,15/01/2008
2,16/01/2008
2,17/01/2008
2,18/01/2008
2,25/01/2008
2,26/01/2008
2,27/01/2008
2,28/01/2008

Adesso dovrei fare una query che mi torni una cosa del genere:
Id,Dal,Al
1,01/01/2008,05/01/2008
1,10/01/2008,14/01/2008
2,15/01/2008,18/01/2008
2,25/01/2008,28/01/2008

Qualche idea su come fare?

Purtroppo non basta raggruppare per ID perchè uno stesso ID può avere diversi periodi.

Grazie mille.

Emanuele Ciao,

con una sola query T-SQL credo che non sia possibile ottenere l'output richiesto.

Prova la multi-statement table-valued functions udf_get_periodi che trovi di seguito, permette di ottenere l'output richiesto. Non è ottimizzata e fa uso di un cursore... se pensi di utilizzarla e prevedi che più utenti possano lanciarla contemporaneamente su molti dati, forse è meglio dare un'occhiata anche alle prestazioni .

-- Cancellazione funzione dbo.udf_get_periodi

if (object_id('udf_get_periodi') is not null)

  DROP FUNCTION dbo.udf_get_periodi

GO

-- Creazione funzione dbo.udf_get_periodi

CREATE FUNCTION dbo.udf_get_periodi()

returns @tabdate table(id int not null, dal datetime not null, al datetime)

as

begin

  declare @id int,

          @currentid int,

          @countperiodo int,

          @countid int,

          @i int,

          @j int,

          @data datetime

  declare curdate cursor for

    select t.id,

           t.data,

           (select (count(id) / 2) from testdate t1 where t.id=t1.id

            group by t1.id) as countper,

           (select (count(id)) from testdate t1 where t.id=t1.id

            group by t1.id) as countid

    from testdate t

  open curdate

  fetch next from curdate into @id, @data, @countperiodo, @countid

  set @i = 1

  set @currentid = @id

  while (@@fetch_status = 0)

  begin

    if (@currentid = @id) and (@i <= @countperiodo)

    begin

      begin

        if (@i = 1)

        begin

          insert into @tabdate (id, dal, al) values (@id, @data, null)

        end

        else if (@i = @countperiodo)

        begin

          update @tabdate set al= @data where (id= @id) and (al is null)

        end

      end

    end

    else begin

      set @i = 1

      set @currentid = @id

      if (@i = @countperiodo) and (@countperiodo = 1)

      begin

        insert into @tabdate (id, dal, al) values (@id, @data, null)

        set @j = 1

        while (@@fetch_status = 0) and (@j < @countid)

        begin

          fetch next from curdate into @id, @data, @countperiodo, @countid

          set @j = @j + 1

        end

        update @tabdate set al= @data where (id= @id) and (al is null)

      end

      else begin

        insert into @tabdate (id, dal, al) values (@id, @data, null)

      end

    end

    set @i = @i + 1

    fetch next from curdate into @id, @data, @countperiodo, @countid

  end

  close curdate

  deallocate curdate

  return

END

Con questo scenario:

IF (OBJECT_ID('TESTDATE') IS NOT NULL)

CREATE TABLE dbo.TESTDATE (ID INT NOT NULL, DATA DATETIME NOT NULL)

INSERT INTO testdate (id, data) VALUES (1, '01/01/2008')

INSERT INTO testdate (id, data) VALUES (1, '01/02/2008')

INSERT INTO testdate (id, data) VALUES (1, '01/03/2008')

INSERT INTO testdate (id, data) VALUES (1, '01/04/2008')

INSERT INTO testdate (id, data) VALUES (1, '01/05/2008')

INSERT INTO testdate (id, data) VALUES (1, '01/10/2008')

INSERT INTO testdate (id, data) VALUES (1, '01/11/2008')

INSERT INTO testdate (id, data) VALUES (1, '01/12/2008')

INSERT INTO testdate (id, data) VALUES (1, '01/13/2008')

INSERT INTO testdate (id, data) VALUES (1, '01/14/2008')

INSERT INTO testdate (id, data) VALUES (2, '01/15/2008')

INSERT INTO testdate (id, data) VALUES (2, '01/17/2008')

INSERT INTO testdate (id, data) VALUES (2, '01/18/2008')

INSERT INTO testdate (id, data) VALUES (4, '01/19/2008')

ho ottenuto questo output:

SELECT * FROM DBO.UDF_GET_PERIODI()

4           2008-01-19      NULL

Ciao!

Spaziale!!

Grazie mille sgovoni, me lo studio con calma e vedo di capirci qualche cosa :)

Ciao Emanuele

il tuo problema sarebbe facilmente risolvibile se i database avessero le estensioni temporali (come presentato da Gianluca in un paio di eventi UGISS. Puoi prelevare il materiale da qui:

http://community.ugiss.org/files/folders/workshop_20071003/default.aspx 

http://community.ugiss.org/files/folders/workshop_20070227/default.aspx

)

In particolare ti servirebbe l'operatore "PACK". Se è vero che tale operatore non esiste è anche vero che la teoria a supporto di questo c'è, quindi si tratta solo di fare a mano quello che l'operatore farebbe in automatico:

/*

    SETUP

*/
use tempdb
go

create function dbo.fn_Nums(@m as bigint) returns table
as
return
with
t0 as (select n = 1 union all select n = 1),
t1 as (select n = 1 from t0 as a, t0 as b),
t2 as (select n = 1 from t1 as a, t1 as b),
t3 as (select n = 1 from t2 as a, t2 as b),
t4 as (select n = 1 from t3 as a, t3 as b),
t5 as (select n = 1 from t4 as a, t4 as b),
result as (select row_number() over (order by n) as n from t5)
select n from result where n < @m
go

create table dbo.test
(
    id int,
    giorno datetime
)
go

set dateformat dmy
go

insert into dbo.test values (1,'01/01/2008')
insert into dbo.test values (1,'02/01/2008')
insert into dbo.test values (1,'03/01/2008')
insert into dbo.test values (1,'04/01/2008')
insert into dbo.test values (1,'05/01/2008')
insert into dbo.test values (1,'10/01/2008')
insert into dbo.test values (1,'11/01/2008')
insert into dbo.test values (1,'12/01/2008')
insert into dbo.test values (1,'13/01/2008')
insert into dbo.test values (1,'14/01/2008')
insert into dbo.test values (2,'15/01/2008')
insert into dbo.test values (2,'16/01/2008')
insert into dbo.test values (2,'17/01/2008')
insert into dbo.test values (2,'18/01/2008')
insert into dbo.test values (2,'25/01/2008')
insert into dbo.test values (2,'26/01/2008')
insert into dbo.test values (2,'27/01/2008')
insert into dbo.test values (2,'28/01/2008')
go

/*

    RISOLUZIONE

*/

with cte_calendar as
(
    select
        n,
        giorno = dateadd(dd, n-1, '20080101')
    from
        dbo.fn_Nums(100)
),
cte_test as
(
    select
        id,
        n = row_number() over (order by giorno),       
        giorno
    from
        dbo.test
)
select
    id = min(b.id),
    da = min(a.giorno),
    a = max(b.giorno)
from
    cte_calendar a
left outer join
    cte_test b on a.giorno = b.giorno
where
    b.n is not null
group by
    a.n - b.n, b.id

Come si vede è necessario avere un calendario di appoggio, e questo viene creato utilizzando la funzione fn_Nums. A parte questa funzione, il tutto si riescie a risolvere con un query ed un paio di CTE.

La soluzione di sgovoni è ottima ma....fa uso di cursori che se possibile è bene evitare, favorendo soluzioni "set-based" che sono più performanti, scalabili e semplici. Il problema è che sono un pò più difficili da trovare

Mostruoso :)

Grazie mille dmauri mi toccerà iniziare a studiare invece di fare finta!

Ciao dmauri,
grazie ancora per il tuo aiuto, sto iniziando ad usare la query qui sopra e ho trovato un piccolo intoppo, le mie tabelle sono fatte cosi:

tblCalendario
idGiorno (int)
Giorno(smalldatetime)

tblTariffe
IdTariffa(Int)
Giorno(smalldatetime)
Importo(decimal(10,2))

La tabella tblCalendario contiene tutti i giorni dell'anno, i dati nella tabella tblTariffe sono cosi:

1; 01/01/2009; 100
2; 02/01/2009; 110
3; 03/01/2009; 100
4; 05/01/2009; 110
5; 06/01/2009; 110

Il risultato che vorrei avere è:
dal; al; importo
01/01/2009; 01/01/2009;100
02/01/2009; 02/01/2009;110
03/01/2009; 03/01/2009;100
05/01/2009; 06/01/2009;110

Partendo dalla tua query ho ricavato questa:

with cte_calendario as (
    select    n = row_number() over (order by Giorno),
            giorno
    from dbo.tblCalendario
),
cte_prezzi as (
    select IdTariffa,
            n = row_number() over (order by Trattamento, Giorno),
            giorno,
            Importo
    from dbo.tblTariffe
)

select    min(a.giorno) as Dal,
        max(a.giorno) as Al,
        b.Importo,
        a.n - b.n as raggruppa
from    cte_calendario a left outer join
        cte_prezzi b on a.Giorno = b.giorno
where    b.Giorno is not null and
        a.giorno between '20090101' and '20090110'
group by a.n - b.n, b.importo
order by Dal, Al

E funziona perfettamente tranne che in alcuni casi mi raggruppa "troppo" infatti l'output sui dati sopra è:
Il risultato che vorrei avere è:
dal; al; importo
01/01/2009; 03/01/2009;100
02/01/2009; 02/01/2009;110
05/01/2009; 06/01/2009;110

Come vedi la prima riga è sbagliata perchè raggruppanto per l'importo mi "unisce" i 2 record.

Riesco a risolvere in qualche modo?

Avere una tabella di "appoggio" con tutti i giorni va bene o secondo te è meglio usare una UDF come la tua?

Grazie ancora

C'eri quasi

E' necessario partizionare i rownumber generati anche per importo, e quindi raggrupare sempre per importo:

/*

    SETUP

*/
use tempdb
go

create function dbo.fn_Nums(@m as bigint) returns table
as
return
with
t0 as (select n = 1 union all select n = 1),
t1 as (select n = 1 from t0 as a, t0 as b),
t2 as (select n = 1 from t1 as a, t1 as b),
t3 as (select n = 1 from t2 as a, t2 as b),
t4 as (select n = 1 from t3 as a, t3 as b),
t5 as (select n = 1 from t4 as a, t4 as b),
result as (select row_number() over (order by n) as n from t5)
select n from result where n < @m
go

create table dbo.test
(
    id int,
    giorno datetime,
    importo decimal
)
go

set dateformat dmy
go

insert into dbo.test values (1,'01/01/2009',100)
insert into dbo.test values (1,'02/01/2009',110)
insert into dbo.test values (1,'03/01/2009',100)
insert into dbo.test values (1,'05/01/2009',110)
insert into dbo.test values (1,'06/01/2009',110)
go

select * from dbo.test
go

/*

    RISOLUZIONE

*/

with cte_calendar as
(
    select
        n,
        giorno = dateadd(dd, n-1, '20090101')
    from
        dbo.fn_Nums(100)
),
cte_test as
(
    select
        id,
        n = row_number() over (partition by importo order by giorno, importo),       
        giorno,
        importo
    from
        dbo.test
)
select
    id = min(b.id),
    da = min(a.giorno),
    a = max(b.giorno),
    b.importo
from
    cte_calendar a
left outer join
    cte_test b on a.giorno = b.giorno
where
    b.n is not null
group by
    a.n - b.n, b.id, b.importo
order by
 id, da, a