I have a question here half hairy and I need a little help. I have a table (tb_resultado_indicator) containing the following fields:
CD_Centro (Company Code)
CD_indicador_performance (production line code)
CD_Perode (1 for day, 2 for month and 3 for year)
TP_Dado (real or meta or accumulated meta or accumulated)
DT_Resultado (result date)
vl_indicator (result in them, numerical value)
I need to present the production data for a productive process in a report (Crystal Reports) as follows:
| Day | mês | Plan |
| CD_indicator | Meta | Real | Var | IC% | Meta | Real | Var | IC% | Monthly |
where the query parameter are the company code and date. IC% is the consecutive index. For a given date,
are presented the goal of the day, the actual value produced, the variation (real-goal) and the IC, as well as (from day 1st of the month)
Accumulated until the day, the real production until the day and the IC. Lastly the plan for the month is presented.
The select used (which returns only one line), is the following:
SELECT A.CD_INDICADOR_PERFORMANCE, A.VL_INDICADOR AS META, B.VL_INDICADOR AS REAL,
(B.VL_INDICADOR - A.VL_INDICADOR) AS VARDIA,
ROUND(DECODE(NVL(A.VL_INDICADOR, 0), 0, 0, NVL(B.VL_INDICADOR, 0)/A.VL_INDICADOR * 100), 1) AS ICDIA,
C.VL_INDICADOR AS ACUMMETA, D.VL_INDICADOR AS ACUMREAL,
(D.VL_INDICADOR - C.VL_INDICADOR) AS VARACUM,
ROUND(DECODE(NVL(C.VL_INDICADOR, 0), 0, 0, NVL(D.VL_INDICADOR, 0)/C.VL_INDICADOR * 100), 1) AS ICACUM,
E.VL_INDICADOR AS PLANO
FROM TB_RESULTADO_INDICADOR A, TB_RESULTADO_INDICADOR B, TB_RESULTADO_INDICADOR C, TB_RESULTADO_INDICADOR D, TB_RESULTADO_INDICADOR E
WHERE A.CD_CENTRO = {?empresa_filtro}
AND A.CD_INDICADOR_PERFORMANCE = 552
AND A.CD_PERIODO = 1
AND A.TP_DADO = 'META'
AND A.DT_RESULTADO = {?data}
AND B.CD_CENTRO = {?empresa_filtro}
AND B.CD_INDICADOR_PERFORMANCE = 552
AND B.CD_PERIODO = 1
AND B.TP_DADO = 'REAL'
AND B.DT_RESULTADO = {?data}
AND C.CD_CENTRO = {?empresa_filtro}
AND C.CD_INDICADOR_PERFORMANCE = 552
AND C.CD_PERIODO = 2
AND C.TP_DADO = 'ACUM META'
AND C.DT_RESULTADO = {?data}
AND D.CD_CENTRO = {?empresa_filtro}
AND D.CD_INDICADOR_PERFORMANCE = 552
AND D.CD_PERIODO = 2
AND D.TP_DADO = 'ACUM REAL'
AND D.DT_RESULTADO = {?data}
AND E.CD_CENTRO = {?empresa_filtro}
AND E.CD_INDICADOR_PERFORMANCE = 552
AND E.CD_PERIODO = 2
AND E.TP_DADO = 'META'
AND E.DT_RESULTADO = LAST_DAY({?data})
appears blank, even existing the actual value produced. What to do so that the values ??in the table
are made available, even when any data is missing ??
I do not know if I was clear enough in the explanation of the problem, but from now on, I thank the attention. [/ Img] [url=http://upload8.postimage.org/339480/photo_hosting.html]