社保统计软件中的几个过程

//原来写的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"> &nbsp;<strong>单位名称</strong></td>
    <td width="326" bgcolor="#FFFFFF"><strong>&nbsp;窗口名称</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">&nbsp; <%=rs("dwname")%> </td>
    <td height="25" bgcolor="#FFFFFF">&nbsp; <%=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