DECLARE @幚巤婜娫fr datetime
DECLARE @幚巤婜娫to datetime
DECLARE @媥柊懳徾擔 datetime
SET @幚巤婜娫fr = '2002/1/11'
SET @幚巤婜娫to = '2003/7/11'
SET @媥柊懳徾擔 = '2001/7/15'
/*
--*/



---- Fill
--SELECT DISTINCT
--	  M03屭媞.屭媞No
--	, M03屭媞.屭媞ID
--	, M03屭媞.巵柤
--	, M03屭媞.巵柤僇僫
--	, M01屌掕懏惈.抣 AS 惈暿
--	, M03屭媞.惗擭寧擔
--	, M03屭媞.梄曋斣崋
--	, M03屭媞.廧強
--	, M03屭媞.TEL
--	, M03屭媞.儊乕儖傾僪儗僗
--FROM
--	M01屌掕懏惈 INNER JOIN
--	M03屭媞 ON M01屌掕懏惈.屌掕懏惈No = M03屭媞.惈暿 RIGHT OUTER JOIN
--	H01斕攧棜楌 ON M03屭媞.屭媞No = H01斕攧棜楌.屭媞No
--WHERE
--	H01斕攧棜楌.慻怐No = @慻怐No AND
--	@幚巤婜娫fr < H01斕攧棜楌.斕攧擔帪 AND
--	H01斕攧棜楌.斕攧擔帪 < @幚巤婜娫to AND
--	M03屭媞.屭媞No IN
--	(
--		SELECT
--			屭媞No
--		FROM
--			H01斕攧棜楌
--		WHERE
--			H01斕攧棜楌.慻怐No = @慻怐No AND
--			H01斕攧棜楌.斕攧擔帪 < @媥柊懳徾擔 AND
--			H01斕攧棜楌.屭媞No NOT IN
--			(
--				SELECT
--					屭媞No
--				FROM
--					H01斕攧棜楌
--				WHERE
--					慻怐No = @慻怐No AND
--					@媥柊懳徾擔 < H01斕攧棜楌.斕攧擔帪 AND
--					H01斕攧棜楌.斕攧擔帪 < @幚巤婜娫fr 
--			)
--	)
SELECT DISTINCT 
	M03屭媞.屭媞No,
	M03屭媞.屭媞ID,
	M03屭媞.巵柤,
	M03屭媞.巵柤僇僫, 
	M04懏惈.抣 AS 惈暿,
	M03屭媞.惗擭寧擔,
	M03屭媞.梄曋斣崋,
	M03屭媞.廧強, 
	M03屭媞.TEL,
	M03屭媞.儊乕儖傾僪儗僗
FROM
	M04懏惈 INNER JOIN
	M03屭媞 ON M04懏惈.懏惈No = M03屭媞.惈暿 RIGHT OUTER JOIN
	H01斕攧棜楌 ON M03屭媞.屭媞No = H01斕攧棜楌.屭媞No
WHERE
	@幚巤婜娫fr < H01斕攧棜楌.斕攧擔帪 AND 
	H01斕攧棜楌.斕攧擔帪 < @幚巤婜娫to AND 
	M03屭媞.屭媞No IN (
		SELECT
			屭媞No
		FROM
			H01斕攧棜楌 AS H01斕攧棜楌_2
		WHERE
			斕攧擔帪 < @媥柊懳徾擔 AND
			屭媞No NOT IN (
				SELECT
					屭媞No
				FROM
					H01斕攧棜楌 AS H01斕攧棜楌_1
				WHERE
					@媥柊懳徾擔 < 斕攧擔帪 AND 
					斕攧擔帪 < @幚巤婜娫fr
			)
	)
