clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1130722 fiddles created (16586 in the last week).

create table "INVAB" ( 編號 float, 品號 varchar(255), 庫別 varchar(255), 出貨日 varchar(255) );
 hidden batch(es)


insert into "INVAB" values (1,'1101','T1','20180801'), (2,'1102','T2','20180701'), (3,'1103','T3','20180301'), (4,'1103','T3','20180601'), (5,'1104','T4','20180102'), (6,'1104','T4','20180201'), (7,'1104','T3','20180801'), (8,'1104','T4','20181005');
8 rows affected
 hidden batch(es)


select * from "INVAB"
編號 品號 庫別 出貨日
1 1101 T1 20180801
2 1102 T2 20180701
3 1103 T3 20180301
4 1103 T3 20180601
5 1104 T4 20180102
6 1104 T4 20180201
7 1104 T3 20180801
8 1104 T4 20181005
 hidden batch(es)


select b.* from( select A.*, DATEADD(month,6,CONVERT(date,最大出貨日)) "最大出貨日+半年", CONVERT(date,'20190101') "設定日期", CASE WHEN DATEADD(month,6,CONVERT(date,最大出貨日)) < CONVERT(date,'20190101') THEN 'O' ELSE 'X' END tag from ( select *, MAX(出貨日) over (PARTITION BY 品號) 最大出貨日 from "INVAB" ) A ) B where tag = 'O'
編號 品號 庫別 出貨日 最大出貨日 最大出貨日+半年 設定日期 tag
3 1103 T3 20180301 20180601 01/12/2018 00:00:00 01/01/2019 00:00:00 O
4 1103 T3 20180601 20180601 01/12/2018 00:00:00 01/01/2019 00:00:00 O
 hidden batch(es)