34,588
社区成员
发帖
与我相关
我的任务
分享
if object_id('sp_MonthReport_Calculate','u') is not null
drop proc sp_MonthReport_Calculate
go
create proc sp_MonthReport_Calculate
@st DATETIME,
@ed DATETIME,
@RecorderID VARCHAR(5)
as
declare @days int,@i int
declare @stepDate datetime
declare @t table(Temperature float,MaxTemperature float,MaxTemperatureTime datetime,RecorderID varchar(5),nDate varchar(10))
set @days=datediff(dd,@st,@ed)
set @i=0
while @i <@days
begin
set @stepDate = dateadd(HH,24,@st)
insert into @t
SELECT Temperature=AVG(EnvirTemp)
,MaxTemperature=MAX(MaxTemperature)
,MaxTemperatureTime=(select min(MaxTemperatureTime) from MeteTable where (nDate between @st And @stepDate) And RecorderID=@RecorderID and MaxTemperature=max(t.MaxTemperature))
,RecorderID=@RecorderID
,nDate=CONVERT(VARCHAR(10),@stepDate,120)
FROM MeteTable t
WHERE RecorderID=@RecorderID and nDate between @st AND @stepDate
set @i=@i+1
set @st=dateadd(hh,24,@st)
print CONVERT(VARCHAR(10),@st,120)
end
select * from @t
exec sp_MonthReport_Calculate '2008-9-30 20:00','2008-10-31 20:00','08342'
declare @arg datetime set @arg = '2008-3-12'
set nocount on
declare @mnBgn datetime , @mnEnd datetime
declare @tb table (id int identity(0,1), dt datetime)
set @mnBgn = dateadd(month,datediff(month,0,@arg),0)
set @mnEnd = dateadd(month,1,@mnBgn)
insert into @tb select top 31 @mnBgn from sysobjects
update @tb set dt = dt+id
select dateadd(hour,-4,dt), dateadd(hour,20,dt) from @tb where dt<=@mnEnd
-- ,
-- 2008-02-29 20:00:00.000,2008-03-01 20:00:00.000
-- 2008-03-01 20:00:00.000,2008-03-02 20:00:00.000
-- 2008-03-02 20:00:00.000,2008-03-03 20:00:00.000
-- 2008-03-03 20:00:00.000,2008-03-04 20:00:00.000
-- ...
-- 2008-03-29 20:00:00.000,2008-03-30 20:00:00.000
-- 2008-03-30 20:00:00.000,2008-03-31 20:00:00.000
if object_id('sp_MonthReport_Calculate','u') is not null
drop proc sp_MonthReport_Calculate
go
create proc sp_MonthReport_Calculate
@st DATETIME,
@ed DATETIME,
@RecorderID VARCHAR(5)
as
declare @days int,@i int
declare @t table(Temperature float,MaxTemperature float,MaxTemperatureTime datetime,RecorderID int,nDate varchar(10))
set @days=datediff(dd,@st,@ed)
set @i=0
while @i <@days
begin
set @st=dateadd(dd,1,@st)
set @ed=dateadd(dd,1,@st)
insert into @t
SELECT Temperature=AVG(EnvirTemp)
,MaxTemperature=MAX(MaxTemperature)
,MaxTemperatureTime=(select min(MaxTemperatureTime) from MeteTable where (nDate between @st And @ed) And RecorderID=@RecorderID and MaxTemperature=max(t.MaxTemperature))
,RecorderID=@RecorderID
,nDate=CONVERT(VARCHAR(10),@ed,120)
FROM MeteTable t
WHERE RecorderID=@RecorderID and nDate between @st AND @ed
set @i=@i+1
end
select * from @t
之前没太仔细看.运行就是这样了,给任意两个时间:
exec sp_MonthReport_Calculate '2008-10-16 20:00','2008-10-17 20:00','08295'
是一天
exec sp_MonthReport_Calculate '2008-10-16 20:00','2008-10-18 20:00','08295'
两天
exec sp_MonthReport_Calculate '2008-10-16 20:00','2008-11-16 20:00','08295'
一个月的结果
exec sp_MonthReport_Calculate '2008-10-16 20:00','2008-10-17 20:00','08295'
是一天
exec sp_MonthReport_Calculate '2008-10-16 20:00','2008-10-18 20:00','08295'
这样不是两天吗?
exec sp_MonthReport_Calculate '2008-10-16 20:00','2008-11-16 20:00','08295'
这么不是一个月的结果吗?
declare @mnBgn datetime, @mnEnd datetime
set @mnBgn = dateadd(hour,-4,dateadd(month,datediff(month,0,@argdate),0))
set @mnEnd = dateadd(month,1,@mnBgn)
...