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