サーバ移転手続き完了しました

「日付」と「年・月・日」の変換は以下の通り。
┌──────────────────────────────────────┐
│日付 ← DATEFROMPARTS(年, 月, 日)                                           │
├──────────────────────────────────────┤
│年   ← DATEPART(YEAR, 日付)                                                │
│月   ← DATEPART(MONTH, 日付)                                               │
│日   ← DATEADD(DAY, 日付)                                                  │
└──────────────────────────────────────┘

「日付」を「週(年内週)」(その年の何週目か)に変換するのは以下の通り。
┌──────────────────────────────────────┐
│年内週 ← DATEPART(WEEK, 日付)                                              │
└──────────────────────────────────────┘

その逆(週から週の範囲を求める方法)は以下の通り。
┌──────────────────────────────────────┐
│週頭日付 ← DATEADD(DAY, 1 - DATEPART(WEEKDAY, 日付), 日付)                 │
│週末日付 ← DATEADD(DAY, 6, 週頭日付)                                       │
└──────────────────────────────────────┘

週の範囲がわかれば、「月内週」(その月の月週目か)がわかる。
┌──────────────────────────────────────┐
│年     ← DATEPART(YEAR, 日付)                                              │
│月     ← DATEPART(MONTH, 日付)                                             │
│月内週 ← DATEPART(WEEK, 日付)                                              │
│        - DATEPART(WEEK, DATEFROMPARTS(DATEPART(YEAR, 日付),                │
│                                       DATEPART(MONTH, 日付),               │
│                                       1))                                  │
│        + 1                                                                 │
├──────────────────────────────────────┤
│年     ← DATEPART(YEAR, 週頭日付)                                          │
│月     ← DATEPART(MONTH, 週頭日付)                                         │
│月内週 ← DATEPART(WEEK, 週頭日付)                                          │
│        - DATEPART(WEEK, DATEFROMPARTS(DATEPART(YEAR, 週頭日付),            │
│                                       DATEPART(MONTH, 週頭日付),           │
│                                       1))                                  │
│        + 1                                                                 │
└──────────────────────────────────────┘

大文字と小文字を区別する場合、「PARTITION BY」に「COLLATE Japanese」を使用する。
┌──────────────────────────────────────┐
│SELECT [キー]                                                               │
│       [集合キー],                                                          │
│       RANK() OVER(PARTITION BY [集合キー] COLLATE Japanese_CS_AS_KS_WS     │
│                   ORDER BY [キー] ASC) AS [順番]                           │
│    FROM [表];                                                              │
└──────────────────────────────────────┘

実は「GROUP BY」では大文字と小文字を区別しない。
┌──────────────────────────────────────┐
│SELECT [集計項目],                                                          │
│       COUNT([キー]) AS [件数]                                              │
│    FROM (VALUES                                                            │
│        (1, 'ABC'),                                                         │
│        (2, 'ABC'),                                                         │
│        (3, 'abc')                                                          │
│    ) AS [テストＤ] (                                                       │
│        [キー],                                                             │
│        [集計項目]                                                          │
│    )                                                                       │
│    GROUP BY [集計項目];                                                    │
└──────────────────────────────────────┘
例えば上記の場合、「ABC」が「3件」という結果になり、
「abc」も「ABC」扱いとなってしまう。

ということで、厳密に。
WHERE句で使う「COLLATE Japanese」を流用したいところだが、
「GROUP BY」句に付けたらエラーとなってしまう。

正解は、一旦SELECT文で受けてあげるという形をとればよい。
┌──────────────────────────────────────┐
│SELECT [集計項目],                                                          │
│       COUNT([キー]) AS [件数]                                              │
│    FROM (                                                                  │
│        SELECT [キー],                                                      │
│               [集計項目] COLLATE Japanese_CS_AS_KS_WS AS [集計項目]        │
│            FROM (VALUES                                                    │
│                (1, 'ABC'),                                                 │
│                (2, 'ABC'),                                                 │
│                (3, 'abc')                                                  │
│            ) AS [テストＤ] (                                               │
│                [キー],                                                     │
│                [集計項目]                                                  │
│            )                                                               │
│    )  AS [中間Ｄ]                                                          │
│    GROUP BY [集計項目];                                                    │
└──────────────────────────────────────┘
ある意味、変換関数を介する感じだ。

最近のMSSQL(SQL Server)には、「GROUPING SETS」という便利な関数がある。

これは、例えばある売上データを、日付で集計したいし、
人(営業マン)でも集計したいという場合があったとする。

こういう場合は、それぞれでSQL文を発行するのが最も簡単なやり方だ。

ただ、間が空くと、タイミングによって、結果が異なるリスクもあるので、
できれば1つのSQL文を発行することによって同時に取得したい.

となると、以下の様に「UNION ALL」を使って、たすき掛けなデータを作ることになる。
┌──────────────────────────────────────┐
│SELECT [キー1],                                                             │
│       NULL      AS [キー2],                                                │
│       SUM([値]) AS [合計]                                                  │
│    FROM [表]                                                               │
│    GROUP BY [キー1]                                                        │
│UNION ALL                                                                   │
│SELECT NULL      AS [キー1],                                                │
│       [キー2],                                                             │
│       SUM([値]) AS [合計]                                                  │
│    FROM [表]                                                               │
│    GROUP BY [キー2]                                                        │
└──────────────────────────────────────┘

これを簡単にやってくれるのが「GROUPING SETS」関数だ。
基本はこんな感じ。だいぶ見やすい。
┌──────────────────────────────────────┐
│SELECT [キー1],                                                             │
│       [キー2],                                                             │
│       SUM([値]) AS [合計]                                                  │
│    FROM [表]                                                               │
│    GROUP BY GROUPING SETS (                                                │
│                 [キー1],                                                   │
│                 [キー2]                                                    │
│             );                                                             │
└──────────────────────────────────────┘


複合キーだって、共通のサブ階層だってへっちゃら。

以下の様に応用ができる。
┌──────────────────────────────────────┐
│SELECT [キー1-1],                                                           │
│       [キー1-2],                                                           │
│       [キー2],                                                             │
│       SUM([値]) AS [合計]                                                  │
│    FROM [表]                                                               │
│    GROUP BY GROUPING SETS (                                                │
│                 ([キー1-1], [キー1-2]),                                    │
│                 [キー2]                                                    │
│             ),                                                             │
│             [キーx-3]                                                      │
│    ORDER BY [キー2]   ASC,  --NULLが先頭になるのを避けるため               │
│             [キー1-1] ASC,                                                 │
│             [キー1-2] ASC,                                                 │
│             [キーx-3] ASC;                                                 │
└──────────────────────────────────────┘

ExceVBAでADODBを使ってアクセスする場合、オープン処理というのを行う。
なので、クローズ処理というのが対応して必要になる。
(プロセスを終了してしまえばクローズ処理は端折れるのだがなんか気持ち悪い)

で、ストアドプロシージャについては、「adCmdStoredProc」を指定することにより
専用処理を使用することができる。
これも「.Execute」をテーブルオブジェクトに代入すれば
Openした場合と同じように操作できる。

で、疑問なのだが、後者の場合、Close処理は必要なのだろうか？

これを解決するために、オープン状態を確認するデバッグ文を仕込んでみた。

その結果、後者の場合もオープン状態はオープン中だということがわかる。
ということで、後者も前者と同じ様にクローズ処理を実装した方がよさそうだ。

ちなみに、実験したのは、以下のような感じの処理
(グローバル変数(メンバ変数)の設定処理は省略して記載)
┌──────────────────────────────────────┐
│Private mデータベース As New ADODB.Connection                               │
│Private mSQL文 As String                                                    │
├──────────────────────────────────────┤
│Dim テーブル As New ADODB.Recordset                                         │
│Dim SQLコマンド As New ADODB.Command                                        │
│'-(オープン) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -│
│With SQLコマンド                                                            │
│    .ActiveConnection = mデータベース                                       │
│    .CommandText = mSQL文                                                   │
│End With                                                                    │
│テーブル.Open SQLコマンド                                                   │
│If mテーブル.State = adStateClosed Then                                     │
│    MsgBox "エラー", vbCritical                                             │
│    ' エラー処理                                                            │
│    Exit Sub                                                                │
│End If                                                                      │
│MsgBox テーブル.State   ' オープン状態のの確認 → 1(adStateOpen)＝オープン中│
│'-(参照処理) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -│
│' テーブル.Fields(列名).Value などを参照                                    │
│'-(クローズ) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -│
│If テーブル.State <> adStateClosed Then                                     │
│    テーブル.Close                                                          │
│End If                                                                      │
└──────────────────────────────────────┘
┌──────────────────────────────────────┐
│Private mデータベース As New ADODB.Connection                               │
├──────────────────────────────────────┤
│Dim テーブル As New ADODB.Recordset                                         │
│Dim SQLコマンド As New ADODB.Command                                        │
│'-(オープン) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -│
│With SQLコマンド                                                            │
│    .ActiveConnection = mデータベース                                       │
│    .CommandType = adCmdStoredProc                                          │
│    .CommandText = "ストアドプロシージャ"                                   │
│    Set テーブル = .Execute                                                 │
│End With                                                                    │
│MsgBox テーブル.State   ' オープン状態のの確認 → 1(adStateOpen)＝オープン中│
│'-(参照処理) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -│
│' テーブル.Fields(列名).Value などを参照                                    │
│'-(クローズ) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -│
│If テーブル.State <> adStateClosed Then                                     │
│    テーブル.Close                                                          │
│End If                                                                      │
└──────────────────────────────────────┘

以下のような感じ。
┌──────────────────────────────────────┐
│Dim 今月頭 As Date: 今月頭 = DateSerial(今年, 今月    , 1)                  │
├──────────────────────────────────────┤
│Dim 今月末 As Date: 今月末 = DateSerial(今年, 今月 + 1, 0)                  │
└──────────────────────────────────────┘
日に「0」を指定することにより、「-1日」(前日)扱いとなる。
よって、来月の前日を求めれば今月末が求まるということだ。

MSSQLでは「計算列」という自動で計算してくれる列がある。
たいていは、「単価×数量」の結果を格納する列にそれを設定するのだが、
キーのグルーピングにも応用できそうだ。

以下のような感じ。(主キーの先頭4文字を別キーとする)
┌──────────────────────────────────────┐
│CREATE TABLE [dbo].[表] (                                                   │
│    [キー]                  [int]               NOT NULL,                   │
│    [部分キー]              AS SUBSTRING([キー], 0, 4),                     │
│    CONSTRAINT [主キー] PRIMARY KEY CLUSTERED (                             │
│        [キー]                  ASC                                         │
│    )                                                                       │
│);                                                                          │
├──────────────────────────────────────┤
│CREATE INDEX [索引] ON [dbo].[表] (                                         │
│    [部分キー]                                                              │
│);                                                                          │
└──────────────────────────────────────┘

インデックスも張れるので、都度文字列編集しながらの検索とかが
インデックスを効かせて快適(快速)に行うことができる。
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM [表]                                                               │
│    WHERE SUBSTRING([キー], 0, 4) = 'XXXX';                                 │
├──────────────────────────────────────┤
│SELECT *                                                                    │
│    FROM [表]                                                               │
│    WHERE [部分キー] = 'XXXX';                                              │
└──────────────────────────────────────┘
これは便利。

なお、「計算列」には直接INSERTできない(エラー)から注意(というか当たり前か)。

以下の二つの案がある

【あらかじめ集計テーブルを作り結合する案】
┌──────────────────────────────────────┐
│SELECT [親表].[キー],                                                       │
│       ISNULL([子表].[件数], 0) AS [件数]                                   │
│    FROM [親表]                                                             │
│        LEFT JOIN (                                                         │
│           SELECT [外部キー],                                               │
│                  COUNT(*)   AS [件数]                                      │
│               FROM [子表]                                                  │
│               GROUP BY [外部キー]                                          │
│        ), 0) AS [子表]                                                     │
│          ON [子表].[外部キー] = [親表].[キー]                              │
└──────────────────────────────────────┘

【副問い合わせにて集計する案】
┌──────────────────────────────────────┐
│SELECT [親表].[キー],                                                       │
│       ISNULL((                                                             │
│           SELECT COUNT(*)                                                  │
│               FROM [子表]                                                  │
│               WHERE [子表].[外部キー] = [親表].[キー]                      │
│       ), 0) AS [件数]                                                      │
│    FROM [親表];                                                            │
└──────────────────────────────────────┘

結果の種類が多い場合は前者が、少ない場合は後者が有利っぽいが、
最適化された結果、そんなに変わらないみたい。
＃一見、後者は毎回集計しそうだが、あらかじめ集計テーブルを作っぽい

コピペという点では後者が有利っぽいが、性能的にはちょっとだけ前者がよさそう。

「SQL Server Management Studio」のクエリにて、実行計画を表示する設定。
┌──────────────────────────────────────┐
│SET STATISTICS PROFILE ON;                                                  │
└──────────────────────────────────────┘
元に戻すには以下の通り。
┌──────────────────────────────────────┐
│SET STATISTICS PROFILE OFF;                                                 │
└──────────────────────────────────────┘