Microsoft Access 掲示板

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

8 コメント

views

4 フォロー

例
開始日　　　　　終了日　　　　　単価
2021/08/01　　　Null　　　　　　150
2021/08/05　　　2021/08/10　　　120
2021/08/20　　　Null　　　　　　140

このようなデータで日付を指定した際に
指定日付　2021/08/01　～　2021/08/04　基準開始日　2021/08/01　　単価150　　
指定日付　2021/08/05　～　2021/08/10　基準開始日　2021/08/05　　単価120
指定日付　2021/08/11　～　2021/08/19　基準開始日　2021/08/01　　単価150
指定日付　2021/08/20　～　　　　　　基準開始日　2021/08/20　　単価140
と基準開始日と単価が返ってくるようにしたいのです

　　
私個人は、hirotonさんやhatenaさんと同じく
ソリューションとしては圧倒的に
{ テーブルデータの保有形式を変更 }　＞＞＞　{ トリッキーな SQL または VBA }
という見解です。

ただ Access の一般機能で「実現可能」なご要望であることも事実ですから
実用性とパフォーマンスは度外視で、SQL( クエリ )のサンプル を載せておきます。
( ※ 今回の投稿はテーブル定義とデータ例のみ )

なお、( 何らかの )日付範囲の抽出条件を満たした後のデータが
「例」の３行であるとするなら

「 指定日付 」に該当するようなパラメータなりリテラル値は
開始日フィールドに指定された可能性が高いと推測し、
私の回答( SQL )では、現状のテーブルデータに影響を与えない抽出条件として

    開始日 >= dateserial( year( date() ), month( date() ), 1 )

を指定しています。
　　

■テーブル：単価マスタ

CREATE TABLE 単価マスタ
(
      rid    counter  not null primary key
    , 開始日 datetime not null
    , 終了日 datetime
    , 単価   money    not null
);

rid	開始日	終了日	単価
1	2021/08/01		150
2	2021/08/05	2021/08/10	120
3	2021/08/13		100
4	2021/08/20	2021/08/22	140
5	2021/08/24		15
6	2021/08/28		30

　　
　　
■テーブル： T_num　　

CREATE TABLE T_num ( num int not null primary key );

( T_num テーブルの num には 0 ～ 9 までの数値を入力 )

num
0
1
2
3
4
5
6
7
8
9

> 例
> 開始日　　　　　終了日　　　　　単価
> 2021/08/01　　　Null　　　　　　150
> 2021/08/05　　　2021/08/10　　　120
> 2021/08/20　　　Null　　　　　　140
> 
> このようなデータで日付を指定した際に
> 指定日付　2021/08/01　～　2021/08/04　基準開始日　2021/08/01　　単価150　　
> 指定日付　2021/08/05　～　2021/08/10　基準開始日　2021/08/05　　単価120
> 指定日付　2021/08/11　～　2021/08/19　基準開始日　2021/08/01　　単価150
> 指定日付　2021/08/20　～　　　　　　    基準開始日　2021/08/20　　単価140
> と基準開始日と単価が返ってくるようにしたいのです

　　 
私個人は、hirotonさんやhatenaさんと同じく
ソリューションとしては 圧倒的に
%%{size:12pt}%%{fg:blue}{ テーブルデータの保有形式を変更 }%%　**＞＞＞**　%%{fg:red}{ トリッキーな SQL または VBA }%%%%
という見解です。

ただ Access の一般機能で「 実現可能 」な ご要望であることも事実ですから
実用性とパフォーマンスは度外視で、**SQL( クエリ )のサンプル** を載せておきます。
( ※ 今回の投稿はテーブル定義とデータ例のみ )

なお、( 何らかの )日付範囲の抽出条件を満たした後のデータが
「 **例** 」の３行であるとするなら

「 **指定日付** 」に該当するような パラメータなりリテラル値は
開始日フィールドに指定された可能性が高いと推測し、
私の回答( SQL )では、現状のテーブルデータに影響を与えない抽出条件として
``` 
    開始日 >= dateserial( year( date() ), month( date() ), 1 ) 
```
を指定しています。
　　

■テーブル： 単価マスタ
```sql
CREATE TABLE 単価マスタ
(
      rid    counter  not null primary key
    , 開始日 datetime not null
    , 終了日 datetime
    , 単価   money    not null
);
```

| rid |   開始日   |   終了日   | 単価 |
|:---:|:----------:|:----------:|:----:|
| 1   | 2021/08/01 |            | 150  |
| 2   | 2021/08/05 | 2021/08/10 | 120  |
| 3   | 2021/08/13 |            | 100  |
| 4   | 2021/08/20 | 2021/08/22 | 140  |
| 5   | 2021/08/24 |            | 15   |
| 6   | 2021/08/28 |            | 30   |

　　
　　
■テーブル： T_num　　
```sql
CREATE TABLE T_num ( num int not null primary key );
```
( T_num テーブル の num には 0 ～ 9 までの数値を入力 )

| num |       |
|:---:|:-----:|
| 0   |       |
| 1   |       |
| 2   |       |
| 3   |       |
| 4   |       |
| 5   |       |
| 6   |       |
| 7   |       |
| 8   |       |
| 9   |       |

ソース表示通報 ...

( 続き )

＃最初は 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

( 続き )
　

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 (386) Office2016 (294) フォーム (242) Office2021 or 2019 (224) クエリ SQL (220) テーブル (139) レポート (128) Office2013 (57) Office2010 (50) Microsoft365 (47)

Markdown テーブル生成

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

Markdown Tables generator

Microsoft Access 掲示板

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

最新トピック

タグリスト

Markdown テーブル生成