特定の値を除く最大値を取得するには？

Question

前提・実現したいこと

エクセルでいうCOUNTIFのMAX版のようなものがSQLにないでしょうか？
：SQLserver

質問

よくあるヘッダと明細のテーブルにて

ヘッダ
伝票番号　0001

明細　日付　金額
明細番号001　2011/11/01　1900
明細番号002　2011/11/02　2900
明細番号003　2011/11/03　3900
明細番号004　2011/11/04　4900
明細番号005　2999/12/31　5900

があるとして、明細データのサマリーを持たせた伝票ヘッダの情報を取得する際、
2999/12/31を覗いた値の中の最大値2011/11/04を「カラムA」として取得したいです。

取得データとしては2999/12/31も含めた金額のサマリーや、2999/12/31も含めた明細数のカウントも必要となります。

この場合一番スマートなSQLはどのようになるでしょうか。

ぱっと思いついたのは
サブクエリで明細の別テーブル（2999/12/31を除いたもの）を用意し、それをJOINしてMAXを取る方法なのですが、エクセルで言うCOUNTIFのMAX版みたいなのがあれば、別テーブルいらないのかなと思いました。

もう少しいい方法はありますか？

Answer 1

こういうことでしょうか？

WITH TestTable AS (
  SELECT N'伝票番号001'AS [Denpyo], N'明細番号001' AS [Meisai], '2011/11/01' AS [Date], 1900 AS [Price] UNION ALL
  SELECT N'伝票番号001'AS [Denpyo], N'明細番号002' AS [Meisai], '2011/11/02' AS [Date], 2900 AS [Price] UNION ALL
  SELECT N'伝票番号001'AS [Denpyo], N'明細番号003' AS [Meisai], '2011/11/03' AS [Date], 3900 AS [Price] UNION ALL
  SELECT N'伝票番号001'AS [Denpyo], N'明細番号004' AS [Meisai], '2011/11/04' AS [Date], 4900 AS [Price] UNION ALL
  SELECT N'伝票番号001'AS [Denpyo], N'明細番号005' AS [Meisai], '2999/12/31' AS [Date], 5900 AS [Price] UNION ALL

  SELECT N'伝票番号002'AS [Denpyo], N'明細番号201' AS [Meisai], '2020/11/11' AS [Date], 1900 AS [Price] UNION ALL
  SELECT N'伝票番号002'AS [Denpyo], N'明細番号202' AS [Meisai], '2020/12/12' AS [Date], 2900 AS [Price] UNION ALL
  SELECT N'伝票番号002'AS [Denpyo], N'明細番号205' AS [Meisai], '2999/12/31' AS [Date], 5900 AS [Price] 
)

SELECT *

-- MAXの中に CASE 文を書く。
-- 特定の日付だったらNULLに差し替えてMAXの候補に入らないようにしてしまう
, MAX(
    CASE WHEN [Date] = '2999/12/31' THEN NULL
         ELSE [Date]
    END
  ) OVER(PARTITION BY Denpyo)

  AS [MAX内でCASE]

-- サブクエリで取る。
, (
    SELECT MAX([Date]) FROM TestTable sub
    WHERE [Date] <> '2999/12/31'
    AND sub.Denpyo = t.Denpyo
    GROUP BY sub.Denpyo
  )
  AS [サブクエリ] 

FROM TestTable t
ORDER BY t.Denpyo, t.Meisai

とりあえず 2 案。
でもサブクエリの方が個人的にしっくりくるかな…？ どうでしょう

Comments

[ms5025]

ありがとうございます！
MAXの中にCASE入れる事できるんですね！

サブクエリのほうがsk_3122さん的にはお好みですか？

[sk_3122]

単純に好みの問題でサブクエリの方が好きかな…？ というレベルなのですが。
ぱっと見わかりやすい気がする…？ という程度です。

あとパフォーマンスとかどうなんでしょうね…？ 検証したことは無いのですが。

[ms5025]

ありがとうございます。
やっぱりサブクエリにしておきます。

Answer 2

一回で済ませなくてもいいような気もしますが

WITH W AS(
  SELECT * FROM 明細データ WHERE 伝票番号 = '0001'
)
SELECT COUNT(T.明細) COUNT, SUM(T.金額) SUM, (
  SELECT MAX(T2.日付) FROM W T2 WHERE T2.日付 <> '2999-12-31'
) カラムA
FROM W T

Comments

[ms5025]

結局サブクエリを使うしかないかなという感じですかね？
COUNTみたいに関数内にcase文やIF文書けないのかなーとおもってしまいまして。

[x_x]

書けます
https://docs.microsoft.com/ja-jp/sql/t-sql/functions/max-transact-sql?view=sql-server-2017
その場合はNULLにするんでしょう。
MAX(CASE T.日付 WHEN '2999-12-31' THEN NULL ELSE T.日付 END) カラムA

[ms5025]

ありがとうございます！MAXにCASE文入れることできるんですね！

Answer 3

こんな感じですかね

create table tbl(明細 varchar(10),日付 date,金額 int);
insert into tbl values
('001','2011/11/01','1900'),
('002','2011/11/02','2900'),
('003','2011/11/03','3900'),
('004','2011/11/04','4900'),
('005','2999/12/31','5900');
select * from tbl as t1 where not exists(
select 1 from tbl where and 日付<'2999/12/31' and t1.金額<金額
) and 日付<'2999/12/31'

修正

create table tbl(伝票番号 varchar(10),明細 varchar(10),日付 date,金額 int);
insert into tbl values
('0001','001','2011/11/01','1900'),
('0001','002','2011/11/02','2900'),
('0001','003','2011/11/03','5000'),
('0001','004','2011/11/04','4900'),
('0001','005','2999/12/31','5900'),
('0002','001','2011/11/05','5000');

2999/12/31を除く最大値の日付を持つ金額

select 伝票番号,金額 from tbl as t1 where not exists(
select 1 from tbl where 日付<'2999/12/31' and t1.日付<日付 and t1.伝票番号=伝票番号
) and 日付<'2999/12/31' and 伝票番号='0001'

Comments

[ms5025]

詳細は割愛しますがこういった一般的なヘッダ明細の伝票テーブルにおいて
T_header（ヘッダ）
T_detail（明細）

SQL

select　
T_header.明細番号
,sum(T_detail.金額）
from　T_header
left join T_detail
on
T_header.伝票番号=T_detail.伝票番号

結果：

伝票番号0001　,　19500

明細のサマリーを乗せた上記のごくごく一般的な
ヘッダと明細のsqlに
上記質問のカラムＡ（2999/12/31を除く最大日付）を追加でselect対象に入れたい
ということでした。

[ms5025]

select　
T_header.伝票番号
,sum(T_detail.金額）
from　T_header
left join T_detail
on
T_header.伝票番号=T_detail.伝票番号

でしたすいません。

[ms5025]

修正ありがとうございます。
あまりなじみないsqlでちょっと考えてしまったのですが

これって結果として

伝票番号0001　,　19500園（明細全ての合計金額）,　2011/11/05（2999/12/31をのぞく最大日）
という一行がちゃんと返ってくるんでしょうか・・？

どこで金額のサマリーが行われてるのかわからなくて・・。
明細の羅列になりません？