Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : その他)
連続する日付範囲ごとに抽出
投稿日時: 21/08/07 20:57:08
投稿者: Manabukunn

連続する日付範囲ごとに抽出するクエリなどができないかで
困っております。
 
テーブルA
薬剤名  使用開始日  終了日
A     2020/12/1 2020/12/3
A     2020/12/4 2020/12/10
A 2020/12/11 2020/12/30
A 2021/2/10 2020/2/15
A 2021/2/16  2020/2/27
A     2021/2/28  2021/4/5
 
とデータが入っていたとします。
クエリなどをつかって
 
薬剤名  使用開始日  終了日
A  2020/12/1 2020/12/30
A  2021/2/10 2021/4/5
 
と日付の連続する期間を一つのデータとして
抽出することは出るでしょうか。
お知恵をお願いいたします。
 

回答
投稿日時: 21/08/07 22:11:18
投稿者: mayu.

Manabukunn さんの引用:
日付の連続する期間を一つのデータとして抽出することは出るでしょうか。

■SQL
SELECT 薬剤名
     , 開始日
     , Min( q.終了日 ) As 終了日
FROM
(
    SELECT x.薬剤名
         , x.使用開始日 As 開始日
         , y.終了日
    FROM テーブルA x
    INNER JOIN ( テーブルA y
                 INNER JOIN テーブルA z
                         ON y.薬剤名 = z.薬剤名
    )
    ON x.薬剤名 = y.薬剤名
    WHERE x.終了日 <= y.終了日
    GROUP BY x.薬剤名
           , x.使用開始日
           , y.終了日
    HAVING Sum(
               IIf( 
                       ( z.使用開始日 <  x.使用開始日
                            AND 
                         x.使用開始日 <= DateAdd( 'd', 1, z.終了日 )
                       )
                   OR
                       ( y.終了日     <  z.終了日
                           AND 
                         z.使用開始日 <= DateAdd( 'd', 1, y.終了日 )
                       )
                 , 1
                 , 0
               )
           ) = 0
) q
GROUP BY 薬剤名
       , 開始日
ORDER BY 1, 2 ;

投稿日時: 21/08/07 23:02:32
投稿者: Manabukunn

mayu.さん
有難うございます!!
やりたいことはできました。
SQLってすごいですね。
  
できたのはできたのですが
なかなか自分には理解ができない状況です。
  
薬剤名  使用開始日  終了日
A     2020/12/1 2020/12/3
A     2020/12/4 2020/12/10
A     2020/12/11 2020/12/30
 
たとえば今回のように連続するものが一つしかないと
仮定した場合はどうなるのでしょうか。
おそらくこちらの方が理解しやすいかとおもいますので
ご面倒ですが再度よろしくお願いいたします。

回答
投稿日時: 21/08/08 00:57:07
投稿者: mayu.

Manabukunn さんの引用:
薬剤名  使用開始日  終了日
A     2020/12/1 2020/12/3
A     2020/12/4 2020/12/10
A     2020/12/11 2020/12/30
 
たとえば今回のように連続するものが一つしかないと
仮定した場合はどうなるのでしょうか。

SQL文は、一番内側の記述から順に把握するといいでしょう。
今回ですと、FROM句のサブクエリを解析することになります。
 
SELECT x.薬剤名
     , x.使用開始日 As x_始
     , x.終了日     As x_終
     , y.使用開始日 As y_始
     , y.終了日     As y_終
     , z.使用開始日 As z_始
     , z.終了日     As z_終
     , IIf( z.使用開始日 <  x.使用開始日
                AND
            x.使用開始日 <= DateAdd( 'd', 1, z.終了日 )
         , '×'
         , '〇'
       ) As 始_back
     , IIf( y.終了日 <  z.終了日
                AND
            z.使用開始日 <= DateAdd( 'd', 1, y.終了日 )
         , '×'
         , '〇'
       ) As 終_foward
FROM テーブルA x
INNER JOIN ( テーブルA y
             INNER JOIN テーブルA z
                     ON y.薬剤名 = z.薬剤名
)
ON x.薬剤名 = y.薬剤名
WHERE x.終了日 <= y.終了日
ORDER BY x.使用開始日
       , y.終了日
       , z.終了日 ;

■ 結果
x_始	x_終	y_始	y_終	z_始	z_終	始_back		終_foward
---------------------------------------------------------------------
12/01	12/03	12/01	12/03	12/01	12/03	〇		〇
12/01	12/03	12/01	12/03	12/04	12/10	〇		×
12/01	12/03	12/01	12/03	12/11	12/30	〇		〇
---------------------------------------------------------------------
12/01	12/03	12/04	12/10	12/01	12/03	〇		〇
12/01	12/03	12/04	12/10	12/04	12/10	〇		〇
12/01	12/03	12/04	12/10	12/11	12/30	〇		×
---------------------------------------------------------------------
12/01	12/03	12/11	12/30	12/01	12/03	〇		〇
12/01	12/03	12/11	12/30	12/04	12/10	〇		〇
12/01	12/03	12/11	12/30	12/11	12/30	〇		〇
---------------------------------------------------------------------
12/04	12/10	12/04	12/10	12/01	12/03	×		〇
12/04	12/10	12/04	12/10	12/04	12/10	〇		〇
12/04	12/10	12/04	12/10	12/11	12/30	〇		×
---------------------------------------------------------------------
12/04	12/10	12/11	12/30	12/01	12/03	×		〇
12/04	12/10	12/11	12/30	12/04	12/10	〇		〇
12/04	12/10	12/11	12/30	12/11	12/30	〇		〇
---------------------------------------------------------------------
12/11	12/30	12/11	12/30	12/01	12/03	〇		〇
12/11	12/30	12/11	12/30	12/04	12/10	×		〇
12/11	12/30	12/11	12/30	12/11	12/30	〇		〇

x_始    --- 母集団 ( グルーピング項目 )
y_終   --- 母集団 ( グルーピング項目 )
始_back  --- 開始日をもっと前に伸ばせるか(×)否か(〇)を判別
終_foward --- 終了日をもっと後に伸ばせるか(×)否か(〇)を判別
 
SQLの結果セットには、6つのグループが出来ます。
( 各グループが目視確認しやすいよう、点線で区切っています )
 
グループの中で
始_back と 終_foward のいずれか1つでも × が付くということは
開始日 か 終了日 を 他レコードの値で代替可能 】ということですから
該当したグループは除外します。
 
その結果、始_back と 終_foward 全てが 〇 になっているグループは
 
x_始	x_終	y_始	y_終	z_始	z_終	始_back		終_foward
---------------------------------------------------------------------
12/01	12/03	12/11	12/30	12/01	12/03	〇		〇
12/01	12/03	12/11	12/30	12/04	12/10	〇		〇
12/01	12/03	12/11	12/30	12/11	12/30	〇		〇

となります。

投稿日時: 21/08/14 22:18:42
投稿者: Manabukunn

mayu.様
 
この度はご丁寧なご試読ありがとうございました。
理解いたしました!!
とこのページに記載させていただきたかったのですが
なかなか困難です。
今後ともSQLも学んでいき何とか理解できる日を迎えたいと
思っております。
お礼が遅くなり申し訳ございません。
今後ともよろしくお願いいたします。