Microsoft Access 掲示板

一時的な日付範囲指定を含む日付データからの抽出 / 5

8 コメント

views

4 フォロー

( 続き )

＃最初は SQLの一筆書きを投稿しようとしたところ
＃ 160行の記述が長すぎて投稿不可であったことと
＃サブクエリのネストが深くて、記述が複雑怪奇になったため、
＃構造別にクエリを複数( 計7つ )作るという回答に切り替えました。

まぁ...個々の記述が短いと最終結果が出るまでの工程も分かりやすいでしょう。
( クエリを作る順番も、掲載した順と同じになります )

SQL文
クエリ名
SQLの結果セット

SELECT 開始日
     , cdate(
           nz( 
               x.終了日
             , nz( 
                   ( 
                     SELECT min( 開始日 ) - 1 
                       FROM 単価マスタ y 
                      WHERE y.開始日 >= dateserial( year( date() )
                                                  , month( date() )
                                                  , 1
                                        )
                        AND y.開始日 > x.開始日
                   )
                 , dateserial( 2099, 12, 31 )
               )
          ) 
      ) As 終了日
    , 単価
FROM 単価マスタ x
WHERE 開始日 >= dateserial( year( date() ), month( date() ), 1 ) ;

q_base

開始日	終了日	単価
2021/08/01	2021/08/04	150
2021/08/05	2021/08/10	120
2021/08/20	2021/08/22	140
2021/08/13	2021/08/19	100
2021/08/24	2021/08/27	15
2021/08/28	2099/12/31	30

SELECT dateadd( 'd', x.num + y.num * 10, z.d_min ) As ymd
FROM T_num x
   , T_num y
   , ( 
         SELECT min( 開始日 ) As d_min
              , datediff( 'd', min( 開始日 ), max( 開始日 ) ) As d_count
         FROM 単価マスタ
         WHERE 開始日 >= dateserial( year( date() ), month( date() ), 1 )
     ) z
WHERE x.num + y.num * 10 < z.d_count ;

q_ymd

ymd
2021/8/1
2021/8/2
2021/8/3
( 省略)
2021/8/25
2021/8/26
2021/8/27

SELECT x.ymd
FROM      q_ymd x
LEFT JOIN q_base y
       ON ( ( x.ymd between y.開始日 and y.終了日 ) )
WHERE y.開始日 Is Null ;

q_ymdlist

ymd
2021/8/11
2021/8/12
2021/8/23

( 続き )

＃ 最初は SQLの一筆書きを投稿しようとしたところ
＃ 160行の記述が長すぎて投稿不可であったことと
＃ サブクエリのネストが深くて、記述が複雑怪奇になったため、
＃ 構造別にクエリを複数( 計7つ )作るという回答に切り替えました。

まぁ...個々の記述が短いと 最終結果が出るまでの工程も分かりやすいでしょう。
( クエリを作る順番も、掲載した順と同じになります )
### 
- SQL文
- クエリ名
- SQLの結果セット

　
[q_base:---
```sql
SELECT 開始日
     , cdate(
           nz( 
               x.終了日
             , nz( 
                   ( 
                     SELECT min( 開始日 ) - 1 
                       FROM 単価マスタ y 
                      WHERE y.開始日 >= dateserial( year( date() )
                                                  , month( date() )
                                                  , 1
                                        )
                        AND y.開始日 > x.開始日
                   )
                 , dateserial( 2099, 12, 31 )
               )
          ) 
      ) As 終了日
    , 単価
FROM 単価マスタ x
WHERE 開始日 >= dateserial( year( date() ), month( date() ), 1 ) ;
```
---]

q_base

|   開始日   |   終了日   | 単価 |
|:----------:|:----------:|-----:|
| 2021/08/01 | 2021/08/04 | 150  |
| 2021/08/05 | 2021/08/10 | 120  |
| 2021/08/20 | 2021/08/22 | 140  |
| 2021/08/13 | 2021/08/19 | 100  |
| 2021/08/24 | 2021/08/27 | 15   |
| 2021/08/28 | 2099/12/31 | 30   |

　
[q_ymd:---
```sql
SELECT dateadd( 'd', x.num + y.num * 10, z.d_min ) As ymd
FROM T_num x
   , T_num y
   , ( 
         SELECT min( 開始日 ) As d_min
              , datediff( 'd', min( 開始日 ), max( 開始日 ) ) As d_count
         FROM 単価マスタ
         WHERE 開始日 >= dateserial( year( date() ), month( date() ), 1 )
     ) z
WHERE x.num + y.num * 10 < z.d_count ;
```
---]

q_ymd

|    ymd    |   |
|:---------:|---|
| 2021/8/1  |   |
| 2021/8/2  |   |
| 2021/8/3  |   |
| 　( 省略) |   |
| 2021/8/25 |   |
| 2021/8/26 |   |
| 2021/8/27 |   |

　
[q_ymdlist:---
```sql
SELECT x.ymd
FROM      q_ymd x
LEFT JOIN q_base y
       ON ( ( x.ymd between y.開始日 and y.終了日 ) )
WHERE y.開始日 Is Null ;
```
---]

q_ymdlist

| ymd       |   |
|-----------|---|
| 2021/8/11 |   |
| 2021/8/12 |   |
| 2021/8/23 |   |

ソース表示通報 ...

( 続き )
　

SELECT min( ymd ) As d_from
     , max( ymd ) As d_to
FROM
(
    SELECT x.ymd
         , count(1) As n
    FROM q_ymdlist x
       , q_ymdlist y
    WHERE x.ymd >= y.ymd
    GROUP BY x.ymd
) q
GROUP BY ymd - n ;

q_fromto

d_from	d_to
2021/08/11	2021/08/12
2021/08/23	2021/08/23

SELECT x.d_from
     , x.d_to
     , max( iif( y.終了日 Is Null, y.開始日 ) ) As high
     , max( y.開始日 ) As low
FROM q_fromto x
LEFT JOIN 単価マスタ y
       ON ( 
              (
                  x.d_from > y.開始日
                      AND
                  y.開始日 >= dateserial( year( date() ), month( date() ), 1 )
              )
          )
GROUP BY x.d_from
       , x.d_to ;

q_highlow

d_from	d_to	high	low
2021/08/11	2021/08/12	2021/08/01	2021/08/05
2021/08/23	2021/08/23	2021/08/13	2021/08/20

SELECT x.d_from
     , x.d_to
     , y.開始日 As 基準開始日
     , 1 As 補填行
     , y.単価
FROM q_highlow x
   , 単価マスタ y
WHERE cdate( nz( x.high, x.low ) ) = y.開始日 ;

q_union

d_from	d_to	基準開始日	補填行	単価
2021/08/11	2021/08/12	2021/08/01	1	150
2021/08/23	2021/08/23	2021/08/13	1	100

SELECT 開始日 As 指定開始日
     , 終了日 As 指定終了日
     , 開始日 As 基準開始日
     , 0 As 補填行
     , 単価
FROM q_base
UNION ALL
SELECT d_from
     , d_to
     , 基準開始日
     , 補填行
     , 単価
FROM q_union
ORDER BY 1 ;

q_result

指定開始日	指定終了日	基準開始日	補填行	単価
2021/08/01	2021/08/04	2021/08/01	0	150
2021/08/05	2021/08/10	2021/08/05	0	120
2021/08/11	2021/08/12	2021/08/01	1	150
2021/08/13	2021/08/19	2021/08/13	0	100
2021/08/20	2021/08/22	2021/08/20	0	140
2021/08/23	2021/08/23	2021/08/13	1	100
2021/08/24	2021/08/27	2021/08/24	0	15
2021/08/28	2099/12/31	2021/08/28	0	30

タグリスト

VBA (389) Office2016 (294) フォーム (243) Office2021 or 2019 (224) クエリ SQL (220) テーブル (139) レポート (130) Office2013 (57) Office2010 (52) Microsoft365 (49)

Markdown テーブル生成

Accessのテーブルやクエリのデータを貼り付ける場合は下記で、Markdown書式のテーブルに変換して貼り付けてください。

Markdown Tables generator

Microsoft Access 掲示板

一時的な日付範囲指定を含む日付データからの抽出 / 5

最新トピック

タグリスト

Markdown テーブル生成