//原来写的proc All_input_window
ALTER proc All_input_window
@st_date varchar(10), --开始日期
@ed_date varchar(10) --结束日期
as
begin
--*****************************************************
--
-- 统计某月份每天坚持输入业务的窗口
--*****************************************************
--定义变量
declare @work_days varchar(10) --工作日天数
--1,计算当月除星期6和周日外的天数
set @work_days=(datediff(day,@st_date,@ed_date)+1)-2*datediff(ww,@st_date,@ed_date) --每月要输的天数,没考虑节假日
--如果存在临时表就先删除
if exists (select * from dbo.sysobjects where id=object_id(N'[dbo].[detail_temp]') and OBJECTPROPERTY(id,N'IsUserTable')=1)
begin
drop table detail_temp
end
--建第一个临时表,写入临时表数据
select windowCode,convert(varchar(10),inputDate,120) as inputDate
into detail_temp
from detail
where convert(varchar(10),inputDate,120)>=cast(@st_date as datetime) and convert(varchar(10),inputdate,120)<=cast(@ed_date as datetime)
group by convert(varchar(10),inputDate,120),windowCode
order by windowCode asc
--如果存在临时表就先删除
if exists (select * from dbo.sysobjects where id=object_id(N'[dbo].[detail_temp1]') and OBJECTPROPERTY(id,N'IsUserTable')=1)
begin
drop table detail_temp1
end
-- 建第二个临时表
select d.windowCode,w.name,count(d.windowCode) as zj into detail_temp1
from detail_temp d,window w
where d.windowCode=w.windowCode
group by d.windowCode,w.name
order by d.zj asc
--如果存在临时表就先删除
if exists (select * from dbo.sysobjects where id=object_id(N'[dbo].[detail_temp2]') and OBJECTPROPERTY(id,N'IsUserTable')=1)
begin
drop table detail_temp2
end
drop table detail_temp2
-- 数据库中的天数大于等于工作日天数的单位,说明每天坚持在输
select *
into detail_temp2
from detail_temp1
where zj>=@work_days
order by windowCode
end
//修改后的
ALTER proc All_input_window2
@st_date varchar(10), --开始日期
@ed_date varchar(10) --结束日期
as
begin
--*****************************************************
--
-- 统计某月份每天坚持输入业务的窗口
--*****************************************************
--定义变量
declare @work_days varchar(10) --工作日天数
--1,计算当月除星期6和周日外的天数
set @work_days=(datediff(day,@st_date,@ed_date)+1)-2*datediff(ww,@st_date,@ed_date) --每月要输的天数,没考虑节假日
--如果存在临时表就先删除
if exists (select * from dbo.sysobjects where id=object_id(N'[dbo].[detail_temp]') and OBJECTPROPERTY(id,N'IsUserTable')=1)
begin
drop table detail_temp
end
--建临时表,写入临时表数据(网页从这个表中读数据)
select d.windowCode windowCode,w.name name,count(d.windowCode) as zj into detail_temp
from
(select windowCode,convert(varchar(10),inputDate,120) as inputDate
--into detail_temp
from detail
where convert(varchar(10),inputDate,120)>=cast(@st_date as datetime) and convert(varchar(10),inputdate,120)<=cast(@ed_date as datetime)
group by convert(varchar(10),inputDate,120),windowCode) d,window w
where d.windowCode=w.windowCode
group by d.windowCode,w.name
having count(d.windowCode)>=@work_days
order by count(d.windowCode) asc
end
//===========ASP中直接调用显示存储过程的结果集还没搞定,这里是让存储过程把结果导入一临时表中,在ASP中读取这个临时表的数据。
<%if request("action")="true" then%>
<table width="600" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#cccccc">
<tr>
<td width="271" height="25" bgcolor="#FFFFFF"> <strong>单位名称</strong></td>
<td width="326" bgcolor="#FFFFFF"><strong> 窗口名称</strong></td>
</tr>
<%
'**********计算一个月的开始日期和结束日期***********
dim st_date,ed_date,work_days
st_date=request("niandu")+"-"+request("yue")+"-01"
if request("yue")="12" then
ed_date=Cstr(Cint(request("niandu"))+1)+"-01-01"
else
ed_date=request("niandu")+"-"+Cstr(Cint(request("yue"))+1)+"-01"
end if
st_date=Cdate(st_date)
ed_date=Cdate(ed_date)-1
'work_days=(datediff("d",st_date,ed_date)+1)-2*datediff("ww",st_date,ed_date)
'***************************************************
Set MyComm = Server.CreateObject("ADODB.Command")
with MyComm
.ActiveConnection = conn 'MyConStr是数据库连接字串
.CommandText = "All_input_window2" '指定存储过程名
.CommandType = 4 '表明这是一个存储过程
.Prepared = true '要求将SQL命令先行编译
.Parameters.append .CreateParameter("@st_date",200,1,10,st_date)
.Parameters.append .CreateParameter("@ed_date",200,1,10,ed_date)
'.Parameters.append .CreateParameter("@work_days",200,1,10,work_days)
.Execute
end with
set rs=server.createobject("adodb.recordset")
sql="select d.windowCode,d.name,d.zj,p.name as dwname from detail_temp as d,department as p where left(d.windowCode,3)=p.depCode"
rs.open sql,conn,1,1
for i=1 to rs.recordcount
%>
<tr>
<td height="25" bgcolor="#FFFFFF"> <%=rs("dwname")%> </td>
<td height="25" bgcolor="#FFFFFF"> <%=rs("name")%> </td>
</tr>
<%rs.movenext
next%>
</table>
<%end if%>
//==另外一个过程
ALTER proc ggzj_shzj
@st_date varchar(10), --开始日期
@ed_date varchar(10) --结束日期
as
begin
--***************************************************************************************
--公共职业介绍和十二个社会职介以下指标数字要求自动求和(1)共提供就业岗位____个
--(2)接待求职人员____人(3)求职登记介绍成功____人,(4) 介绍成功中的下岗失业人员数____人。
--***************************************************************************************
--删除临时表
if exists (select * from dbo.sysobjects where id=object_id(N'[dbo].[detail_temp]') and OBJECTPROPERTY(id,N'IsUserTable')=1)
begin
drop table detail_temp
end
create table detail_temp(
zb_name varchar(100),
zj varchar(50)
)
--提供就业岗位数
insert into detail_temp
select '提供就业岗位数',sum(amount)
from detail
where
(convert(varchar(10),inputDate,120)>=@st_date and convert(varchar(10),inputDate,120)<=@ed_date)
and((windowCode='106101' and workCode='106101002')
or (windowCode='122101' and workCode='122101002')
or (windowCode='122102' and workCode='122102002')
or (windowCode='122103' and workCode='122103002')
or (windowCode='122104' and workCode='122104002')
or (windowCode='122105' and workCode='122105002')
or (windowCode='122106' and workCode='122106002')
or (windowCode='122107' and workCode='122107002')
or (windowCode='122108' and workCode='122108002')
or (windowCode='122109' and workCode='122109002')
or (windowCode='122110' and workCode='122110002')
or (windowCode='122111' and workCode='122111002')
or (windowCode='122112' and workCode='122112002') )
--接待求职人员数
insert into detail_temp
select '接待求职人员数',sum(amount)
from detail
where
(convert(varchar(10),inputDate,120)>=@st_date and convert(varchar(10),inputDate,120)<=@ed_date)
and((windowCode='106101' and (workCode='106101003' or workCode='106101004' or workCode='106101005'))
or (windowCode='122101' and (workCode='122101003' or workCode='122101004' or workCode='122101005'))
or (windowCode='122102' and (workCode='122102003' or workCode='122102004' or workCode='122102005'))
or (windowCode='122103' and (workCode='122103003' or workCode='122103004' or workCode='122103005'))
or (windowCode='122104' and (workCode='122104003' or workCode='122104004' or workCode='122104005'))
or (windowCode='122105' and (workCode='122105003' or workCode='122105004' or workCode='122105005'))
or (windowCode='122106' and (workCode='122106003' or workCode='122106004' or workCode='122106005'))
or (windowCode='122107' and (workCode='122107003' or workCode='122107004' or workCode='122107005'))
or (windowCode='122108' and (workCode='122108003' or workCode='122108005' or workCode='122108005'))
or (windowCode='122109' and (workCode='122109003' or workCode='122109004' or workCode='122109005'))
or (windowCode='122110' and (workCode='122110003' or workCode='122110004' or workCode='122110005'))
or (windowCode='122111' and (workCode='122111003' or workCode='122111004' or workCode='122111005'))
or (windowCode='122112' and (workCode='122112003' or workCode='122112004' or workCode='122112005')))
--求职登记介绍成功数
insert into detail_temp
select '求职登记介绍成功数',sum(amount)
from detail
where
(convert(varchar(10),inputDate,120)>=@st_date and convert(varchar(10),inputDate,120)<=@ed_date)
and((windowCode='106101' and workCode='106101006')
or (windowCode='122101' and workCode='122101006')
or (windowCode='122102' and workCode='122102006')
or (windowCode='122103' and workCode='122103006')
or (windowCode='122104' and workCode='122104006')
or (windowCode='122105' and workCode='122105006')
or (windowCode='122106' and workCode='122106006')
or (windowCode='122107' and workCode='122107006')
or (windowCode='122108' and workCode='122108006')
or (windowCode='122109' and workCode='122109006')
or (windowCode='122110' and workCode='122110006')
or (windowCode='122111' and workCode='122111006')
or (windowCode='122112' and workCode='122112006'))
--介绍成功中的下岗失业人员数
insert into detail_temp
select '介绍成功中的下岗失业人员数',sum(amount)
from detail
where
(convert(varchar(10),inputDate,120)>=@st_date and convert(varchar(10),inputDate,120)<=@ed_date)
and((windowCode='106101' and workCode='106101007')
or (windowCode='122101' and workCode='122101007')
or (windowCode='122102' and workCode='122102007')
or (windowCode='122103' and workCode='122103007')
or (windowCode='122104' and workCode='122104007')
or (windowCode='122105' and workCode='122105007')
or (windowCode='122106' and workCode='122106007')
or (windowCode='122107' and workCode='122107007')
or (windowCode='122108' and workCode='122108007')
or (windowCode='122109' and workCode='122109007')
or (windowCode='122110' and workCode='122110007')
or (windowCode='122111' and workCode='122111007')
or (windowCode='122112' and workCode='122112007'))
end