Keri Taylor

SQL help needed for query, help joining rates and cost table

Discussion created by Keri Taylor on Apr 21, 2010
Hi folks,Needing some SQL query help. Guess my mind is way off today since i can't seem to get why i can't do this correctly. I am trying to get my rates and costs from the nbi table. Everytime i join the table, they whole query of actuals are doubled, so obviously i am doing it wrong. any help would be appreciated.   /* basic query */
declare @gstart datetime, @gend datetime
set @gstart = '04/01/2010'
set @gend = '04/17/2010'

select s.slice_date, s.slice, r.last_name, r.first_name, t.prname, p.name, p.code

FROM PRJ_BLB_SLICES s
INNER JOIN PRTIMEENTRY te ON s.prj_object_id = te.prid
INNER JOIN PRTIMESHEET ts ON te.prtimesheetid = ts.prid
LEFT JOIN PRASSIGNMENT a ON te.prassignmentid = a.prid
LEFT JOIN PRCHARGECODE cc ON te.prchargecodeid = cc.prid
LEFT JOIN PRTASK t ON a.prtaskid = t.prid LEFT JOIN PRCHARGECODE tcc ON t.prchargecodeid = tcc.prid
LEFT JOIN inv_investments p ON t.prprojectid = p.id
INNER JOIN SRM_RESOURCES r ON ts.prresourceid = r.id
--inner join PRTEAM tm ON tm.PRID = a.TEAM_ID AND p.ID = tm.PRPROJECTID AND tm.PRRESOURCEID= r.ID
--left join nbi_proj_res_rates_and_costs rc ON tm.prid = rc.team_id and t.prid = rc.task_id and s.slice_date between rc.from_date and rc.to_date
--LEFT JOIN niku.NBI_PROJ_RES_RATES_AND_COSTS rc ON (a.prresourceid=rc.RESOURCE_ID AND a.prtaskid=rc.TASK_ID AND s.SLICE_DATE BETWEEN rc.FROM_DATE AND rc.TO_DATE)


WHERE (1=1)
AND s.slice_request_id = 55555
AND (s.slice_date BETWEEN CONVERT(DATETIME,@gstart,101) AND CONVERT(DATETIME,@gend,101))
and (ts.prstatus = 4 or (ts.prstatus     So, once i uncomment the nbi table, all the hours (s.slice) double. The xlsx shows data for both before and after adding the nbi table.  Thanks,Keri

Attachments

Outcomes