人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

SQLで作業一覧とカレンダーから、期間の営業日の1日当たりの時間を計算して、
結果を表示したいと考えています。DBはSQL Server 2012です。

営業日の1日当たりの時間計算は現在VBAで実装しているのですが、

様々な集計に対応するために、SQLに置き換えて
下記のような結果を出力したいと考えています。

下記のように処理するにはどのような方法を取ればよいでしょうか。

わかるかたいらっしゃいましたら、ご教授をお願いします。

■作業一覧
作業名, 作業者名, 開始日、終了日、時間
作業1, 作業者1, 2017/1/6, 2017/1/9, 8
作業2, 作業者1, 2017/1/9, 2017/1/10, 4
...

■カレンダー(休日はフラグ1)
日付, 休日フラグ
2017/1/6, 0
2017/1/7, 1
2017/1/8, 1
...

↓ 結果(全ての作業・作業日の結果を出力) ※訂正
作業日, 1日当たりの作業時間, 営業日日数, 作業名, 作業者名,
1/6, 4, 2, 作業1, 作業者1
1/9, 4, 2, 作業1, 作業者1
1/9, 2, 2, 作業2, 作業者1
1/10, 2, 2, 作業2, 作業者1
...

●質問者: pochi07
●カテゴリ:コンピュータ
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

1 ● degucho

これでどうでしょうか

SELECT A.日付 作業日
 , B.時間
 , B.作業名
 , B.作業者名
 FROM カレンダー A
 , 作業一覧 B
 WHERE A.日付 BETWEEN B.開始日 AND B.終了日
 AND A.休日フラグ = '0'
ORDER BY 1,3,4

以前の回答でも使用しましたが、
全パターンを羅列する場合は
直積(デカルト積)を使うのがいいと思います
(FROM句でJOINを使わずにテーブル名だけカンマで羅列またはCROSS JOIN)


pochi07さんのコメント
前回もご回答ありがとうございます。 直積使えば全パターン出力できたのですね。勉強不足ですみません。 カンマで羅列できるのも知りませんでした。 上記のSQLで実施したところ、3行しか取得できなかったため、 WITHで日付範囲指定して作成したカレンダーに休日フラグを付加したものを使用して出力しました。 前回ご教授頂いた方法はとても便利です。 あと1日当たりの時間 (時間/営業日数) を出力するために、 営業日数を求めたいのですが、うまくいかなくて悩んでいます。 営業日数 = DATEDIFF(d ,終了日 - 開始日) + 1 - 期間の休日フラグ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 [作業名], [作業者名], DATEDIFF(d, A.[開始日], B.[終了日]) + 1 - [休日フラグ数] AS '営業日数' 
FROM [TEST].[dbo].[作業一覧] AS A ... 

 */
) 

/* -- Statement using the CTE -- */
SELECT A.[日付] AS '作業日', B.[作業名], B.[作業者名], 
... AS '1日当たり時間', ... AS '営業日数', 

FROM [休日フラグ付加一時カレンダー] AS A
CROSS JOIN [TEST].[dbo].[作業一覧] AS B
WHERE A.[日付] BETWEEN B.[開始日] AND B.[終了日]
AND A.[休日フラグ] = 0
ORDER BY 1, 2, 3


2 ● degucho

直積で休日フラグ=0で取得した結果が営業日そのものなので
下記の感じでいけると思います

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.時間 / C.[営業日数] AS [1日当たりの作業時間] 
 , C.[営業日数]
 , B.[作業名]
 , 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
ORDER BY 1,4,5

カレンダー自体を営業日のみにすればもうちょっとシンプルになるかも


pochi07さんのコメント
上記でご教授頂いたSQLで確認できました。ありがとうございます。 営業日の計算をCROSS JOINで休日フラグ0をカウント、なるほどです。 直積という考え方は用途によっては重要ですね。 1つ問題点があり、一時カレンダーの開始日、終了日の範囲外のところで、 0のカウントができないため、営業日数が少なくなってしまうと思われます。 ※対象方法を検討中、一時カレンダーを前後1年分拡張するか、 または、計算方法を 営業日数 = datediff(d, [開始日], [終了日]) + 1 - 休日フラグ数 >|sql| /* -- 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 ||<
関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ