こちらの記事で使用したSQLのスクリプトです
| コマンド | フィールド名 |
|---|---|
| クエリ名 | 0010 work_csr q |
| 説 明 | |
| select_into | [0010 work_csr] |
| select | [_drug].PERSON_ID |
| select | [_drug].D_STD |
| select | [_condition].C_STD |
| select | Switch([D_STD]<[C_STD],"D->C",[D_STD]>[C_STD],"C->D",True,"UNK") AS FLG INTO [0010 work_csr] |
| from | _drug INNER JOIN |
| from | _conditiON |
| from | [_drug].PERSON_ID = [_condition].PERSON_ID |
| group by | [_drug].PERSON_ID |
| group by | [_drug].D_STD |
| group by | [_condition].C_STD |
| group by | Switch([D_STD]<[C_STD],"D->C",[D_STD]>[C_STD],"C->D",True,"UNK") |
| クエリ名 | 0020 work_out q |
| 説 明 | |
| select_into | [0020 work_out] |
| select | [0010 work_csr].FLG |
| select | Count([0010 work_csr].PERSON_ID) AS [Count] INTO [0020 work_out] |
| from | [0010 work_csr] |
| group by | [0010 work_csr].FLG |
| クエリ名 | 0030 work_output1 q |
| 説 明 | |
| select_into | [0030 work_output1] |
| select | Sum(IIf([0020 work_out].[FLG]="D->C",[0020 work_out].[Count],0)) AS DC |
| select | Sum(IIf([0020 work_out].[FLG]="C->D",[0020 work_out].[COUNT],0)) AS CD |
| select | Sum([0020 work_out].Count) AS TOTAL |
| select | [DC]/[CD] AS CSR INTO [0030 work_output1] |
| from | [0020 work_out] |
| group by | [DC]/[CD] |
| クエリ名 | 0040 D_TOTAL q |
| 説 明 | |
| select_into | [0040 D_TOTAL] |
| select | Count([_drug].[PERSON_ID]) AS D_TOTAL INTO [0040 D_TOTAL] |
| from | _drug |
| クエリ名 | 0050 C_TOTAL q |
| 説 明 | |
| select_into | [0050 C_TOTAL] |
| select | Count([_condition].PERSON_ID) AS C_TOTAL INTO [0050 C_TOTAL] |
| from | _condition |
| クエリ名 | 0060 D_COUNT q |
| 説 明 | |
| select_into | [0060 D_COUNT] |
| select | [_drug].[D_STD] |
| select | Count([_drug].[PERSON_ID]) AS D_COUNT INTO [0060 D_COUNT] |
| from | _drug |
| group by | [_drug].[D_STD] |
| クエリ名 | 0070 D_COUNT2 q |
| 説 明 | |
| select_into | [0070 D_COUNT2] |
| select | [0060 D_COUNT].D_STD |
| select | [0060 D_COUNT].D_COUNT |
| select | [_condition].PERSON_ID |
| select | [_condition].C_STD INTO [0070 D_COUNT2] |
| from | [0060 D_COUNT], _condition |
| where | ((([C_STD]-[D_STD])>0)) |
| order by | [0060 D_COUNT].D_STD |
| クエリ名 | 0080 D_COUNT3 q |
| 説 明 | |
| select_into | [0080 D_COUNT3] |
| select | [0070 D_COUNT2].D_STD |
| select | Count([0070 D_COUNT2].D_COUNT) AS [COUNT] INTO [0080 D_COUNT3] |
| from | [0070 D_COUNT2] |
| group by | [0070 D_COUNT2].D_STD |
| order by | [0070 D_COUNT2].D_STD |
| クエリ名 | 0090 D_COUNT4 q |
| 説 明 | |
| select_into | [0090 D_COUNT4] |
| select | [0080 D_COUNT3].D_STD |
| select | [0080 D_COUNT3].COUNT |
| select | [0060 D_COUNT].D_COUNT INTO [0090 D_COUNT4] |
| from | [0080 D_COUNT3] INNER JOIN |
| from | [0060 D_COUNT] ON |
| from | [0080 D_COUNT3].D_STD = [0060 D_COUNT].D_STD |
| order by | [0080 D_COUNT3].D_STD |
| クエリ名 | 0100 D_COUNT4 q |
| 説 明 | |
| select_into | [0100 D_COUNT4] |
| select | [EXP_C] AS TOTAL_SUM |
| select | [0090 D_COUNT4].D_STD |
| select | [0090 D_COUNT4].COUNT |
| select | [0090 D_COUNT4].D_COUNT |
| select | [0050 C_TOTAL].[C_TOTAL] AS N |
| select | [COUNT]/[N] AS P |
| select | [P]*[D_COUNT] AS EXP_C INTO [0100 D_COUNT4] |
| from | [0090 D_COUNT4], [0050 C_TOTAL] |
| group by | [EXP_C] |
| group by | [0090 D_COUNT4].D_STD |
| group by | [0090 D_COUNT4].COUNT |
| group by | [0090 D_COUNT4].D_COUNT |
| group by | [0050 C_TOTAL].[C_TOTAL] |
| group by | [COUNT]/[N] |
| group by | [P]*[D_COUNT] |
| クエリ名 | 0140 D_EXP q |
| 説 明 | |
| select_into | [0140 D_EXP] |
| select | Sum([0100 D_COUNT4].EXP_C) AS D_EXP INTO [0140 D_EXP] |
| from | [0100 D_COUNT4] |
| クエリ名 | 0150 OUTPUT2 q |
| 説 明 | |
| select_into | [0150 OUTPUT2] |
| select | [0140 D_EXP].[D_EXP] |
| select | [0040 D_TOTAL].D_TOTAL |
| select | [D_EXP]/[D_TOTAL] AS A |
| select | [A]/(1-[A]) AS NSR INTO [0150 OUTPUT2] |
| from | [0140 D_EXP], [0040 D_TOTAL] |
| クエリ名 | 0160 ASR q |
| 説 明 | |
| select_into | [0160 ASR] |
| select | [0030 work_output1].CSR |
| select | [0150 OUTPUT2].NSR |
| select | [CSR]/[NSR] AS ASR |
| select | [0030 work_output1].CD |
| select | [0030 work_output1].DC INTO [0160 ASR] |
| from | [0030 work_output1], [0150 OUTPUT2] |
