1。改成
//先判断表里,有没有这个参数对应的记录,如果有,就直接select出来,如果没有,再insert
if exists(select @st_month = st_month from Emstatistic where st_month = @p_st_month)2.select Employee.em_name,
sum(case em_state when '1' then 1 else 0 end),
sum(case em_state when '2' then 1 else 0 end),
sum(case late_state when '1' then 1 else 0 end),
sum(case early_state when '1' then 1 else 0 end),
sum(case abhour_state when '1' then 1 else 0 end),
sum(case abhalf_state when '1' then 1 else 0 end),
sum(case abday_state when '1' then 1 else 0 end),
sum(case addwork_state when '1' then 1 else 0 end),
sum(addwork), datepart(month, Datastate.carddate)
from Employee, Datastate
where Employee.em_id=Datastate.card_id and
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
改成
where Employee.em_id=Datastate.card_id and datepart(month, Datastate.carddate)=@p_st_month group by datepart(month, Datastate.carddate),
Employee.em_name3。你可以判断,如果没到月底不允许统计
//先判断表里,有没有这个参数对应的记录,如果有,就直接select出来,如果没有,再insert
if exists(select @st_month = st_month from Emstatistic where st_month = @p_st_month)2.select Employee.em_name,
sum(case em_state when '1' then 1 else 0 end),
sum(case em_state when '2' then 1 else 0 end),
sum(case late_state when '1' then 1 else 0 end),
sum(case early_state when '1' then 1 else 0 end),
sum(case abhour_state when '1' then 1 else 0 end),
sum(case abhalf_state when '1' then 1 else 0 end),
sum(case abday_state when '1' then 1 else 0 end),
sum(case addwork_state when '1' then 1 else 0 end),
sum(addwork), datepart(month, Datastate.carddate)
from Employee, Datastate
where Employee.em_id=Datastate.card_id and
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
改成
where Employee.em_id=Datastate.card_id and datepart(month, Datastate.carddate)=@p_st_month group by datepart(month, Datastate.carddate),
Employee.em_name3。你可以判断,如果没到月底不允许统计
解决方案 »
- 这条sql语句为何会有重复数据?
- 请问如何在一台服务器的数据库上的存储过程调用另一台服务器的数据库上的数据??急救!!!!!!!!
- 高分求解:多表外连接的写法,SQL高手请帮忙一下,謝謝!
- 怎么样才能够把一个表中的数据复制到另外一个表中去呢?
- 怎么设置数据库的访问权限啊?
- 关于delhpi与sqlserver2000 的问题
- 关于sql保存密码的文件
- 如何在SQL服務器端將一個數值轉換格式為 ###,###,###.###
- 这样的select语句写得出吗?急!在线等!!!
- 高手请帮忙!关于密文解密的问题。。
- 谁有SQL SERVER 2000中,所有数据类型的详细说明?一定给分!
- 关于C++程序设计语言(特别版)
to KnowLittle(人傻不要紧,只要肯学习。) 能不能把第三条说的细一些呀?谢谢:)
我说的意思是说你带的参数如果是1月,那么如果现在1月还没结束,那么这个sp啥么也不做。
但是这样的话,你需要两个参数,另一个就是年份
否则你如何判断?你说那
我给你加一个巴@yearselect Employee.em_name,
sum(case em_state when '1' then 1 else 0 end),
sum(case em_state when '2' then 1 else 0 end),
sum(case late_state when '1' then 1 else 0 end),
sum(case early_state when '1' then 1 else 0 end),
sum(case abhour_state when '1' then 1 else 0 end),
sum(case abhalf_state when '1' then 1 else 0 end),
sum(case abday_state when '1' then 1 else 0 end),
sum(case addwork_state when '1' then 1 else 0 end),
sum(addwork), datepart(month, Datastate.carddate)
from Employee, Datastate
where Employee.em_id=Datastate.card_id and datepart(year,DataSstate.carddate)=@year and datepart(month, Datastate.carddate)=@p_st_month and getdate()>(ltrim(str(@year))+'-'+ltrim(str(@p_st_month+1))+'-1')后面那个日期是下个月1号的日期,时间不写就是0点0分,那只要比他大自然说明上个月过完了
i'll finish this post if everything is ok:)
getdate()>(ltrim(str(@year))+'-'+ltrim(str(@p_st_month+1))+'-1')如何正确转换呢?
还有,象str(@p_st_month+1),如果@p_st_month = 12, 那怎么使其加1以后,得出来的是一月呢?
getdate() > CAST((ltrim(str(@year))+'-'+ltrim(str(@p_st_month+1))+'-1') as DATETIME)建议把存储过程的输入参数改成datetime类型
getdate() > CAST(ltrim(str(@year))+'-'+ltrim(CAST(str(@p_st_month) AS INT)+1)+'-1') as DATETIME)
getdate() > CAST(@year+'-'+CAST(CAST(@p_st_month AS INT)+1 AS VARCHAR(2))+'-1' as DATETIME)很多类型的转换操作~~还是把输入参数改为datetime类型算了~
不好意思,我马虎了
getdate()>dateadd(month,1,@year+'-'+@p_st_month+'-'+'1')
可Emstatistic表中没有年的字段,要加一个吗?
不然,不好判断条件的?
(@p_st_month char(2)='',
@p_st_year char(4)='')
ASset nocount ondeclare @count int
select @count=count(st_month) from Emstatistic where st_month = @p_st_month and st_year = @p_st_yearif @count = 0 /*No data found using the month and year parameter, insert new data insert into Emstatistic (em_name, em_state1, em_state2, late_num, early_num,
abhour_num, abhalf_num, abday_num, addwork_num, addwork_long, st_month, st_year) select Employee.em_name,
sum(case em_state when '1' then 1 else 0 end),
sum(case em_state when '2' then 1 else 0 end),
sum(case late_state when '1' then 1 else 0 end),
sum(case early_state when '1' then 1 else 0 end),
sum(case abhour_state when '1' then 1 else 0 end),
sum(case abhalf_state when '1' then 1 else 0 end),
sum(case abday_state when '1' then 1 else 0 end),
sum(case addwork_state when '1' then 1 else 0 end),
sum(addwork), datepart(month, Datastate.carddate),
datepart(year,Datastate.carddate) from Employee, Datastate where Employee.em_id=Datastate.card_id and datepart(year,Datastate.carddate) = @p_st_year
and datepart(month, Datastate.carddate) = @p_st_month and
getdate()>dateadd(month,1,@p_st_year+'-'+@p_st_month+'-'+'1') group by datepart(month, Datastate.carddate), Employee.em_name, datepart(year, Datastate.carddate)else
/* now select data out select em_name, em_state1, em_state2, late_num, early_num,
abhour_num, abhalf_num, abday_num, addwork_num,
addwork_long, st_month
from Emstatistic where st_month = @p_st_month and st_year = @p_st_yearGO不过现在还是有个问题,就是加完对年参数的判断后,项目中还是不能正确判断年,输入错误的年份,也会输出数据,再帮忙看看呀!
多谢了:)
1.负责生成统计的存储过程pro_Create_Static:要做的是生成当前时间前一月的统计(insert into Emstatistic )。把这存储过程交给SQL Server的“作业”定时去完成,调度时间就设为每月第一天的0时0分。这样生成统计就完全交给了系统去做,不用人手干预了。
2.负责取出统计信息的存储过程pro_Get_Static:要做的仅仅是根据输入参数取出(select from Emstatistic)统计信息。我想Emstatistic这个表的结构还是要改改,把st_month字段改成DateTime类型或增加一个“年”的字段,这样做也方便你的程序~~
declare @yearInt
select @yearInt=convert(int,@p_st_year)
if (@yearInt<2200 and @year>1900)
begin
其他所有的代码
end
declare @flag int
set @flag=isnumeric(@p_st_year)
print @flag
print convert(int,@p_st_year)
if (@flag=1)
begin
if (convert(int,@p_st_year)<2200 and convert(int,@p_st_year)>1900)
begin
其他所有的代码
end
end
abhour_num, abhalf_num, abday_num, addwork_num,
addwork_long, st_month
from Emstatistic where st_month = @p_st_month and st_year = @p_st_year
(@p_st_year char(4)='',
@p_st_month char(2)='')
AS
set nocount on
/* 看统计表中是否是要统计的数据
declare @count int
select @count = count(st_month) from Emstatistic where st_month = @p_st_month and st_year = @p_st_year/*看原始表中是否有满足参数据的待统计记录,
declare @count_ori int
select @count_ori = count(carddate) from Datastate where datepart(year, carddate) = @p_st_year and datepart(month, carddate) = @p_st_monthprint @count_ori
print @count
print @p_st_year
print @p_st_month
/* 如果统计表中没有要统计的记录,而原始数据表中有未统计的记录,则进行统计,并插入
if (@count = 0 and @count_ori > 0)
begin
insert into Emstatistic (em_name, em_state1, em_state2, late_num, early_num,
abhour_num, abhalf_num, abday_num, addwork_num, addwork_long, st_month, st_year) select Employee.em_name,
sum(case em_state when '1' then 1 else 0 end),
sum(case em_state when '2' then 1 else 0 end),
sum(case late_state when '1' then 1 else 0 end),
sum(case early_state when '1' then 1 else 0 end),
sum(case abhour_state when '1' then 1 else 0 end),
sum(case abhalf_state when '1' then 1 else 0 end),
sum(case abday_state when '1' then 1 else 0 end),
sum(case addwork_state when '1' then 1 else 0 end),
sum(addwork), datepart(month, Datastate.carddate),
datepart(year,Datastate.carddate) from Employee, Datastate where Employee.em_id=Datastate.card_id and datepart(year,Datastate.carddate) = @p_st_year
and datepart(month, Datastate.carddate) = @p_st_month and
getdate()>dateadd(month,1,@p_st_year+'-'+@p_st_month+'-'+'1') group by datepart(month, Datastate.carddate), Employee.em_name, datepart(year, Datastate.carddate)
endelse
begin
if (@count > 0)
begin
select em_name, em_state1, em_state2, late_num, early_num,
abhour_num, abhalf_num, abday_num, addwork_num,
addwork_long, st_month, st_year
from Emstatistic where st_month = @p_st_month and st_year = @p_st_year
end
end
GO
看看是不是这个原因?
不是,就当帮你up
2002.12. 有数据 能查出结果来 能查出结果 2003.1. 没有数据 查不出结果 查不出结果2003.12. 没有数据 查不出结果 查出2002.12.的结果
为什么页面的报表与查询分析器的结果不一样呢,代码是没问题的,
why??
把第一次 2003.12的数据现在继续读出来?
asp经常出现这种情况。把ie全关了,从新打开.或者, 我也很郁闷, 这个datepart(month, carddate) = @p_st_month 能行吗?整数 = 字符?
帮你up