Test

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow en español

Preguntas y respuestas para programadores y profesionales de la informática

DECLARE	@EmployeeHistory TABLE(
	ID INT PRIMARY KEY,
	EmployeeID INT NOT NULL,     
	[Status] VARCHAR(20) NOT NULL,
	EffectiveDate DATE NOT NULL
)
INSERT	@EmployeeHistory(ID,EmployeeID,[Status],EffectiveDate)
SELECT 1,110545,'Active','2011-08-01'
UNION ALL SELECT 2,110700,'Active','2012-01-05'
UNION ALL SELECT 3,110060,'Active','2012-01-05'
UNION ALL SELECT 4,110222,'Active','2012-05-30'
UNION ALL SELECT 5,110545,'Resigned','2012-07-01'
UNION ALL SELECT 6,110545,'Active','2013-02-12';

DECLARE	@StartDate DATE,
		@EndDate DATE;
SELECT	@StartDate='2012-08-01',
		@EndDate='2013-01-01';
		
SELECT	*
FROM(		
	SELECT	x.EmployeeID,
			SUM(CASE WHEN x.EffectiveDate<=@StartDate THEN x.StatusSign ELSE 0 END) AS StartDate_Status,
			SUM(CASE WHEN x.EffectiveDate BETWEEN @StartDate AND @EndDate AND x.StatusSign=+1 THEN 1 ELSE 0 END) AS IntermediateDate_Status,
			SUM(CASE WHEN x.EffectiveDate<=@EndDate THEN x.StatusSign ELSE 0 END) AS EndDate_Status
	FROM
	(		
	SELECT	h.EmployeeID,
			h.EffectiveDate,
			CASE 
				WHEN h.[Status]='Active' THEN +1 
				WHEN h.[Status]='Resigned' THEN -1
				ELSE 1/0 -- Internal error: unknown [Status]
			END StatusSign
	FROM	@EmployeeHistory h
	WHERE	h.EffectiveDate<=@EndDate
	)x
	GROUP BY x.EmployeeID
)y
WHERE	y.StartDate_Status=1 
OR		y.IntermediateDate_Status=1
OR		y.EndDate_Status=1;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results