上表是原始sample data, 下表是query结果，其中policy_no is unique.
code stands for different meaning,
/*
A = 'Change'
C = 'Cancel'
N = 'New'
R = 'Renewal'
*/
请问该如何写这个SQL query, 谢谢。
create table PolicyTable
(
Policy_No varchar(50),
Code char(1),
Start_Date Date,
End_Date Date,
Amount decimal(10,2)
)
insert into PolicyTable values
('A000413137', 'N', '2013-12-03', '2014-12-03', 118)
insert into PolicyTable values
('A000413137', 'A', '2013-12-03', '2014-12-03', 28)
insert into PolicyTable values
('A000413137', 'R', '2014-12-03', '2015-12-03', 12)
insert into PolicyTable values
('A000413130', 'R', '2015-01-01', '2016-01-01', 26)
insert into PolicyTable values
('A000413130', 'A', '2014-01-01', '2015-01-01', 12)
insert into PolicyTable values
('A000413130', 'N', '2014-01-01', '2015-01-01', 33)
insert into PolicyTable values
('A000413133', 'C', '2014-01-01', '2015-01-01', 10)
insert into PolicyTable values
('A000413133', 'N', '2014-01-01', '2015-01-01', 10)
select * from PolicyTable
/*
A = 'Change'
C = 'Cancel'
N = 'New'
R = 'Renewal'
*/
------解决思路----------------------
不知这么理解对么：
start_date=同一组(policy_NO相等的数据)数据中的MIN(start_date)，
end_date=同一组数据中的MAX(end_date)。
=========================================================
如果这样理解正确的话，那下表的plocy_NO=A000413133 的数据的end_date是不是应该等于2015-01-01？
------解决思路----------------------
SELECT T1.Policy_No应该是这个意思
,T1.Start_Date
,ISNULL(T2.Start_Date,T1.End_Date)End_Date
FROM(
SELECT Policy_No,MIN(Start_Date)Start_Date,MAX(End_Date)End_Date
FROM PolicyTable
WHERE Code IN('N','A','R')
GROUP BY Policy_No
)T1
LEFT JOIN PolicyTable T2 ON T1.Policy_No=T2.Policy_No AND T2.Code='C'
------解决思路----------------------
假如#5的数据在C之后再R，不能简单地用MIN、MAX算的。