select m.employeeId, m.projectId, sum(100.0/t.projectsCount)/30
from projectEmployeeMap m
join (
select map.employeeId, [date], cast(count(*) as money) as projectsCount
from projectEmployeeMap map
join calendar           cal on map.start <= cal.[date] and map.end >= cal.[date]
group by map.employeeId, map.projectId, [date]
where t.[date] between >= '20090401' and t.[date] < '20090501'
) as t
on t.employeeId = m.employeeId
group by m.employeeId, m.projectId

DECLARE	@Sample TABLE
	(
		EmpID INT,
		ProjID INT,
		PrjStartDt DATE,
		PrjEndDt DATE,
		Duration AS (1 + DATEDIFF(DAY, PrjStartDt, PrjEndDt))
	)

SET DATEFORMAT DMY

INSERT	@Sample
	(
		EmpID,
		ProjID,
		PrjStartDt,
		PrjEndDt
	)
VALUES	(1, 10, '01/04/2009', '23/04/2009'),
	(1, 20, '04/04/2009', '05/06/2009'), 
	(1, 30, '15/05/2009', '30/09/2009' )

-- Solution starts here
;WITH cteSource
AS (
	SELECT		s.EmpID,
			s.ProjID,
			COUNT(*) OVER (PARTITION BY cdiDate) AS Items,
			1E + DATEDIFF(DAY, '20090401', '20090930') AS Total
	FROM		Core.dimDate
	LEFT JOIN	@Sample AS s ON s.PrjStartDt <= cdiDate
				AND s.PrjEndDt >= cdiDate
	WHERE		cdiDate BETWEEN '20090401' AND '20090930'
), cteAggregation
AS (
	SELECT		EmpID,
			ProjID,
			Items,
			100E * COUNT(*) / MIN(Items) / MIN(Total) AS Peso
	FROM		cteSource
	GROUP BY	EmpID,
			ProjID,
			Items
)
SELECT		EmpID,
		ProjID,
		SUM(Peso) AS [Weight]
FROM		cteAggregation
GROUP BY	EmpID,
		ProjID
ORDER BY	EmpID,
		ProjID

quote:

---

Originally posted by Peso

When is EmpID 1 ever working in 3 projects simultaneously?
ProjID 10 ends 23rd of April and Proj30 starts 15th of May...

Anyway, here is a fast solution

DECLARE	@Sample TABLE
	(
		EmpID INT,
		ProjID INT,
		PrjStartDt DATE,
		PrjEndDt DATE,
		Duration AS (1 + DATEDIFF(DAY, PrjStartDt, PrjEndDt))
	)

SET DATEFORMAT DMY

INSERT	@Sample
	(
		EmpID,
		ProjID,
		PrjStartDt,
		PrjEndDt
	)
VALUES	(1, 10, '01/04/2009', '23/04/2009'),
	(1, 20, '04/04/2009', '05/06/2009'), 
	(1, 30, '15/05/2009', '30/09/2009' )

-- Solution starts here
;WITH cteSource
AS (
	SELECT		s.EmpID,
			s.ProjID,
			COUNT(*) OVER (PARTITION BY cdiDate) AS Items,
			1E + DATEDIFF(DAY, '20090401', '20090930') AS Total
	FROM		Core.dimDate
	LEFT JOIN	@Sample AS s ON s.PrjStartDt <= cdiDate
				AND s.PrjEndDt >= cdiDate
	WHERE		cdiDate BETWEEN '20090401' AND '20090930'
), cteAggregation
AS (
	SELECT		EmpID,
			ProjID,
			Items,
			100E * COUNT(*) / MIN(Items) / MIN(Total) AS Peso
	FROM		cteSource
	GROUP BY	EmpID,
			ProjID,
			Items
)
SELECT		EmpID,
		ProjID,
		SUM(Peso) AS [Weight]
FROM		cteAggregation
GROUP BY	EmpID,
		ProjID
ORDER BY	EmpID,
		ProjID

---

N 56°04'39.26"
E 12°55'05.63"

---