1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| select @StartDate As DateLigne, TPerson.Name, TPerson.idperson,
(select sum(coalesce(hours,0) - coalesce(lunch,0)) FROM Thereport
WHERE etridperson=TPerson.idperson AND etridproject=TUserProject.etridproject AND DateDIFF(day, @StartDate, datereport) >= 0 AND DateDIFF(day, datereport, @endDate) >= 0 ) As hours,
(select sum(coalesce(nonbillable,0)) FROM Thereport
WHERE etridperson=TPerson.idperson AND etridproject=TUserProject.etridproject AND DateDIFF(day, @StartDate, datereport) >= 0 AND DateDIFF(day, datereport, @endDate) >= 0 ) As nonbillable,
(select sum((coalesce(miles,0)*3)+ coalesce(perdiem,0)+coalesce(supplies,0)+coalesce(airfare,0)+ coalesce( gas,0) + coalesce(autorental,0)+ coalesce(other,0) ) FROM Thereport
WHERE etridperson=TPerson.idperson AND etridproject=TUserProject.etridproject AND DateDIFF(day, @StartDate, datereport) >= 0 AND DateDIFF(day, datereport, @endDate) >= 0 ) As Expenses
FROM TUserProject, TPerson
WHERE TUserProject.etridperson=TPerson.idperson AND etridproject = @project AND [type]=1and active=isnull(@active,active)
order by Name |
Partager