求帮写一个存储过程,急,~~,在线等.
表:t1
services 港口 到港 离港 SI截关 CY截关 TT(所需天数)
c1 香港 周日 周日 周五 周六
c1 他科马 周五 周六 12
c1 奥克兰 周一 周二 15
c1 檀香山 周六 周日 20
c1 关岛 周二 周三 30
表:t2
Services 年份 第几周
c1 2007 34
c1 2008 34
要得到这样的结果:
香港 到港时间
services 年份 第几周 到港 离港 SI截关 CY截关 他科马 奥克兰 檀香山 关岛
c1 2007 34 8-19 8-19 8-24 8-25 8-31 9-3 9-8 9-13
c1 2008 34 ? ?..............................................
..
..
也就是说:在t1中已经有了周几和TT(例如:从香港到他科马需要12天),
在t2中有对应的services,然后通过年份和给出的第几周,来得到那样的结果.
[解决办法]
这里最大的问题是如何确定2007年第34周的星期几那天的日期,
以他科马为例:
services 年份 第几周 到港 离港 SI截关 CY截关 他科马 奥克兰 檀香山 关岛
c1 2007 34 8-19 8-19 8-24 8-25 8-31 9-3 9-8 9-13
----------------------------------------
到港和离港日期都是8-19,周日.但是2007年第34周的周日那天并不是2007-08-19,而是2007-09-02,计算方法为:
set datefirst 1
select dateadd(day,7-(datepart(weekday,dateadd(week,34, '2007-01-01 '))),dateadd(week,34, '2007-01-01 '))
所以,楼主必须确定日期与周之间的计算规则.
[解决办法]
--创建表
create table t1(
services varchar(20) not null,
港口 varchar(20),
到港 varchar(20),
离港 varchar(20),
SI截关 varchar(20),
CY截关 varchar(20),
TT int
)
go
create table t2(
Services varchar(20) not null,
年份 int,
第几周 int
)
go
--插入数据
insert into t1 select 'c1 ', '香港 ', '周日 ', '周日 ', '周五 ', '周六 ',0
union select 'c1 ', '他科马 ', '周五 ', '周六 ', ' ', ' ',12
union select 'c1 ', '奥克兰 ', '周一 ', '周二 ', ' ', ' ',15
union select 'c1 ', '檀香山 ', '周六 ', '周日 ', ' ', ' ',20
union select 'c1 ', '关岛 ', '周二 ', '周三 ', ' ', ' ',30
go
insert into t2 select 'c1 ',2007,34
union select 'c1 ',2008,34
go
--查询
create function getD(@d datetime)
returns varchar(20)
as
begin
return substring(convert(char(10),@d),7,4)+ '- ' +substring(convert(char(10),@d),1,2) + '- '+replace(substring(convert(char(10),@d),4,2), ' ', '0 ')
end
go
create function ggg(@s varchar(20))
returns int
as
begin
if @s= '周一 '
return 0
if @s= '周二 '
return 1
if @s= '周三 '
return 2
if @s= '周四 '
return 3
if @s= '周五 '
return 4
if @s= '周六 '
return 5
if @s= '周日 '
return 6
return -1
end
go
create procedure ge
@Services varchar(20),
@y int
as
begin
----设置星期一作为每周的第一天
set datefirst 1--
declare @sql nvarchar(1000)--查询语句
declare @s nvarchar(1000)--
declare @w int --第几周
declare @i int --周几
declare @d datetime
declare @dd datetime--出发当天
select @w=第几周 from t2 where Services=@Services and 年份=@y
set @s=Convert(varchar(4),@y)+ '-01-01 '
set @d=DATEADD(DAY, (@w-1) * 7, Convert(Datetime,@s))
declare @si varchar(20)
declare @sl varchar(20)
declare @cy varchar(20)
select @sl=到港,@s=离港,@si=SI截关,@cy=CY截关 from t1 where Services=@Services and TT=0
set @i=dbo.ggg(@s)
set @dd=DateAdd(DAY,@i,@d)
set @s=dbo.getD(@dd)
set @sql=N 'select services, '
set @sql=@sql+ Convert(varchar(4),@y)+ ' 年份, '+Convert(varchar(4),@w)+ ' 第几周, '
set @sl=dbo.getD(DateAdd(DAY,dbo.ggg(@sl),@d))
set @sql=@sql+ ' ' ' '+ @sl+ ' ' ' 到港, '
set @sql=@sql+ ' ' ' '+ @S+ ' ' ' 离港, '
if dbo.ggg(@SI) <dbo.ggg(@CY)
begin
set @i=7+dbo.ggg(@SI)
set @sql=@sql+ ' ' ' '+ dbo.getD(DateAdd(DAY,@i,@dd))+ ' ' ' SI截关, '
set @i=7+dbo.ggg(@CY)
set @sql=@sql+ ' ' ' '+ dbo.getD(DateAdd(DAY,@i,@dd))+ ' ' ' CY截关 '
end
else
begin
set @i=dbo.ggg(@SI)
set @sql=@sql+ ' ' ' '+ dbo.getD(DateAdd(DAY,@i,@dd))+ ' ' ' SI截关, '
set @i=dbo.ggg(@CY)
set @sql=@sql+ ' ' ' '+ dbo.getD(DateAdd(DAY,@i,@dd))+ ' ' ' CY截关 '
end
select @sql=@sql + ',max(case 港口 when ' ' '+港口 + ' ' 'then dbo.getD(DATEADD(DAY, TT,Convert(datetime, ' ' '+@s+ ' ' '))) end )[ ' +港口 + '] '
from (select TOP 100 Percent 港口 from t1 Group By 港口,TT HAVING TT> 0 order by TT asc) as a
select @sql=@sql + ' from t1 group by services '
exec (@sql)
end
go
---运行
exec ge 'c1 ',2007
--删除
drop table t1
drop table t2
drop procedure ge
drop function ggg
drop function getD
