求一个存储过程~~
services week POL POD ETADate Portid tt
b2 35 Shanghai 1 0
b2 35Yantian 2007-09-02 2 2
b2 35HK 2007-09-03 3 3
b2 35Busan 2007-09-05 4 5
b2 35Balboa 2007-09-23 5 23
b2 35Miami 2007-09-27 6 27
b2 35Savannah 2007-09-28 7 28
b2 35Charleston 2007-10-01 8 31
b2 35Newark 2007-10-03 9 33
..
..
b2 36
... ..
b3 36
...
要得到:
services week POL Balboa Miami Savannah Charleston Newark
b2 35 Shanghai 09-23 09-27 09-28 10-01 10-03
b2 35 Yantian 09-25 09-29 09-30 10-03 10-05
b2 35 HK 09-28 09-02 09-03 10-06 10-08
b2 35 Busan .. ..
也就是说以 services和 week为条件分别计算出POL到POD的时间,
例如:Shanghai的tt为0,所以他的时间就是POD对应的ETADate的时间加上0,
Yantian的tt为2,所以他的时间就是POD对应的ETADate的时间加上2,
依次类推..
请大哥帮忙~~
[解决办法]
/*如果POD列不固定,则需要用动态语句,改用下面的存储过程*/
---创建存储过程
Create Procedure Pro_Test
@services Varchar(8),
@week int
As
Set NoCount On
Begin
Declare @sql Varchar(8000)
Set @sql= ' '
Select @sql=@sql+ ',Max(Case B.POD When ' ' '+B.POD+ ' ' '
Then Right(Convert(Varchar(10),Dateadd(dd,A.tt,B.ETADate),120),5) Else ' ' ' ' End) As [ '+B.POD+ '] '
From 表 As B Where B.POD Is Not Null Group By B.POD
Exec( 'Select A.services,A.[week],A.POL '+@sql+ 'From 表 As A Left Join 表 As B
On A.services= ' ' '+@services+ ' ' ' And A.[week]= '+@week+ '
Where A.POL Is Not Null And B.POD Is Not Null
Group By A.services,A.[week],A.POL ')
End
Go
---调用存储过程
Exec Pro_Test 'b2 ',35
