Problem with PPM delivery reports date

[Alexandre Gavazzi]

Dear friends.
I have already asked a few questions on this subject.
But I couldn't fix it so I am asking again.
I have the following problem I need with PPM delivery reports.

What is happening;

At the time of generating the report application is not considering the last day of the requested period.

I have deliveries for period X I have to filter with date of the next month to get the information of the previous period.

For example we make an appointment from 10/1/2019 to 3/31/2020. The consultation returns with every month I have delivery, but in March I also have delivery the application does not bring me any information.

In other words, it is not considering the end date and the search reference month.

I have two templates below if anyone can show me where to change or if they really are correct.

INFO 1=======================================================================================================

SELECT
@SELECT:DIM:USER_DEF:IMPLIED:MES:right('00'+ isnull(X.MES,''),2):MES@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:X.MES_NOME:NOME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:X.data_inicio:data_inicio@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:X.data_final:data_final@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:case when SUM(X.Concluidas_no_prazo) < 0 Then
CAST(cast(SUM(X.Concluidas_no_prazo) AS numeric(38, 2))/sum(X.Concluidas_no_prazo+X.Concluidas_com_atraso+X.Pendentes_no_prazo+X.Pendentes_em_atraso)*100 AS numeric(38, 2))
else 0 end:Concluidas_no_prazo@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:case when SUM(X.Concluidas_com_atraso)< 0 Then
CAST(cast(SUM(X.Concluidas_com_atraso) AS numeric(38, 2))/sum(X.Concluidas_no_prazo+X.Concluidas_com_atraso+X.Pendentes_no_prazo+X.Pendentes_em_atraso)*100 AS numeric(38, 2))
else 0 end:Concluidas_com_atraso@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:case when SUM(X.Pendentes_no_prazo) < 0 Then
CAST(cast(SUM(X.Pendentes_no_prazo) AS numeric(38, 2))/sum(X.Concluidas_no_prazo+X.Concluidas_com_atraso+X.Pendentes_no_prazo+X.Pendentes_em_atraso)*100 AS numeric(38, 2))
else 0 end:Pendentes_no_prazo@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:case when SUM(X.Pendentes_em_atraso) < 0 Then
CAST(cast(SUM(X.Pendentes_em_atraso) AS numeric(38, 2))/sum(X.Concluidas_no_prazo+X.Concluidas_com_atraso+X.Pendentes_no_prazo+X.Pendentes_em_atraso)*100 AS numeric(38, 2))
else 0 end:Pendentes_em_atraso@
FROM
(
SELECT
[MES] = R.LOOKUP_CODE,
[MES_NOME] = R.NAME,
[data_inicio] = D.data_inicio,
[data_final] = D.data_final,
[Planejadas] = (isnull(SUM(A.todas_entregas),0) - isnull(SUM(A.canceladas_suspensas),0)),
[Concluidas_no_prazo] = isnull(SUM(A.concluida_no_prazo),0),
[Concluidas_com_atraso] = isnull(SUM(A.concluida_com_atraso),0),
[Pendentes_no_prazo] = isnull(SUM(A.pendentes_no_prazo),0),
[Pendentes_em_atraso] = isnull(SUM(A.pendentes_em_atraso),0)
FROM (

select
lookup_code,
name

from
cmn_lookups_v meses
where
meses.language_code = 'en'
AND meses.lookup_type = 'BNBTI_MESES_ANO') R
LEFT JOIN
(
SELECT
MES = MONTH(e.bnbti_dt_termino),
data_termino = e.bnbti_dt_termino,
canceladas_suspensas =
CASE
WHEN e.bnbti_estado IN ('CANCELADA', 'SUSPENSA') THEN COUNT(e.id)
END,
todas_entregas = COUNT(e.id),
concluida_no_prazo =
CASE
WHEN e.bnbti_estado = 'CONCLUIDA' AND
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_efe_entrega, 112)) <=
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_termino, 112)) THEN COUNT(e.id)
END,
concluida_com_atraso =
CASE
WHEN e.bnbti_estado = 'CONCLUIDA' AND
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_efe_entrega, 112)) >
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_termino, 112)) THEN COUNT(e.id)
END,
pendentes_no_prazo =
CASE
WHEN e.bnbti_estado NOT IN ('CONCLUIDA', 'CANCELADA', 'SUSPENSA') AND
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_termino, 112)) >=
CONVERT(datetime, CONVERT(varchar, GETDATE(), 112)) THEN COUNT(e.id)
END,
pendentes_em_atraso =
CASE
WHEN e.bnbti_estado NOT IN ('CONCLUIDA', 'CANCELADA', 'SUSPENSA') AND
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_termino, 112)) <
CONVERT(datetime, CONVERT(varchar, GETDATE(), 112)) THEN COUNT(e.id)
END
FROM NIKU.odf_ca_bnbti_entrega e
join inv_investments inv on e.odf_parent_id = inv.id
-- ALTERAÇÃO 26/02/2018 - Inclusão do Filtro de Portfólio --
left join pfm_investments pin on pin.investment_id = inv.id
left join pfm_portfolios prf on prf.id = pin.portfolio_id
-- ALTERAÇÃO 26/02/2018 - Inclusão do Filtro de Portfólio --

WHERE
bnbti_dt_inicio < = @WHERE:PARAM:USER_DEF:DATE:data_inicio@ and bnbti_dt_termino > = @WHERE:PARAM:USER_DEF:DATE:data_final@
and
inv.is_active = 0
-- ALTERAÇÃO 26/02/2018 - Inclusão do Filtro de Portfólio --
and (@WHERE:PARAM:USER_DEF:INTEGER:portfolio@ IS NULL or @WHERE:PARAM:USER_DEF:INTEGER:portfolio@ = prf.id)
-- ALTERAÇÃO 26/02/2018 - Inclusão do Filtro de Portfólio --

and
@FILTER@
GROUP BY bnbti_estado,
bnbti_dt_efe_entrega,
bnbti_dt_termino
) A
ON R.LOOKUP_CODE = A.MES


left join
(

select
CONVERT(VARCHAR(10),@WHERE:PARAM:USER_DEF:DATE:data_inicio@, 111) as data_inicio,
CONVERT(VARCHAR(10),@WHERE:PARAM:USER_DEF:DATE:data_final@ , 111) as data_final

from dual

) D on 1= -1

GROUP BY R.LOOKUP_CODE,
R.NAME,
D.data_inicio,
D.data_final

) X

GROUP BY X.MES,
X.MES_NOME,
X.data_inicio,
X.data_final
==========================================================================================================
INFO2
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:ENT:row_number() over(order by a.id desc):id_ord@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:ENT:a.code:id_entrega@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:ENT:a.code:code_entrega@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:ENT:a.nome:nome_entrega@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:ENT:a.termino:termino_entrega@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:ENT:a.code_tarefa:code_tarefa@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:ENT:a.nome_tarefa:nome_tarefa@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:ENT:a.termino_tarefa:termino_tarefa@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:ENT:a.bnbti_estado:bnbti_estado@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:ENT:a.obs:obs@
FROM (SELECT DISTINCT
ent.id id,
ent.code code,
ent.bnbti_nome_ent nome,
ent.bnbti_dt_inicio inicio,
ent.bnbti_dt_termino termino,
ent.bnbti_ent_cronograma cron_entrega,
ent.bnbti_id_tarefa id_tarefa_entrega,
ent.bnbti_cod_tarefa cod_tarefa_entrega,
tar.prExternalID code_tarefa,
tar.prname nome_tarefa,
tar.prstart inicio_tarefa,
tar.prfinish termino_tarefa,
ent.bnbti_estado,
CASE
WHEN ent.id IS NULL THEN 'Inclusão'
WHEN ent.id IS NOT NULL AND
ent.bnbti_nome_ent <> tar.prname THEN 'Alteração'
WHEN ent.id IS NOT NULL AND
ent.bnbti_dt_termino <> tar.prfinish THEN 'Alteração'
END obs
FROM odf_ca_bnbti_entrega ent
FULL OUTER JOIN PRTASK tar
ON tar.prid = ent.bnbti_id_tarefa
left join odf_ca_task ct
ON ct.id = tar.prid
WHERE tar.prprojectid = @where:param:xml:string:/data/id/@value@
and ct.bnbti_entrega =1
and @filter@
UNION
SELECT DISTINCT
ent.id id,
ent.code code,
ent.bnbti_nome_ent nome,
ent.bnbti_dt_inicio inicio,
ent.bnbti_dt_termino termino,
ent.bnbti_ent_cronograma cron_entrega,
ent.bnbti_id_tarefa id_tarefa_entrega,
ent.bnbti_cod_tarefa cod_tarefa_entrega,
tar.prExternalID code_tarefa,
tar.prname nome_tarefa,
tar.prstart inicio_tarefa,
tar.prfinish termino_tarefa,
ent.bnbti_estado,
'Exclusão' obs
FROM odf_ca_bnbti_entrega ent
left join PRTASK tar on tar.prid = ent.bnbti_id_tarefa
WHERE ent.odf_parent_id = @where:param:xml:string:/data/id/@value@
AND tar.pruid is null
) a
where
a.bnbti_estado not in ('CANCELADA') or a.bnbti_estado is null

===================================================================================================

Thanks
Alexandre

[Pablo Cuomo]

hi there, I think in the query it takes the hour, sometimes it does not assumes the end of the day. What happens if you change the period on the filter and add the day after the end of the project, 31-3-20, if you change it to 1-4-20 and check if the project appears?

Cheers!
Pablo

------------------------------
[Designation]
PPM Sr Consultant
SAM
[City]Argentina
------------------------------

[Alexandre Gavazzi]

Yes, if I change the dates projects appear, if I put one month ahead or one day more it appears.
However if I put the query to see the month 02 that I have deliveries with the date of the end of the month does not bring me anything, now if I put on 03/01 already brings me the information of the month 02.

Thanks 

Alexandre

[David Morton]

Looking at your code here;

WHERE
bnbti_dt_inicio < = @WHERE:PARAM:USER_DEF:DATE:data_inicio@ and bnbti_dt_termino > = @WHERE:PARAM:USER_DEF:DATE:data_final@


if bnbti_dt_inicio and bnbti_dt_termino are date and time fields and param_data_inicio and param_data_final are only date fields then you will have problems. For example '20-Nov-2019 15:07' is > '20-nov-2019' which I think is what you are describing?

to correct this, just need to cast the date/time fields to dates and this will work OK.

WHERE
CAST ( bnbti_dt_inicio AS DATE ) < = @WHERE:PARAM:USER_DEF:DATE:data_inicio@ and CAST ( bnbti_dt_termino AS DATE ) > = @WHERE:PARAM:USER_DEF:DATE:data_final@

[Alexandre Gavazzi]

@David Morton
Continues, not bringing the month information of the month consultation, and I have deliveries registered this month 02.


SELECT
@SELECT:DIM:USER_DEF:IMPLIED:MES:right('00'+ isnull(X.MES,''),2):MES@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:X.MES_NOME:NOME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:X.data_inicio:data_inicio@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:X.data_final:data_final@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:case when SUM(X.Concluidas_no_prazo) < 0 Then
CAST(cast(SUM(X.Concluidas_no_prazo) AS numeric(38, 2))/sum(X.Concluidas_no_prazo+X.Concluidas_com_atraso+X.Pendentes_no_prazo+X.Pendentes_em_atraso)*100 AS numeric(38, 2))
else 0 end:Concluidas_no_prazo@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:case when SUM(X.Concluidas_com_atraso)< 0 Then
CAST(cast(SUM(X.Concluidas_com_atraso) AS numeric(38, 2))/sum(X.Concluidas_no_prazo+X.Concluidas_com_atraso+X.Pendentes_no_prazo+X.Pendentes_em_atraso)*100 AS numeric(38, 2))
else 0 end:Concluidas_com_atraso@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:case when SUM(X.Pendentes_no_prazo) < 0 Then
CAST(cast(SUM(X.Pendentes_no_prazo) AS numeric(38, 2))/sum(X.Concluidas_no_prazo+X.Concluidas_com_atraso+X.Pendentes_no_prazo+X.Pendentes_em_atraso)*100 AS numeric(38, 2))
else 0 end:Pendentes_no_prazo@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:case when SUM(X.Pendentes_em_atraso) < 0 Then
CAST(cast(SUM(X.Pendentes_em_atraso) AS numeric(38, 2))/sum(X.Concluidas_no_prazo+X.Concluidas_com_atraso+X.Pendentes_no_prazo+X.Pendentes_em_atraso)*100 AS numeric(38, 2))
else 0 end:Pendentes_em_atraso@
FROM
(
SELECT
[MES] = R.LOOKUP_CODE,
[MES_NOME] = R.NAME,
[data_inicio] = D.data_inicio,
[data_final] = D.data_final,
[Planejadas] = (isnull(SUM(A.todas_entregas),0) - isnull(SUM(A.canceladas_suspensas),0)),
[Concluidas_no_prazo] = isnull(SUM(A.concluida_no_prazo),0),
[Concluidas_com_atraso] = isnull(SUM(A.concluida_com_atraso),0),
[Pendentes_no_prazo] = isnull(SUM(A.pendentes_no_prazo),0),
[Pendentes_em_atraso] = isnull(SUM(A.pendentes_em_atraso),0)
FROM (

select
lookup_code,
name

from
cmn_lookups_v meses
where
meses.language_code = 'en'
AND meses.lookup_type = 'BNBTI_MESES_ANO') R
LEFT JOIN
(
SELECT
MES = MONTH(e.bnbti_dt_termino),
data_termino = e.bnbti_dt_termino,
canceladas_suspensas =
CASE
WHEN e.bnbti_estado IN ('CANCELADA', 'SUSPENSA') THEN COUNT(e.id)
END,
todas_entregas = COUNT(e.id),
concluida_no_prazo =
CASE
WHEN e.bnbti_estado = 'CONCLUIDA' AND
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_efe_entrega, 112)) <=
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_termino, 112)) THEN COUNT(e.id)
END,
concluida_com_atraso =
CASE
WHEN e.bnbti_estado = 'CONCLUIDA' AND
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_efe_entrega, 112)) >
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_termino, 112)) THEN COUNT(e.id)
END,
pendentes_no_prazo =
CASE
WHEN e.bnbti_estado NOT IN ('CONCLUIDA', 'CANCELADA', 'SUSPENSA') AND
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_termino, 112)) >=
CONVERT(datetime, CONVERT(varchar, GETDATE(), 112)) THEN COUNT(e.id)
END,
pendentes_em_atraso =
CASE
WHEN e.bnbti_estado NOT IN ('CONCLUIDA', 'CANCELADA', 'SUSPENSA') AND
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_termino, 112)) <
CONVERT(datetime, CONVERT(varchar, GETDATE(), 112)) THEN COUNT(e.id)
END
FROM NIKU.odf_ca_bnbti_entrega e
join inv_investments inv on e.odf_parent_id = inv.id
-- ALTERAÇÃO 26/02/2018 - Inclusão do Filtro de Portfólio --
left join pfm_investments pin on pin.investment_id = inv.id
left join pfm_portfolios prf on prf.id = pin.portfolio_id
-- ALTERAÇÃO 26/02/2018 - Inclusão do Filtro de Portfólio --

WHERE
CAST ( bnbti_dt_termino AS DATE )> = @WHERE:PARAM:USER_DEF:DATE:data_inicio@ and CAST ( bnbti_dt_termino AS DATE ) < = @WHERE:PARAM:USER_DEF:DATE:data_final@
and inv.is_active = 1

-- ALTERAÇÃO 26/02/2018 - Inclusão do Filtro de Portfólio --
and (@WHERE:PARAM:USER_DEF:INTEGER:portfolio@ IS NULL or @WHERE:PARAM:USER_DEF:INTEGER:portfolio@ = prf.id)
-- ALTERAÇÃO 26/02/2018 - Inclusão do Filtro de Portfólio --

and
@FILTER@
GROUP BY bnbti_estado,
bnbti_dt_efe_entrega,
bnbti_dt_termino
) A
ON R.LOOKUP_CODE = A.MES


left join
(

select
CONVERT(VARCHAR(10),@WHERE:PARAM:USER_DEF:DATE:data_inicio@, 111) as data_inicio,
CONVERT(VARCHAR(10),@WHERE:PARAM:USER_DEF:DATE:data_final@ , 111) as data_final

from dual

) D on 1=1

GROUP BY R.LOOKUP_CODE,
R.NAME,
D.data_inicio,
D.data_final

) X

GROUP BY X.MES,
X.MES_NOME,
X.data_inicio,
X.data_final

[David Morton]

You have introduced an error in your logic I think.

You now have

WHERE
CAST ( bnbti_dt_termino AS DATE )> = @WHERE:PARAM:USER_DEF:DATE:data_inicio@ and CAST ( bnbti_dt_termino AS DATE ) < = @WHERE:PARAM:USER_DEF:DATE:data_final@

I think that you should have;

WHERE
CAST ( bnbti_dt_inicio AS DATE )> = @WHERE:PARAM:USER_DEF:DATE:data_inicio@ and CAST ( bnbti_dt_termino AS DATE ) < = @WHERE:PARAM:USER_DEF:DATE:data_final@

[Alexandre Gavazzi]

SELECT
@SELECT:DIM:USER_DEF:IMPLIED:MES:right('00'+ isnull(X.MES,''),2):MES@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:X.MES_NOME:NOME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:X.data_inicio:data_inicio@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:X.data_final:data_final@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:case when SUM(X.Concluidas_no_prazo) < 0 Then
CAST(cast(SUM(X.Concluidas_no_prazo) AS numeric(38, 2))/sum(X.Concluidas_no_prazo+X.Concluidas_com_atraso+X.Pendentes_no_prazo+X.Pendentes_em_atraso)*100 AS numeric(38, 2))
else 0 end:Concluidas_no_prazo@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:case when SUM(X.Concluidas_com_atraso)< 0 Then
CAST(cast(SUM(X.Concluidas_com_atraso) AS numeric(38, 2))/sum(X.Concluidas_no_prazo+X.Concluidas_com_atraso+X.Pendentes_no_prazo+X.Pendentes_em_atraso)*100 AS numeric(38, 2))
else 0 end:Concluidas_com_atraso@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:case when SUM(X.Pendentes_no_prazo) < 0 Then
CAST(cast(SUM(X.Pendentes_no_prazo) AS numeric(38, 2))/sum(X.Concluidas_no_prazo+X.Concluidas_com_atraso+X.Pendentes_no_prazo+X.Pendentes_em_atraso)*100 AS numeric(38, 2))
else 0 end:Pendentes_no_prazo@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MES:case when SUM(X.Pendentes_em_atraso) < 0 Then
CAST(cast(SUM(X.Pendentes_em_atraso) AS numeric(38, 2))/sum(X.Concluidas_no_prazo+X.Concluidas_com_atraso+X.Pendentes_no_prazo+X.Pendentes_em_atraso)*100 AS numeric(38, 2))
else 0 end:Pendentes_em_atraso@
FROM
(
SELECT
[MES] = R.LOOKUP_CODE,
[MES_NOME] = R.NAME,
[data_inicio] = D.data_inicio,
[data_final] = D.data_final,
[Planejadas] = (isnull(SUM(A.todas_entregas),0) - isnull(SUM(A.canceladas_suspensas),0)),
[Concluidas_no_prazo] = isnull(SUM(A.concluida_no_prazo),0),
[Concluidas_com_atraso] = isnull(SUM(A.concluida_com_atraso),0),
[Pendentes_no_prazo] = isnull(SUM(A.pendentes_no_prazo),0),
[Pendentes_em_atraso] = isnull(SUM(A.pendentes_em_atraso),0)
FROM (

select
lookup_code,
name

from
cmn_lookups_v meses
where
meses.language_code = 'en'
AND meses.lookup_type = 'BNBTI_MESES_ANO') R
LEFT JOIN
(
SELECT
MES = MONTH(e.bnbti_dt_termino),
data_termino = e.bnbti_dt_termino,
canceladas_suspensas =
CASE
WHEN e.bnbti_estado IN ('CANCELADA', 'SUSPENSA') THEN COUNT(e.id)
END,
todas_entregas = COUNT(e.id),
concluida_no_prazo =
CASE
WHEN e.bnbti_estado = 'CONCLUIDA' AND
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_efe_entrega, 112)) <=
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_termino, 112)) THEN COUNT(e.id)
END,
concluida_com_atraso =
CASE
WHEN e.bnbti_estado = 'CONCLUIDA' AND
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_efe_entrega, 112)) >
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_termino, 112)) THEN COUNT(e.id)
END,
pendentes_no_prazo =
CASE
WHEN e.bnbti_estado NOT IN ('CONCLUIDA', 'CANCELADA', 'SUSPENSA') AND
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_termino, 112)) >=
CONVERT(datetime, CONVERT(varchar, GETDATE(), 112)) THEN COUNT(e.id)
END,
pendentes_em_atraso =
CASE
WHEN e.bnbti_estado NOT IN ('CONCLUIDA', 'CANCELADA', 'SUSPENSA') AND
CONVERT(datetime, CONVERT(varchar, e.bnbti_dt_termino, 112)) <
CONVERT(datetime, CONVERT(varchar, GETDATE(), 112)) THEN COUNT(e.id)
END
FROM NIKU.odf_ca_bnbti_entrega e
join inv_investments inv on e.odf_parent_id = inv.id
-- ALTERAÇÃO 26/02/2018 - Inclusão do Filtro de Portfólio --
left join pfm_investments pin on pin.investment_id = inv.id
left join pfm_portfolios prf on prf.id = pin.portfolio_id
-- ALTERAÇÃO 26/02/2018 - Inclusão do Filtro de Portfólio --

WHERE
CAST ( bnbti_dt_termino AS DATE) > = @WHERE:PARAM:USER_DEF:DATE:data_termino@ and CAST ( bnbti_dt_termino AS DATE ) < = @WHERE:PARAM:USER_DEF:DATE:data_final@
and inv.is_active =1

-- ALTERAÇÃO 26/02/2018 - Inclusão do Filtro de Portfólio --
and (@WHERE:PARAM:USER_DEF:INTEGER:portfolio@ IS NULL or @WHERE:PARAM:USER_DEF:INTEGER:portfolio@ = prf.id)
-- ALTERAÇÃO 26/02/2018 - Inclusão do Filtro de Portfólio --

and
@FILTER@
GROUP BY bnbti_estado,
bnbti_dt_efe_entrega,
bnbti_dt_termino
) A
ON R.LOOKUP_CODE = A.MES


left join
(

select
CONVERT(VARCHAR(10),@WHERE:PARAM:USER_DEF:DATE:data_inicio@, 111) as data_inicio,
CONVERT(VARCHAR(10),@WHERE:PARAM:USER_DEF:DATE:data_final@ , 111) as data_final

from dual

) D on 1=1

GROUP BY R.LOOKUP_CODE,
R.NAME,
D.data_inicio,
D.data_final

) X

GROUP BY X.MES,
X.MES_NOME,
X.data_inicio,
X.data_final

==================================================================


still not taking deliveries and tasks of the month I'm doing the consultation

[David Morton]

You STILL have a code error, you have replaced the code with some other wrong code.

You now have;

WHERE
CAST ( bnbti_dt_termino AS DATE) > = @WHERE:PARAM:USER_DEF:DATE:data_termino@ and CAST ( bnbti_dt_termino AS DATE ) < = @WHERE:PARAM:USER_DEF:DATE:data_final@

it should be (as I said above);

WHERE
CAST ( bnbti_dt_inicio AS DATE )> = @WHERE:PARAM:USER_DEF:DATE:data_inicio@ and CAST ( bnbti_dt_termino AS DATE ) < = @WHERE:PARAM:USER_DEF:DATE:data_final@

Please think about what you are doing rather than just responding "it doesn't work" - this error (and the previous one) should really have been very easy for you to spot.