问题没弄清楚，再来求解，关于月报的问题

2008-10-22 04:54:08 229 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

软件项目周报、月报模板该资源包括软件项目的周报，月报模板，可以供相关IT人事借鉴。立即下载

就是求一个月报，字段如下
ID EnvirTemp MaxTemperature MaxTemperatureTime RecorderID nDate
1 1.2 2.2 2008-10-20 13:11 08295 2008-10-20 13:12
1 1.2 2.2 2008-10-21 13:11 08295 2008-10-21 13:12
1 1.3 3.2 2008-10-21 13:11 08295 2008-10-21 13:12
1 1.4 2.2 2008-10-24 13:11 08295 2008-10-24 13:12
1 1.2 7.2 2008-10-27 13:11 08295 2008-10-27 13:12
1 1.4 1.2 2008-10-28 13:11 08295 2008-10-28 13:12
1 1.2 3.2 2008-10-28 13:11 08295 2008-10-28 13:12

下面的存储过程通过输入起始的日期（@st）和结束日期(@ed)和编号(RecorderID='08295')，可以满足求一天的数据了，但问题是一个月有30天，那么我的程序只能求一天的，这样还达不到要求，我想求从一个月开始到一个月结束这段时间的值，请兄弟帮我改一下程序，既得可以到到一个月每一天的值。如果查询不到数据，就只插入nDate的值和RecorderID的值其余为空。

我本想用笨方法while还循环得到这30天的值，但是一循环又无法变成一个表了，我想返回一个表。

注：每一天指的是前一天的20点到今天的20点算做一天，过了今天的20点算下一天。
比如：大于 2008-10-20 20:00 并且小于 2008-10-21 20:00 这样才算做一天

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

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

go

exec sp_MonthReport_Calculate '2008-10-16 20:00','2008-10-17 20:00','08295'

...全文

项目开发中的周报/月报模板一个简单的项目开发管理过程中的周报/月报模板。用户对工作进展进行汇报，同时总结下一步工作计划。立即下载

zoujiaming

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

zoujiaming 2008-10-23

打赏
举报

Temperature MaxTemperature MaxTemperatureTime RecorderID nDate
NULL NULL NULL 08342 2008-10-01
NULL NULL NULL 08342 2008-10-02
NULL NULL NULL 08342 2008-10-03
NULL NULL NULL 08342 2008-10-04
NULL NULL NULL 08342 2008-10-05
NULL NULL NULL 08342 2008-10-06
NULL NULL NULL 08342 2008-10-07
NULL NULL NULL 08342 2008-10-08
NULL NULL NULL 08342 2008-10-09
NULL NULL NULL 08342 2008-10-10
NULL NULL NULL 08342 2008-10-11
NULL NULL NULL 08342 2008-10-12
NULL NULL NULL 08342 2008-10-13
18.8385620915032 21.2 2008-10-14 14:21:00.000 08342 2008-10-14
20.6792452830189 21.6 2008-10-15 17:39:00.000 08342 2008-10-15
21.2411764705882 21.5 2008-10-15 19:00:00.000 08342 2008-10-16(这里有问题)
20.5965517241379 21.3 2008-10-17 17:05:00.000 08342 2008-10-17
21.8086956521739 22 2008-10-18 17:43:00.000 08342 2008-10-18
21.6643719806765 22 2008-10-19 13:36:00.000 08342 2008-10-19
21.4022435897435 22.5 2008-10-20 15:08:00.000 08342 2008-10-20
21.1988202637061 21.8 2008-10-21 10:25:00.000 08342 2008-10-21
20.8793778801844 21.3 2008-10-21 19:59:00.000 08342 2008-10-22(这里有问题)
NULL NULL NULL 08342 2008-10-23
NULL NULL NULL 08342 2008-10-24
NULL NULL NULL 08342 2008-10-25
NULL NULL NULL 08342 2008-10-26
NULL NULL NULL 08342 2008-10-27
NULL NULL NULL 08342 2008-10-28
NULL NULL NULL 08342 2008-10-29
NULL NULL NULL 08342 2008-10-30
NULL NULL NULL 08342 2008-10-31

不，还是有一点点小问题，显然，如果从20点到第二天的20点，那么不应该包含前一天的20点之前的数据



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'

davidchen0218 2008-10-23

打赏
举报

不会是指时间格式的问题吧.
那就根据需要转换下.
declare @a datetime
set @a=getdate()
select @a
select CONVERT(VARCHAR(23),@a,120)
select substring(convert(varchar,@a,120),1,16)

zoujiaming 2008-10-23

打赏
举报

谢谢 David

davidchen0218 2008-10-23

打赏
举报

这个程序不错，但还是有问题，时间有些乱了
--------
看不出来乱在哪里呢,查询的结果是根据你提供的那个slect语句得到的啊.没看出来问题在哪.如果不满足需求,你对应的修改那个select查询语句就好了吧.

zoujiaming 2008-10-23

打赏
举报

To David

这个程序不错，但还是有问题，时间有些乱了

Temperature MaxTemperature MaxTemperatureTime RecorderID nDate
49.5919500346981 99 2008-01-02 20:23:00.000 1 2008-01-03
48.6988202637058 99 2008-01-04 16:33:00.000 1 2008-01-04
51.2477446217904 99 2008-01-04 22:40:00.000 1 2008-01-05
49.0194309507287 99 2008-01-06 11:56:00.000 1 2008-01-06
48.9243580846634 99 2008-01-07 05:34:00.000 1 2008-01-07
49.3608605135323 99 2008-01-07 21:36:00.000 1 2008-01-08
49.469118667592 99 2008-01-09 02:35:00.000 1 2008-01-09
49.0277585010409 99 2008-01-10 07:58:00.000 1 2008-01-10
48.9639139486468 99 2008-01-10 20:32:00.000 1 2008-01-11
49.8861901457321 99 2008-01-12 02:12:00.000 1 2008-01-12
49.5031228313671 99 2008-01-13 00:31:00.000 1 2008-01-13
49.0506592643997 99 2008-01-14 00:59:00.000 1 2008-01-14
50.116585704372 99 2008-01-14 21:04:00.000 1 2008-01-15
49.9306037473976 99 2008-01-15 21:21:00.000 1 2008-01-16
49.4226231783484 99 2008-01-16 20:09:00.000 1 2008-01-17
50.1693268563498 99 2008-01-18 02:14:00.000 1 2008-01-18
49.1311589174185 99 2008-01-18 23:18:00.000 1 2008-01-19
50.469812630118 99 2008-01-19 20:12:00.000 1 2008-01-20
49.9236641221374 99 2008-01-20 21:26:00.000 1 2008-01-21
49.3032616238723 99 2008-01-22 04:19:00.000 1 2008-01-22
49.6086051353227 99 2008-01-22 20:48:00.000 1 2008-01-23
49.8105482303956 99 2008-01-24 01:32:00.000 1 2008-01-24
48.8105482303956 99 2008-01-25 02:40:00.000 1 2008-01-25
49.3865371269951 99 2008-01-25 21:10:00.000 1 2008-01-26
50.2963219986121 99 2008-01-26 20:19:00.000 1 2008-01-27
49.322692574601 99 2008-01-27 20:53:00.000 1 2008-01-28
49.8070784177654 99 2008-01-28 20:19:00.000 1 2008-01-29
48.6738376127689 99 2008-01-29 20:24:00.000 1 2008-01-30
48.4781401804303 99 2008-01-31 01:54:00.000 1 2008-01-31
49.4177654406662 99 2008-01-31 22:57:00.000 1 2008-02-01
49.1138098542679 99 2008-02-02 01:36:00.000 1 2008-02-02

tim_spac 2008-10-23

打赏
举报

11楼的代码在给定任意一天后返回所在月的日列表

tim_spac 2008-10-23

打赏
举报

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

davidchen0218 2008-10-23

打赏
举报



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' 

一个月的结果

davidchen0218 2008-10-23

打赏
举报

[code=SQL]
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'
一个月的结果

zoujiaming 2008-10-23

打赏
举报

[Quote=引用 7 楼 davidchen0218 的回复:]
SQL code
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'
这么不是一个月的结果吗?
[/Quote]

不是求一个月整，而是一天一条记录，一共30天，购成30条记录算一个月。（月的天数同正常月天数）

davidchen0218 2008-10-23

打赏
举报



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'

这么不是一个月的结果吗?

zoujiaming 2008-10-23

打赏
举报

再次向大家求解，大家帮忙啊

to chinahuyong

你做广告最好远点，这里不欢迎你

.NET快速开发框架 2008-10-22

打赏
举报

经典的东西.学习加工作:
http://blog.csdn.net/chinahuyong

tim_spac 2008-10-22

打赏
举报

-- 假设传入的日期参数为 @argdate, 其值可以是想查询月份内的任意时间点



declare @mnBgn datetime, @mnEnd datetime

set @mnBgn = dateadd(hour,-4,dateadd(month,datediff(month,0,@argdate),0))

set @mnEnd = dateadd(month,1,@mnBgn)

...

rucypli 2008-10-22

打赏
举报

拼sql
union all

zoujiaming 2008-10-22

打赏
举报

如果不想用临时表怎么办？

skyzcl 2008-10-22

打赏
举报

用临时表

问题没弄清楚，再来求解，关于月报的问题

相关推荐