matpj

Problem with SQL to return planning screen information

Discussion created by matpj on Dec 12, 2008
Hi all,I am using the following SQL to return the total budget for a project in hours.The project I am testing has 3 approved revisions, but it is doubling the budget figures.  It is showing 3 lines for each chargecode, with the actual current value against one line, and then two lesser values against the other two lines (these values are not from the previous revisions, so i'm not sure where it is getting them from)  SELECT  mp.id, fd.id, c.prexternalid, sum(fd.units), fp.revision FROM     NIKU.PAC_MNT_PROJECTS MP, NIKU.PAC_FRC_FORECAST_PROPERTIES FP, NIKU.PAC_FRC_FORECAST_DETAILS FD, NIKU.PRCHARGECODE C, NIKU.PAC_FRC_FORECAST_VALUES FV, (SELECT  project_id, max(revision) max_revision FROM niku.pac_frc_forecast_properties x WHERE x.status = 2 GROUP BY project_id) FPV WHERE    FP.PROJECT_ID=MP.ID AND      FP.ID=FD.FORECAST_ID AND      FD.DETAIL_ID=C.PRID AND      FD.ID=FV.FORECAST_DETAILS_ID AND      FP.USE_DETAIL_TYPE = 2 AND      FV.CURRENCY_TYPE = 'HOME' AND      MP.APPROVED = 1 AND      MP.STATUS 'C' AND C.prexternalid not LIKE 'LDEV523%' AND      FP.REVISION = FPV.MAX_REVISION AND      FP.PROJECT_ID = FPV.PROJECT_ID  and fp.project_id = 5020206    group by c.prexternalid, fp.revision,mp.id, fd.id    can anybody help me with this?I have checked other projects with budget revisions and they are being reported fine.  It seems to be a problem in the Forecast details table.Many thanks,Matt

Outcomes