大家好!
从*.dqy文件查询sql的数据,想实现输入参数查询.(即可以输入参数,使查询任何一个时间段,一个部门的数据到一个excel表)
这是我做的dqy文件,但没有输入参数查询
--------------------------------------------------
XLODBC
1
DSN=1;UID=newhruser;APP=Microsoft Office 2003;WSID=ALLEN;DATABASE=ziguang;Network=DBMSSOCN
exec allen5Kqtj '2008-05-01' ,'2008-05-30' ,'15'
工号 姓名 出勤日1 出勤日2
--------------------------------------------------下面是我在数据库做的存储过程allen5Kqtjset ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
gocreate procedure [ziguang].[allen5Kqtj]
@bdt smalldatetime,@edt smalldatetime,@departid tinyint asbegin
select e.employeecode as 工号,e.chinesename as 姓名,kq.[出勤日1],kq.[出勤日2]
from employeemsg e join
(select employeeid,sum(case when (onoffdutydata.rwt+onoffdutydata.rot)>=8 then 1 else 0 end) as [出勤日1],
sum(case when onoffdutydata.rwt>=8 then 1 else 0 end) as [出勤日2]
from onoffdutydata
where checkdate between @bdt and @edt
group by employeeid) kq
on kq.employeeid=e.employeeid
where e.department=@departid and e.chinesename not like '[ABCDE]%'
order by e.employeecode
end
从*.dqy文件查询sql的数据,想实现输入参数查询.(即可以输入参数,使查询任何一个时间段,一个部门的数据到一个excel表)
这是我做的dqy文件,但没有输入参数查询
--------------------------------------------------
XLODBC
1
DSN=1;UID=newhruser;APP=Microsoft Office 2003;WSID=ALLEN;DATABASE=ziguang;Network=DBMSSOCN
exec allen5Kqtj '2008-05-01' ,'2008-05-30' ,'15'
工号 姓名 出勤日1 出勤日2
--------------------------------------------------下面是我在数据库做的存储过程allen5Kqtjset ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
gocreate procedure [ziguang].[allen5Kqtj]
@bdt smalldatetime,@edt smalldatetime,@departid tinyint asbegin
select e.employeecode as 工号,e.chinesename as 姓名,kq.[出勤日1],kq.[出勤日2]
from employeemsg e join
(select employeeid,sum(case when (onoffdutydata.rwt+onoffdutydata.rot)>=8 then 1 else 0 end) as [出勤日1],
sum(case when onoffdutydata.rwt>=8 then 1 else 0 end) as [出勤日2]
from onoffdutydata
where checkdate between @bdt and @edt
group by employeeid) kq
on kq.employeeid=e.employeeid
where e.department=@departid and e.chinesename not like '[ABCDE]%'
order by e.employeecode
end
-----------
这句话是什么意思,
参数没有传进去,
还是传进去了没有起作用.
你的存储过程有三个参数,其中两个是日期,建议改成自动取当月日期,至于另一个参数(好像是部门代码),没有好的方法
问题差不多解决了.
将
--------------------------------------------------
XLODBC
1
DSN=1;UID=newhruser;APP=Microsoft Office 2003;WSID=ALLEN;DATABASE=ziguang;Network=DBMSSOCN
exec allen5Kqtj '2008-05-01' ,'2008-05-30' ,'15' 工号 姓名 出勤日1 出勤日2
--------------------------------------------------
中的exec allen5Kqtj '2008-05-01' ,'2008-05-30' ,'15' 改为:
exec allen5Kqtj ? ,? ,?
就可以用了.我们公司的人事系统功能比较差,统计不出每月各部门工作人员的出勤日,所以求助于excel.:)