根据业务需要,在水晶报表中把数据库的多条记录显示为整合为一条。即把同一ID号的出件接收时间整合为一条，且标识出是第几条的接收时间。
数据库中的数据样式如下：
SAID ReceivedDate
000104 2006-03-01
000104 2006-03-04
000104 2006-03-07
000104 2006-03-08
000105 2006-08-09
-----------水晶报表中显示的样式为：
SAID 1st Received Date 2nd Received Date 3rd Received Date 4th Received Date
000104 2006-03-01 2006-03-04 2006-03-07 2006-03-08
000105 2006-08-09
---------------------SQL语句为：
Code
select [SA].[SAID]
,MAX(case when xh = 1 then SentDate end) as EmailNotice
,MAX(case when xh = 2 then SentDate end) as FirstReminder
,MAX(case when xh = 3 then SentDate end) as SecondReminder
,MAX(case when xh = 4 then [SA].[LastReminderDate] end) as AlertToQCS
,[SA].[VendorName],[SA].[FactoryName]
from(select *,xh=(select xh = count(*) from [#a] where a.ReferenceID = ReferenceID and a.xid>=xid) from [#a] a) b , [SA]
WHERE
[SA].[SAID]=b.ReferenceID AND
(ISNULL(@CustomerID,'')=''OR [SA].CustomerID=@CustomerID) AND
(ISNULL(@VendorID,'')='' OR [SA].VendorID=@VendorID) AND
(ISNULL(@FactoryID,'')='' OR [SA].FactoryID=@FactoryID) AND
(ISNULL(@BeginDate,'1999-1-1')='1999-1-1' OR [SA].FirstReminderDate>= @BeginDate) AND
(ISNULL(@EndDate,'2999-1-1')='2999-1-1' OR [SA].FirstReminderDate<=@EndDate) AND
(ISNULL(@SAID1,'')='' OR CAST([SA].SAID AS int) >=CAST(@SAID1 AS INT)) AND
(ISNULL(@SAID2,'')='' OR CAST(SA.SAID AS INT)<=CAST(@SAID2 AS INT)) AND
[SA].Confirmed=1 AND
[SA].Booked=0 AND
SA.Deleted=0
AND SA.CompanyID=@CompanyID AND
SA.DepartmentID=@DepartmentID AND SA.[DivisionID]=@DivisionID
GROUP BY ReferenceID,[SA].[VendorName],[SA].[FactoryName],[SA].[SAID]
drop table #a
