Connect By on SqlServer

0

I have a query that works in Oracle but is not working in SQL Server.

The table I have saved has a start date and end date on the same line, but when it goes from one day to the next, I needed to look at how much time I had in a day

Dt_Ini     | Dt_fim
30/03/2017 | 31/03/2017 

I needed you to list

Dt_ini 30/03/2017  //e na outra linha
Dt ini 31/03/2017
SELECT DATABASEEXT, CODCRE, USU_TIPIND, CODMTV, USU_PARPRO, SUM(QTDHRS) AS QTDHRS 
FROM(   
    WITH tabela as (
    SELECT usu_dtinpa AS DT_INI,
           usu_dtfipa AS DT_FIM,
           usu_hrinpa AS HRBFIM,
        usu_tctrpar.*
        FROM usu_tctrpar
        WHERE usu_dtinpa  BETWEEN '01/03/2017' AND '31/03/2017'
        AND usu_seqapt = 181
        ),
    results as (
        SELECT DISTINCT
            usu_CODCRE, usu_CODMTV, 
             CASE WHEN (DT_INI + LEVEL - 1) <> (DT_INI) THEN
                       (DT_INI + LEVEL - 1)
               ELSE DT_INI
             END DATABASEEXT,

             CASE WHEN (DT_INI + LEVEL - 1) <> (DT_FIM) THEN
                       (DT_INI + LEVEL) 
               ELSE DT_FIM
             END DATA_FIM,      
            HORINI, HORFIM,
             CASE WHEN (DT_INI + LEVEL - 1) <> (DT_FIM) THEN HORINI

                  WHEN HORFIM <= 1407 AND CODCRE = '901' THEN HORINI 
                  WHEN HORFIM >  1407 AND CODCRE = '902' THEN 1440 

                  WHEN HORFIM <= 1320 AND CODCRE = '901' THEN HORINI  
                  WHEN HORFIM >  1320 AND CODCRE = '902' THEN 1440    

               ELSE 1320
             END HORINI2,

             CASE WHEN (DT_INI + LEVEL - 1) <> (DT_FIM) AND CODCRE ='902' THEN 1407
                  WHEN HORFIM > 1407 AND CODCRE ='901'  THEN HORFIM

                  WHEN (DT_INI + LEVEL - 1) <> (DT_FIM) AND CODCRE ='901' THEN 1320
                  WHEN HORFIM > 1320 AND CODCRE ='902'  THEN HORFIM

             ELSE HORFIM
            END HORFIM2
          FROM tabela
        CONNECT BY LEVEL <=  (DT_FIM - DT_INI) + 1
        )
    SELECT results.*, results.HORFIM2 -  results.HORINI2 AS QTDHRS, E018MTV.USU_TIPIND, E018MTV.USU_PARPRO
      FROM results, E018MTV
      WHERE DATABASEEXT BETWEEN '01/03/2017' AND '31/03/2017'
      AND results.codmtv = E018MTV.CODMTV
    ORDER BY  DATABASEEXT, HORINI
    )
GROUP BY DATABASEEXT, CODCRE, USU_TIPIND, CODMTV, USU_PARPRO
ORDER BY DATABASEEXT, CODCRE, CODMTV
    
asked by anonymous 25.04.2017 / 15:49

0 answers