SQLで作業一覧とカレンダーから、期間の営業日の1日当たりの時間を計算して、

前回もご回答ありがとうございます。
直積使えば全パターン出力できたのですね。勉強不足ですみません。

カンマで羅列できるのも知りませんでした。


上記のSQLで実施したところ、3行しか取得できなかったため、
WITHで日付範囲指定して作成したカレンダーに休日フラグを付加したものを使用して出力しました。
前回ご教授頂いた方法はとても便利です。


あと1日当たりの時間　(時間/営業日数)　を出力するために、
営業日数を求めたいのですが、うまくいかなくて悩んでいます。


営業日数 = DATEDIFF(d ,終了日 - 開始日) + 1 - 期間の休日フラグ1の数
で求まるとは思うのですが。


質問文の訂正と補足を追加させて頂きました。

上記でご教授頂いたSQLで確認できました。ありがとうございます。


営業日の計算をCROSS JOINで休日フラグ0をカウント、なるほどです。
直積という考え方は用途によっては重要ですね。


1つ問題点があり、一時カレンダーの開始日、終了日の範囲外のところで、
0のカウントができないため、営業日数が少なくなってしまうと思われます。

※対象方法を検討中、一時カレンダーを前後1年分拡張するか、
または、計算方法を　営業日数 = datediff(d, [開始日], [終了日]) + 1 - 休日フラグ数

/* -- CTE -- */
WITH 　

/* -- 一時カレンダー作成(再帰CTE) -- */
一時カレンダー(日付) AS (
SELECT CONVERT(DATETIME, '2017-01-01') 
UNION ALL
SELECT DATEADD(D, 1, 日付) 
FROM [一時カレンダー] 
WHERE [日付] < CONVERT(DATETIME, '2017-01-31')
), 

/* -- 一時カレンダーに休日フラグを付加 -- */
[休日フラグ付加一時カレンダー] AS (
SELECT A.[日付],  CASE WHEN B.[休日フラグ] IS NULL THEN 0 ELSE B.[休日フラグ] END AS '休日フラグ'
FROM [一時カレンダー] AS A
LEFT OUTER JOIN [TEST].[dbo].[カレンダー] AS B ON A.[日付] = B.[日付] 
), 

/* -- 作業一覧の営業日数取得 -- */
[作業ごと営業日数] AS (
SELECT B.[作業名], COUNT(A.日付) AS [営業日数]
FROM [休日フラグ付加一時カレンダー] AS A
CROSS JOIN [TEST].[dbo].[作業一覧] AS B
WHERE A.[日付] BETWEEN B.[開始日] AND B.[終了日]
AND A.[休日フラグ] = 0
GROUP BY B.[作業名]
) 

/* -- Statement using the CTE -- */

/* -- 全作業一覧 -- */

SELECT A.[日付] AS '作業日', 
B.[作業名], 
B.[作業者名], 
CASE WHEN C.[営業日数] > 0 THEN B.[作業時間] / C.[営業日数] ELSE 0 END AS '1日当たり作業時間',  
C.[営業日数],  
B.[作業時間] 
FROM [休日フラグ付加一時カレンダー] AS A
CROSS JOIN [TEST].[dbo].[作業一覧] AS B 
INNER JOIN [作業ごと営業日数] AS C ON B.作業名 = C.作業名
WHERE A.日付 BETWEEN B.[開始日] AND B.[終了日]
AND A.[休日フラグ] = 0