![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
00148474 ビーワン東日本 大谷 敦洋様
WITH CTE(代理店ID, 代理店名1, 親代理店ID, 階層ID,LEVEL,SORT)
AS (
SELECT
代理店ID
,代理店名1
,親代理店ID
,階層ID
,1 AS LEVEL
,RTRIM(SPACE(258)) + 親代理店ID AS SORT
FROM
M代理店子
WHERE
代理店ID = @代理店ID
AND M代理店子.有効開始日 <= @有効開始日
AND M代理店子.有効終了日 >= @有効開始日
UNION ALL
SELECT
M代理店子.代理店ID
,M代理店子.代理店名1
,M代理店子.親代理店ID
,M代理店子.階層ID
,CTE.LEVEL + 1
,CONVERT(varchar(268),RTRIM(CTE.SORT) +M代理店子.階層ID + M代理店子.代理店ID)
FROM
M理店子 WITH(INDEX(IX_親代理店))
INNER JOIN CTE ON M代理店子.親代理店ID= CTE.代理店ID
WHERE
(M代理店子.有効開始日 <= @有効開始日
AND M代理店子.有効終了日 >= @有効開始日)
OR M代理店子.有効開始日 >= @有効開始日 --将来子になる代理店も含む
)
SELECT * FROM CTE
WHERE 代理店ID = @親代理店ID WITH CTE(代理店ID, 代理店名1, 親代理店ID, 階層ID, LEVEL,SORT, 有効開始日, 有効終了日) /* 期間で絞り込むために有効開始日と有効終了日を追加 */
AS(
SELECT
代理店ID
,代理店名1
,親代理店ID
,階層ID
,1 AS LEVEL
,RTRIM(SPACE(258)) + 親代理店ID AS SORT
,有効開始日
,有効終了日
FROM
M代理店子
WHERE
代理店ID = @親代理店ID /* 親代理店に変更 */
AND (
(M代理店子.有効開始日 <= @有効開始日
AND M代理店子.有効終了日 >= @有効開始日)
OR M代理店子.有効開始日 >= @有効開始日) --未来も対象に含む
UNION ALL
SELECT
M代理店子.代理店ID
,M代理店子.代理店名1
,M代理店子.親代理店ID
,M代理店子.階層ID
,CTE.LEVEL + 1
,CONVERT(varchar(268),RTRIM(CTE.SORT) +M代理店子.階層ID + M代理店子.代理店ID)
,CASE WHEN M代理店子.有効開始日 > CTE.有効開始日 THEN M代理店子.有効開始日 ELSE CTE.有効開始日 END AS 有効開始日
,CASE WHEN M代理店子.有効終了日 < CTE.有効終了日 THEN M代理店子.有効終了日 ELSE CTE.有効終了日 END AS 有効終了日
FROM
M代理店子 WITH(INDEX(IX_親代理店))
INNER JOIN CTE ON M代理店子.代理店ID = CTE.親代理店ID /* 親を辿るように変更 */
WHERE
M代理店子.代理店ID <> @無効代理店ID
AND M代理店子.代理店ID <> @本社代理店ID
AND NOT (
M代理店子.有効終了日 < CTE.有効開始日
OR M代理店子.有効開始日 > CTE.有効終了日) /* 期間内に有効であるための条件 */
)
SELECT * FROM CTE
WHERE 代理店ID = @代理店ID /* 対象代理店に変更 */
