是气象部门的一个降雨评分程序,领导突然发疯明天就要要这个东西,压给小弟,失败啊,解决后可再赠送500分作为酬谢,以姓氏作担保,决不食言,请大家帮我写一下啊……共有3个表,表A是实况值,表B是预报值,表C是每天评分的结果,几个表的结构如下:表A:实况值
SID date value
50136 20070212 31
50136 20070213 20
50136 20070214 15
……
表A说明:SID字段是台站编号,date是日期,value是降水量。表B
SID date 1 2 3 4
50136 20070211 0 0 1 2
50136 20070212 0 1 2 3
……
表B说明:SID字段是台站编号,date是预报日期,字段1、2、3、4分别代表时间步长,1代表20070211这天的后一天,即报20070212日降水量级是0;2代表后两天,即20070213,以此类推;降水量级表示如果是0就是无降水,如果是小于等于10就是1,如果是小于等于20就是2,以此类推,表A中12、13、14日的降水量所对应的量级分别是4、2、2。表C
SID date Avalue Bvalue Span
50136 20070211 4 0 1
50136 20070211 2 0 2
……
表C说明:SID字段是台站编号,date是预报日期,这两个跟前面一样的意思,Avalue和Bvalue分别是表A和表B中相对应时间步长(Span字段)下的降水量级。如第一行表示50136这个台站在20070211日报的明天的(Span=1)量级是0,但实况是4,报的后天(Span=2)的量级是0,实况是2,以此类推。要求做出来一个每天可以自动运行的程序,最好是利用SQL自己的脚本或者C#编写的程序,每天运行后把结果更新到表C中。要注意的是台站不仅只有50136,大概有100多个,每一个台站都要这么做,即每个台站每天都要做它后面四天的预报结果的评分。谢谢大家了啊!
SID date value
50136 20070212 31
50136 20070213 20
50136 20070214 15
……
表A说明:SID字段是台站编号,date是日期,value是降水量。表B
SID date 1 2 3 4
50136 20070211 0 0 1 2
50136 20070212 0 1 2 3
……
表B说明:SID字段是台站编号,date是预报日期,字段1、2、3、4分别代表时间步长,1代表20070211这天的后一天,即报20070212日降水量级是0;2代表后两天,即20070213,以此类推;降水量级表示如果是0就是无降水,如果是小于等于10就是1,如果是小于等于20就是2,以此类推,表A中12、13、14日的降水量所对应的量级分别是4、2、2。表C
SID date Avalue Bvalue Span
50136 20070211 4 0 1
50136 20070211 2 0 2
……
表C说明:SID字段是台站编号,date是预报日期,这两个跟前面一样的意思,Avalue和Bvalue分别是表A和表B中相对应时间步长(Span字段)下的降水量级。如第一行表示50136这个台站在20070211日报的明天的(Span=1)量级是0,但实况是4,报的后天(Span=2)的量级是0,实况是2,以此类推。要求做出来一个每天可以自动运行的程序,最好是利用SQL自己的脚本或者C#编写的程序,每天运行后把结果更新到表C中。要注意的是台站不仅只有50136,大概有100多个,每一个台站都要这么做,即每个台站每天都要做它后面四天的预报结果的评分。谢谢大家了啊!
select sid,
date,
(select case when value between 1 and 10 then 1
when value between 10 and 20 then 2
when value between 20 and 30 then 3
when value between 30 and 40 then 4 else 0 end
from 表A a
where cast(a.date as datetime)=dateadd(day,1,cast(b.date as datetime)) and a.sid=b.sid),
[1],
1
from 表B b
union all
select sid,
date,
(select case when value between 1 and 10 then 1
when value between 10 and 20 then 2
when value between 20 and 30 then 3
when value between 30 and 40 then 4 else 0 end
from 表A a
where cast(a.date as datetime)=dateadd(day,2,cast(b.date as datetime)) and a.sid=b.sid),
[2],
2
from 表B b
union all
select sid,
date,
(select case when value between 1 and 10 then 1
when value between 10 and 20 then 2
when value between 20 and 30 then 3
when value between 30 and 40 then 4 else 0 end
from 表A a
where cast(a.date as datetime)=dateadd(day,3,cast(b.date as datetime)) and a.sid=b.sid),
[3],
3
from 表B b
union all
select sid,
date,
(select case when value between 1 and 10 then 1
when value between 10 and 20 then 2
when value between 20 and 30 then 3
when value between 30 and 40 then 4 else 0 end
from 表A a
where cast(a.date as datetime)=dateadd(day,4,cast(b.date as datetime)) and a.sid=b.sid),
[4],
4
from 表B b
create table 实况值(SID int,date datetime,value int)
create table 预测值(SID int,date datetime,[1] int,[2] int,[3] int,[4] int)
--插入测试数据
insert 实况值(SID,date,value)
select '50136','20070212','31' union all
select '50136','20070213','20' union all
select '50136','20070214','15'
insert 预测值(SID,date,[1],[2],[3],[4])
select '50136','20070211','0','0','1','2' union all
select '50136','20070212','0','1','2','3'
create table 分析表(SID int,date datetime,Avalue int,Bvalue int,Span int)--求解过程
insert 分析表
select b.sid,b.date,(a.value+9)/10 as Avalue,b.value as Bvalue,b.span
from 实况值 a
join(
select SID,date,[1] as value,1 as span from 预测值 union all
select SID,date,[2] as value,2 as span from 预测值 union all
select SID,date,[3] as value,3 as span from 预测值 union all
select SID,date,[4] as value,4 as span from 预测值
) b on a.date = b.date + span and a.sid = b.sid
order by b.date,b.spanselect * from 分析表
--删除测试环境
drop table 实况值,预测值,分析表/*--测试结果
SID date Avalue Bvalue Span
----------- ------------------------------------------------------ ----------- ----------- -----------
50136 2007-02-11 00:00:00.000 4 0 1
50136 2007-02-11 00:00:00.000 2 0 2
50136 2007-02-11 00:00:00.000 2 1 3
50136 2007-02-12 00:00:00.000 2 0 1
50136 2007-02-12 00:00:00.000 2 1 2(所影响的行数为 5 行)
*/
SID date Avalue Bvalue Span
50136 2007-02-11 00:00:00.000 4 0 1
50136 2007-02-11 00:00:00.000 2 0 2
50136 2007-02-11 00:00:00.000 2 1 3
50136 2007-02-12 00:00:00.000 2 0 1
50136 2007-02-12 00:00:00.000 2 1 2
create table 表a(sid int,date varchar(20),value int)
insert into 表a
select 50136,'20070212',31
union all select 50136,'20070213',20
union all select 50136,'20070214',15create table 表b(sid int,date varchar(20),[1] int,[2] int,[3] int,[4] int)
insert into 表b
select 50136,'20070211',0,0,1,2
union all select 50136,'20070212',0,1,2,3create table 表C(sid int,date varchar(20),avalue int,bvalue int,span int)insert into 表C(sid,date,avalue,bvalue,span)
select sid,
date,
(select ceiling(value/10.0)
from 表A a
where cast(a.date as datetime)=dateadd(day,1,cast(b.date as datetime)) and a.sid=b.sid),
[1],
1
from 表B b
union all
select sid,
date,
(select ceiling(value/10.0)
from 表A a
where cast(a.date as datetime)=dateadd(day,2,cast(b.date as datetime)) and a.sid=b.sid),
[2],
2
from 表B b
union all
select sid,
date,
(select ceiling(value/10.0)
from 表A a
where cast(a.date as datetime)=dateadd(day,3,cast(b.date as datetime)) and a.sid=b.sid),
[3],
3
from 表B b
union all
select sid,
date,
(select ceiling(value/10.0)
from 表A a
where cast(a.date as datetime)=dateadd(day,4,cast(b.date as datetime)) and a.sid=b.sid),
[4],
4
from 表B bselect * from 表c order by sid,date
/*
sid date avalue bvalue span
----------- -------------------- ----------- ----------- -----------
50136 20070211 4 0 1
50136 20070211 2 0 2
50136 20070211 2 1 3
50136 20070211 NULL 2 4
50136 20070212 NULL 3 4
50136 20070212 NULL 2 3
50136 20070212 2 1 2
50136 20070212 2 0 1(所影响的行数为 8 行)
*/
from 表A a
where cast(a.date as datetime)=dateadd(day,1,cast(b.date as datetime)) and a.sid=b.sid),
[1],
1
这里的[1]代表什么呢?后面的1是什么意思呢?
from 表A a
where cast(a.date as datetime)=dateadd(day,1,cast(b.date as datetime)) and a.sid=b.sid),
[1],
1
这里的[1]代表什么呢?后面的1是什么意思呢?
----------------------------------------
[1],[2],[3],[4]是字段名称,因为不能用数字做字段名称,所以要用[]括起来
后面的1,因为你表B不是有1,2,3,4四个步长吗,所以在向C表插数据的就是固定值1,2,3,4
我已超過了30次了
故步驟貼在這裡:第一步:創建存儲過程,代碼在樓上2007-03-24 15:23:15
第二步:在查詢分析裡執行該存儲過程,代碼為:exec SP_qyRainJG08
第三步:創建作業,代碼在樓上2007-03-24 14:24:20
以後SQLserver每天23:30執行一次,預測及實況結果都會存儲到表qyRainJG08中到時查看qyRainJG08表就知道所有預測及實況的結果了
还有刚才说的查询结果是反过来的,是不是把代码里面的left换成right就可以了呢?
江涛兄,在企业管理器中看不到hbqxsql也可以创建存储过程吗?我看帮助创建存储过程需要在企业管理器里选中那个数据库才可以的(我们说得所有的表都在hbqxsql里面)。后面的两步还没有执行,因为直接执行您老的第一步在查询分析里面,一眨眼就执行完了,但是并没有查询结果在jg08的表里面,不知道是为什么?
insert [qyrainjg08]([iiiii],[DateDay],[ybspan],[yblj],[sklj])
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'024' as ybspan,a.[024] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,1,b.dateday),120),'-','')
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'048' as ybspan,a.[048] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,2,b.dateday),120),'-','')
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'072' as ybspan,a.[072] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,3,b.dateday),120),'-','')
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'096' as ybspan,a.[096] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,4,b.dateday),120),'-','')
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'120' as ybspan,a.[120] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,5,b.dateday),120),'-','')
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'144' as ybspan,a.[144] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,6,b.dateday),120),'-','')
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'168' as ybspan,a.[168] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,7,b.dateday),120),'-','')但是写下面的代码就不能将结果写进jg08表里面,是因为什么啊?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Rocky
-- Create date: 2007.03.24
-- Description:將指定日期之后的預測和實況結果插入到結果表[qyrainjg08]
-- =============================================
create PROCEDURE dbo.SP_qyRainJG08
@date smalldatetime=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;IF @date IS NULL
SET @date = GETDATE()DECLARE @StrDate varchar(10)SET @StrDate=replace(convert(varchar(10),@date,120),'-','') -- Insert statements for procedure here
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'024' as ybspan,a.[024] as yblj,b.r0808 as sklj into #temp
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,1,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'048' as ybspan,a.[048] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,2,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'072' as ybspan,a.[072] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,3,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'096' as ybspan,a.[096] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,4,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'120' as ybspan,a.[120] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,5,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'144' as ybspan,a.[144] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,6,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'168' as ybspan,a.[168] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,7,a.dateday),120),'-','')
WHERE a.dateday>=@StrDateinsert [qyrainjg08]([iiiii],[DateDay],[ybspan],[yblj],[sklj])
SELECT * FROM #temp A (nolock) WHERE NOT EXISTS (SELECT 1 FROM [qyrainjg08] (nolock) where [iiiii]=A.[iiiii] and dateday=A.dateday AND [ybspan]=A.[ybspan])truncate table #temp
drop table #temp
END
GO
SELECT [iiiii],[DateDay],[ybspan],[yblj],[sklj]
,实况的量级=case
when [sklj] is null then '實況雨量未輸入'
when [sklj]=0 then '0毫米'
when [sklj]=1.0 then '0.01-9.99毫米'
when [sklj]=2.0 then '10-24.99毫米'
when [sklj]=3.0 then '25-49.99毫米'
when [sklj]=4.0 then '50-99.99毫米'
when [sklj]=5.0 then '100毫米以上'
else '其他量級'
end
FROM [dbo].[qyrainjg08]
寫成存儲過程後,交給SQLserver了,讓它每天執行一次了(用job實現)
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'024' as ybspan,a.[024] as yblj,b.r0808 as sklj into #temp
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,1,a.dateday),120),'-','')union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'048' as ybspan,a.[048] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,2,a.dateday),120),'-','')union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'072' as ybspan,a.[072] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,3,a.dateday),120),'-','')union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'096' as ybspan,a.[096] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,4,a.dateday),120),'-','')union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'120' as ybspan,a.[120] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,5,a.dateday),120),'-','')union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'144' as ybspan,a.[144] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,6,a.dateday),120),'-','')union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'168' as ybspan,a.[168] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,7,a.dateday),120),'-','')
insert [qyrainjg08]([iiiii],[DateDay],[ybspan],[yblj],[sklj])
SELECT [iiiii],[DateDay],[ybspan]
,case
when [yblj]=0 then '0.0'
when [yblj]>='0.01' and [yblj]<='9.99' then '1.0'
when [yblj]>='10' and [yblj]<='24.99' then '2.0'
when [yblj]>='25' and [yblj]<='49.99' then '3.0'
when [yblj]>='50' and [yblj]<='99.99' then '4.0'
when [yblj]>='100' then '5.0'
when [yblj] is null then null
else '999.99'
end as [yblj]
,case
when [sklj]=0 then '0.0'
when [sklj]>='0.01' and [sklj]<='9.99' then '1.0'
when [sklj]>='10' and [sklj]<='24.99' then '2.0'
when [sklj]>='25' and [sklj]<='49.99' then '3.0'
when [sklj]>='50' and [sklj]<='99.99' then '4.0'
when [sklj]>='100' then '5.0'
when [sklj] is null then null
else '999.99'
end as sklj
FROM #temp A (nolock) WHERE NOT EXISTS (SELECT 1 FROM [qyrainjg08] (nolock) where [iiiii]=A.[iiiii] and dateday=A.dateday AND [ybspan]=A.[ybspan])truncate table #temp
drop table #temp
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_qyRainJG08]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_qyRainJG08]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO-- =============================================
-- Author:Rocky
-- Create date: 2007.03.24
-- Description:將指定日期之后的預測和實況結果插入到結果表[qyrainjg08]
-- =============================================
CREATE PROCEDURE dbo.SP_qyRainJG08
@date smalldatetime=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;IF @date IS NULL
SET @date = GETDATE()DECLARE @StrDate varchar(10)SET @StrDate=replace(convert(varchar(10),@date,120),'-','') -- Insert statements for procedure here
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'024' as ybspan,a.[024] as yblj,b.r0808 as sklj into #temp
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,1,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'048' as ybspan,a.[048] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,2,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'072' as ybspan,a.[072] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,3,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'096' as ybspan,a.[096] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,4,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'120' as ybspan,a.[120] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,5,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'144' as ybspan,a.[144] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,6,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'168' as ybspan,a.[168] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,7,a.dateday),120),'-','')
WHERE a.dateday>=@StrDateinsert [qyrainjg08]([iiiii],[DateDay],[ybspan],[yblj],[sklj])
SELECT [iiiii],[DateDay],[ybspan]
,case
when [yblj]=0 then '0.0'
when [yblj]>='0.01' and [yblj]<='9.99' then '1.0'
when [yblj]>='10' and [yblj]<='24.99' then '2.0'
when [yblj]>='25' and [yblj]<='49.99' then '3.0'
when [yblj]>='50' and [yblj]<='99.99' then '4.0'
when [yblj]>='100' then '5.0'
when [yblj] is null then null
else '999.99'
end as [yblj]
,case
when [sklj]=0 then '0.0'
when [sklj]>='0.01' and [sklj]<='9.99' then '1.0'
when [sklj]>='10' and [sklj]<='24.99' then '2.0'
when [sklj]>='25' and [sklj]<='49.99' then '3.0'
when [sklj]>='50' and [sklj]<='99.99' then '4.0'
when [sklj]>='100' then '5.0'
when [sklj] is null then null
else '999.99'
end as sklj
FROM #temp A (nolock) WHERE NOT EXISTS (SELECT 1 FROM [qyrainjg08] (nolock) where [iiiii]=A.[iiiii] and dateday=A.dateday AND [ybspan]=A.[ybspan])truncate table #temp
drop table #temp
ENDGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--最後一步:創建作業
USE [msdb]
GO
/****** 物件: Job [Job_qyRainJG08] 指令碼日期: 03/24/2007 14:14:13 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** 物件: JobCategory [[Uncategorized (Local)]]] 指令碼日期: 03/24/2007 14:14:13 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Job_qyRainJG08',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'EXEC DBO.SP_qyRainJG08',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** 物件: Step [08] 指令碼日期: 03/24/2007 14:14:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'08',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC DBO.SP_qyRainJG08',
@database_name=N'hbqxsql',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'everyday',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20070324,
@active_end_date=99991231,
@active_start_time=233000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
truncate table [qyrainyb08]
服务器: 消息 14261,级别 16,状态 1,过程 sp_verify_job,行 67
指定的 @name('Job_qyRainJG08')已存在。
是什么原因呢?
已经很晚了江涛兄,您老先回去休息吧还是,麻烦了您老一天,很过意不去,能有一个初步的jg08表就好,可以先应付一下了……