none
Aiuto query su date RRS feed

  • Domanda

  • Salve, avrei una query da sottoporvi che non riesco a risolvere.
    Ho una tabella [A] contenente degli intervalli di tempo (da data a data per intenderci)
    ed una tabella [B] contenente delle date.
    Se una o pi date della tabella [B] cadono in uno o pi intervalli della tabella [A]
    allora spezzano questi ultimi in intervalli pi piccoli.
    Di seguito vi posto un esempio e il risultato che vorrei ottenere.
    Spero che qualcuno mi dia una dritta.

    USE [TempDB]
    
    GO
    
    
    
    CREATE TABLE A (Id int, FromDate datetime, ToDate datetime)
    
    INSERT [A] ([Id], [FromDate], [ToDate])
    
    SELECT 10, '2009-01-01', '2009-12-31' UNION ALL
    
    SELECT 20, '2009-06-01', '2009-06-18' UNION ALL
    
    SELECT 30, '2009-05-01', '2009-06-10'
    
    
    
    CREATE TABLE B (Date datetime)
    
    INSERT [B] ([Date])
    
    SELECT '2009-06-15' UNION ALL
    
    SELECT '2009-06-20'
    
    
    
    SELECT * FROM A
    
    SELECT * FROM B
    
    SELECT 10, CONVERT(datetime, '2009-01-01'), CONVERT(datetime, '2009-06-14') UNION ALL
    
    SELECT 10, CONVERT(datetime, '2009-06-15'), CONVERT(datetime, '2009-06-19') UNION ALL
    
    SELECT 10, CONVERT(datetime, '2009-06-20'), CONVERT(datetime, '2009-12-31') UNION ALL
    
    SELECT 20, CONVERT(datetime, '2009-06-01'), CONVERT(datetime, '2009-06-14') UNION ALL
    
    SELECT 20, CONVERT(datetime, '2009-06-15'), CONVERT(datetime, '2009-06-18') UNION ALL
    
    SELECT 30, CONVERT(datetime, '2009-05-01'), CONVERT(datetime, '2009-06-10')
    
    
    
    DROP TABLE A, B
    
    
    sabato 14 novembre 2009 19:40

Risposte

  • Ciao Palegra,

    qui la query per ottenere il risultato
    ----
    USE TEMPDB
    GO
    CREATE TABLE A (Id int, FromDate datetime, ToDate datetime)

    INSERT [A] ([Id], [FromDate], [ToDate])
    SELECT 10, '20090101', '20091231' UNION ALL
    SELECT 20, '20090601', '20090618' UNION ALL
    SELECT 30, '20090501', '20090610'

    CREATE TABLE B (Date datetime)

    INSERT[B] ([Date])
    SELECT '20090615' UNION ALL
    SELECT '20090620' ;

    WITH T AS
    (
    SELECT A.ID,
           ROW_NUMBER() OVER (PARTITION BY ID ORDER BY B.Date) AS IdRank,
           A.FromDate,B.Date AS MiddleDate,
           A.ToDate
    FROM   A LEFT JOIN B ON B.Date>A.FromDate AND B.Date<A.ToDate
    )
    SELECT Id,
           IdRank,
           FromDate = CASE T.IdRank WHEN 1 THEN FromDate
                      ELSE (SELECT MiddleDate
                            FROM T AS T1
                            WHERE T1.ID = T.ID AND T1.IdRank = T.IdRank-1
                           )
                      END,
           ToDate = IsNull(MiddleDate-1,ToDate)
    FROM T
    UNION ALL
    SELECT T.Id,T.IdRank+1,T.MiddleDate,T.ToDate
    FROM T INNER JOIN
            (SELECT Id,MAX(IdRank) AS IdRank
             FROM T
             GROUP BY ID) AS T1 ON T.Id=T1.Id AND T.IdRank=T1.IdRank
    WHERE T.MiddleDate IS NOT NULL
    ORDER BY Id,IdRank

    DROP TABLE A, B

     

     

    ----

    Risultato:
    Id   IdRank   FromDate      MiddleDate
    --- -------  ------------  -----------
    10 1           2009-01-01   2009-06-14
    10 2           2009-06-15   2009-06-19
    10 3           2009-06-20   2009-12-31
    20 1           2009-06-01   2009-06-14
    20 2           2009-06-15   2009-06-18
    30 1           2009-05-01   2009-06-10

    se le righe sono molte, al posto della cte puoi usare una tabella temporanea con un indice clustered composto dai campi Id e IdRank

    Ciao
    Giorgio Rancati
    • Contrassegnato come risposta fcavicchi lunedì 16 novembre 2009 18:47
    lunedì 16 novembre 2009 12:52
    Moderatore
  • Ciao Palegra,

    5000 righe sono troppe per quel tipo di soluzione, meglio utilizzare una tabella temporanea.
    Esempio:

    USE TEMPDB 
    GO
    CREATE TABLE A (Id int, FromDate datetime, ToDate datetime)
    
    INSERT [A] ([Id], [FromDate], [ToDate])
    SELECT 10, '20090101', '20091231' UNION ALL
    SELECT 20, '20090601', '20090618' UNION ALL
    SELECT 30, '20090501', '20090610'
    
    CREATE TABLE B (Date datetime)
    
    INSERT[B] ([Date])
    SELECT '20090615' UNION ALL
    SELECT '20090620' ;
    
    
    -- Tabella temporanea
    CREATE TABLE #T
    (ID Int Not Null,
     IdRank Int Not Null,
     FromDate Datetime,
     MiddleDate Datetime,
     ToDate Datetime,
     PRIMARY KEY CLUSTERED (ID,IdRank)
    )
    
    -- Popolo la tabella temporanea
    INSERT INTO #T
    SELECT A.ID,
           ROW_NUMBER() OVER (PARTITION BY ID ORDER BY B.Date) AS IdRank,
           A.FromDate,B.Date AS MiddleDate,
           A.ToDate
    FROM   A LEFT JOIN B ON B.Date>A.FromDate AND B.Date<A.ToDate 
    
    -- Select Finale
    SELECT Id,
           IdRank,
           FromDate = CASE T.IdRank WHEN 1 THEN FromDate
                      ELSE (SELECT MiddleDate 
                            FROM #T AS T1 
                            WHERE T1.ID = T.ID AND T1.IdRank = T.IdRank-1
                           ) 
                      END,
           ToDate = IsNull(MiddleDate-1,ToDate)
    FROM #T AS T
    UNION ALL 
    SELECT T.Id,T.IdRank+1,T.MiddleDate,T.ToDate
    FROM #T AS T INNER JOIN
            (SELECT Id,MAX(IdRank) AS IdRank
             FROM #T
             GROUP BY ID) AS T1 ON T.Id=T1.Id AND T.IdRank=T1.IdRank
    WHERE T.MiddleDate IS NOT NULL
    ORDER BY Id,IdRank
    
    DROP TABLE A, B,#T

    Ciao
    Giorgio Rancati
    • Contrassegnato come risposta fcavicchi martedì 17 novembre 2009 12:57
    martedì 17 novembre 2009 10:54
    Moderatore

Tutte le risposte

  • Ciao Palegra,

    qui la query per ottenere il risultato
    ----
    USE TEMPDB
    GO
    CREATE TABLE A (Id int, FromDate datetime, ToDate datetime)

    INSERT [A] ([Id], [FromDate], [ToDate])
    SELECT 10, '20090101', '20091231' UNION ALL
    SELECT 20, '20090601', '20090618' UNION ALL
    SELECT 30, '20090501', '20090610'

    CREATE TABLE B (Date datetime)

    INSERT[B] ([Date])
    SELECT '20090615' UNION ALL
    SELECT '20090620' ;

    WITH T AS
    (
    SELECT A.ID,
           ROW_NUMBER() OVER (PARTITION BY ID ORDER BY B.Date) AS IdRank,
           A.FromDate,B.Date AS MiddleDate,
           A.ToDate
    FROM   A LEFT JOIN B ON B.Date>A.FromDate AND B.Date<A.ToDate
    )
    SELECT Id,
           IdRank,
           FromDate = CASE T.IdRank WHEN 1 THEN FromDate
                      ELSE (SELECT MiddleDate
                            FROM T AS T1
                            WHERE T1.ID = T.ID AND T1.IdRank = T.IdRank-1
                           )
                      END,
           ToDate = IsNull(MiddleDate-1,ToDate)
    FROM T
    UNION ALL
    SELECT T.Id,T.IdRank+1,T.MiddleDate,T.ToDate
    FROM T INNER JOIN
            (SELECT Id,MAX(IdRank) AS IdRank
             FROM T
             GROUP BY ID) AS T1 ON T.Id=T1.Id AND T.IdRank=T1.IdRank
    WHERE T.MiddleDate IS NOT NULL
    ORDER BY Id,IdRank

    DROP TABLE A, B

     

     

    ----

    Risultato:
    Id   IdRank   FromDate      MiddleDate
    --- -------  ------------  -----------
    10 1           2009-01-01   2009-06-14
    10 2           2009-06-15   2009-06-19
    10 3           2009-06-20   2009-12-31
    20 1           2009-06-01   2009-06-14
    20 2           2009-06-15   2009-06-18
    30 1           2009-05-01   2009-06-10

    se le righe sono molte, al posto della cte puoi usare una tabella temporanea con un indice clustered composto dai campi Id e IdRank

    Ciao
    Giorgio Rancati
    • Contrassegnato come risposta fcavicchi lunedì 16 novembre 2009 18:47
    lunedì 16 novembre 2009 12:52
    Moderatore
  • Ciao Giorgio, grazie 1k per l'aiuto.
    Il numero di record sui quali potrei fare la query è variabile cmq non più di 5000.
    Pensi che sarebbe il caso di usare una tabella temporanea come da te ipotizzato?

    Ciao
    Palegra
    lunedì 16 novembre 2009 18:47
  • Ciao Palegra,

    5000 righe sono troppe per quel tipo di soluzione, meglio utilizzare una tabella temporanea.
    Esempio:

    USE TEMPDB 
    GO
    CREATE TABLE A (Id int, FromDate datetime, ToDate datetime)
    
    INSERT [A] ([Id], [FromDate], [ToDate])
    SELECT 10, '20090101', '20091231' UNION ALL
    SELECT 20, '20090601', '20090618' UNION ALL
    SELECT 30, '20090501', '20090610'
    
    CREATE TABLE B (Date datetime)
    
    INSERT[B] ([Date])
    SELECT '20090615' UNION ALL
    SELECT '20090620' ;
    
    
    -- Tabella temporanea
    CREATE TABLE #T
    (ID Int Not Null,
     IdRank Int Not Null,
     FromDate Datetime,
     MiddleDate Datetime,
     ToDate Datetime,
     PRIMARY KEY CLUSTERED (ID,IdRank)
    )
    
    -- Popolo la tabella temporanea
    INSERT INTO #T
    SELECT A.ID,
           ROW_NUMBER() OVER (PARTITION BY ID ORDER BY B.Date) AS IdRank,
           A.FromDate,B.Date AS MiddleDate,
           A.ToDate
    FROM   A LEFT JOIN B ON B.Date>A.FromDate AND B.Date<A.ToDate 
    
    -- Select Finale
    SELECT Id,
           IdRank,
           FromDate = CASE T.IdRank WHEN 1 THEN FromDate
                      ELSE (SELECT MiddleDate 
                            FROM #T AS T1 
                            WHERE T1.ID = T.ID AND T1.IdRank = T.IdRank-1
                           ) 
                      END,
           ToDate = IsNull(MiddleDate-1,ToDate)
    FROM #T AS T
    UNION ALL 
    SELECT T.Id,T.IdRank+1,T.MiddleDate,T.ToDate
    FROM #T AS T INNER JOIN
            (SELECT Id,MAX(IdRank) AS IdRank
             FROM #T
             GROUP BY ID) AS T1 ON T.Id=T1.Id AND T.IdRank=T1.IdRank
    WHERE T.MiddleDate IS NOT NULL
    ORDER BY Id,IdRank
    
    DROP TABLE A, B,#T

    Ciao
    Giorgio Rancati
    • Contrassegnato come risposta fcavicchi martedì 17 novembre 2009 12:57
    martedì 17 novembre 2009 10:54
    Moderatore
  • Ciao Giorgio,
    ho già provato la tua soluzione e funziona perfettamente.
    Grazie
    Palegra
    martedì 17 novembre 2009 13:00