C# と VB.NET の質問掲示板

日付のデータ:CAL
DATE      PH LABEL
    :
20181001, 1  J1   (平日)
20181002, 1  J2
20181003, 1  J3
20181004, 1  J4
20181005, 1  J5
20181006, 1  J6
20181007, 2  null (休日)
20181008, 2  null
20181009, 1  J7
20181010, 1  J8
    :

予定のデータ:PLN
NAME DAY DATE
    :
A,   5,  20181009
B,   3,  20181005
C,   3,  20181004
    :

があります。
PLNデータのDATEを基準にDAY日分前日の日付とラベルをとりたいので
以下のようにしました。

SELECT	PLN.NAME, CAL.DATE, CAL.LABEL
FROM	PLN
LEFT	OUTER JOIN (
	SELECT	DATE, LABEL, ROW_NUMBER() OVER(ORDER BY DATE DESC) AS RNUM
	FROM	CAL
	WHERE	PH = 1
		AND DATE <= PLN.DATE　　←バインドできない
	) AS CAL
		ON CAL.RNUM = PLN.DAY

バインドさせる方法はないのでしょうか？
また、このような場合はどうするのがいいのでしょうか？

■No89073 (魔界の仮面弁士 さん) に返信

>>AND DATE <= PLN.DATE　　←バインドできない
> 左辺の DATE が、CAL.DATE と PLN.DATE の間で曖昧になりますが、
> それ以前の問題として、提示いただいたデータを見る限りでは、
> 　20181005　… CAL.DATE は 8 桁
> 　2018105 　… PLN.DATE は 7 桁
> という点にも問題がありそう。
すみません。表記間違いました。

> PLN.DATE の絞り込み方法を、PLN.DAY と同様の構文に置き換えるとか。
> LEFT OUTER JOIN (SELECT 〜 FROM [CAL] WHERE [CAL].[PH]=1) AS [W]
> ON [W].[RNUM] = [PLN].[DAY] AND [W].[DATE] <= [PLN].[DATE]

結果が変わってしまいます。

A　20181004　J4
B　NULL　　　NULL
C　NULL　　　NULL

欲しい結果は以下になります
A　20181002　J02
B　20181003　J03
C　20181002　J02

SQL文は以下のようにしています。

SELECT	*
INTO	#CAL
FROM	(
		SELECT 20181001 AS DATE, 1 AS PH, 'J1' AS LABEL
		UNION SELECT 20181002, 1,  'J2'
		UNION SELECT 20181003, 1,  'J3'
		UNION SELECT 20181004, 1,  'J4'
		UNION SELECT 20181005, 1,  'J5'
		UNION SELECT 20181006, 1,  'J6'
		UNION SELECT 20181007, 2,  null
		UNION SELECT 20181008, 2,  null
		UNION SELECT 20181009, 1,  'J7'
		UNION SELECT 20181010, 1,  'J8'
		) AS CAL

SELECT	*
INTO	#PLN
FROM	(
		SELECT 'A' AS NAME,   5 AS DAY,    20181009 AS DATE
		UNION SELECT 'B', 3, 20181005
		UNION SELECT 'C', 3, 20181004
		) AS PLN;

SELECT	#PLN.NAME, CAL.DATE, CAL.LABEL
FROM	#PLN
LEFT	OUTER JOIN (
		SELECT	DATE, LABEL, ROW_NUMBER() OVER(ORDER BY DATE DESC) AS RNUM
		FROM	#CAL
		WHERE	#CAL.PH = 1
--				AND DATE <= #PLN.DATE
		) AS CAL
			ON CAL.RNUM = #PLN.DAY
			AND CAL.DATE <= #PLN.DATE

DROP TABLE #CAL;
DROP TABLE #PLN;

■No89076 (夜叉丸 さん) に返信
> 予定のデータ:PLN
> A,   5,  20181009
> B,   3,  20181005
> C,   3,  20181004
>
> 欲しい結果は以下になります
> A　20181002　J02
> B　20181003　J03
> C　20181002　J02

えぇと…。PH=2 は除外して、
稼働日である PH=1 のみをカウントするのですよね。

結果が J2 ではなく J02 に変化している点には目をつぶるとしても、まだよくわかりません。

A (20181009) の 5 営業日前が、20181002 であるのは分かったのですが、
B (20181005) の 3 営業日前が、20181002 ではなく 20181003 なのは何故でしょうか?
C (20181004) の 3 営業日前が、20181001 ではなく 20181002 なのは何故でしょうか?

当日を DAY=0 ではなく、DAY=1 としてカウントする仕様だったと考えれば
B と C の結果が合致するのですが、その場合は A が 20181003 になるはず…。


> 欲しい結果は以下になります
> A　20181002　J02
> B　20181003　J03
> C　20181002　J02

LAG 関数だとどうでしょう。(手元に SQL Server 2012 以降が無いので未確認)


WITH [CTE] AS (
  SELECT 
    [PLN].[NAME]
  , [PLN].[DAY]
  , [PLN].[DATE] AS [PLN_DATE]
  , [CAL].[DATE] AS [CAL_DATE]
  , [CAL].[LABEL]
  , LAG([CAL].[DATE] , [PLN].[DAY]) OVER (PARTITION BY [PLN].[NAME] ORDER BY [CAL].[DATE]) AS [LAG_DATE]
  , LAG([CAL].[LABEL], [PLN].[DAY]) OVER (PARTITION BY [PLN].[NAME] ORDER BY [CAL].[DATE]) AS [LAG_LABEL]
FROM
  [CAL] LEFT JOIN [PLN] ON [CAL].[DATE] <= [PLN].[DATE]
WHERE
  [CAL].[PH] = 1 AND [PLN].[NAME] IS NOT NULL
)
SELECT [NAME], [LAG_DATE], [LAG_LABEL] FROM [CTE]
WHERE [PLN_DATE] = [CAL_DATE]
ORDER BY [NAME]


/*
『WITH CTE AS (SELECT〜FROM〜WHERE〜) SELECT〜FROM CTE』を
『SELECT〜FROM (SELECT〜FROM〜WHERE〜) CTE WHERE 〜』にしても動きそう
*/