现在我在做一个出勤历史查询,查询出 矿工的次数、还有出勤率,表里面有的列 SignId(签卡ID) SignTime (签卡时间),SignTag 签卡标签(签到,签退), UserName(签卡姓名) 在一段时间内查询 非周六和周日。矿工次数: select count (*) from ManualSign where UserName='张三' and SignTime between 开始时间 and 结束时间 剩下的条件怎么写。。求出这个张三的矿工次数?? 还有出勤率 是什么样的公式,,很迷茫 请给我代码
最好是一句话,asp前台条方法。再次谢谢大家了。如果一句sql解决不了,, 把SignTime 那前台处理,是否能循环时间呢? DateTime d1= Convert.ToDateTime( TextBox1.Text); 开始时间
DateTime d2=Convert.ToDateTime(TextBox2.Text); 结束时间
for(i=d1;i <=d2;i++)
{
怎么操作?
}
最好是一句话,asp前台条方法。再次谢谢大家了。如果一句sql解决不了,, 把SignTime 那前台处理,是否能循环时间呢? DateTime d1= Convert.ToDateTime( TextBox1.Text); 开始时间
DateTime d2=Convert.ToDateTime(TextBox2.Text); 结束时间
for(i=d1;i <=d2;i++)
{
怎么操作?
}
一般来说出勤率,就是指:有来上班的天数/应该上班的天数旷工你怎么定义?早上没打卡就早上旷工?下午没打卡就下午旷工?还是以一天为单位?“SignTag 签卡 标签(签到,签退)” 这些,看不懂是什么
SignTag 字段是(签到或签退)
以月份为单位的话,把年月、人员id传进去,或者部门id传进去,查整个部门的人
用存储过程或者function解决
在sql里判断一个月有多少工作日,和员工的打卡情况判断具体怎么写,没那么多时间,思路大概就这样
你截的那个图,要写sql查出来,不是一会能写完的
sum(出勤次数) as 出勤,
((开始时间到结束时间内的应出勤次数)(结束时间 - 开始时间 + 1) - sum(出勤次数) - sum(迟 到)-sum(早退)) as 旷工次数
from ManualSign
group by 员工姓名这里要解决(开始时间到结束时间内的应出勤次数)
部门一个表,机构一个表,人员一个表,还有签卡信息表 这些表要联合起来 存储过程或者function怎么调, sql懂得太少了。还有储过程或者function写法 太迷茫了,如果能帮我解决我的所有分都给你, 憋了两天了。
部门表、机构表、人员表、签卡信息表
需要得到的效果就是那张图,然后等高人帮你写sql吧我自问要写出你截图的那个效果来,到午饭去也写不完,帮不了你了
--SQL 取出 所有周六 周日的日期
create table SatSun([id] int identity(1,1),[date] datetime,[weekday] char(6))
godeclare @datetime datetime,@weekday char(6)
set @datetime='2009-10-1'
while @datetime<='2009-10-31'
begin
select @weekday=datename(weekday,@datetime)
if @weekday= '星期六' insert SatSun([date],[weekday]) values(@datetime,@weekday)
if @weekday='星期日' insert SatSun([date],[weekday]) values(@datetime,'星期天')
select @datetime=dateadd(day,1,@datetime)
end
goselect * from satsundrop table satsun
declare @d1 datetime,@d2 datetime
select @d1='2009-10-19',@d2='2009-10-21'
select datediff(wk,dateadd(dd,-datepart(dw,@d1)+1,@d1),
dateadd(dd,-datepart(dw,@d2)+1,@d2)) * 5 +
(datepart(dw,@d2)-1) - (datepart(dw,@d1)-1)
-- 本帖的解决方法
select datediff(wk,dateadd(dd,-datepart(dw,开始时间)+1,开始时间),
dateadd(dd,-datepart(dw,结束时间)+1,结束时间)) * 5 +
(datepart(dw,结束时间)-1) - (datepart(dw,开始时间)-1)-count(1) as '矿工次数'
,convert(float,count(1))/datediff(wk,dateadd(dd,-datepart(dw,开始时间)+1,开始时间),
dateadd(dd,-datepart(dw,结束时间)+1,结束时间)) * 5 +
(datepart(dw,结束时间)-1) - (datepart(dw,开始时间)-1) as '出勤率'
from ManualSign where UserName='张三' and SignTime between 开始时间 and 结束时间
DateTime end = Convert.ToDateTime(endTime).AddDays(1);//考勤統計開始時間
//DateTime end = Convert.ToDateTime(endTime); //結束時間
string sql = "select u.UserId,u.UserName,d.DepartName,b.BranchName, "+
"datediff(d,'" + start + "','" + end + "') as Total, "+
"(select count(*) from ManualSign ms,WorkTime wt "+
"where SignTag=1 and ms.UserId=u.UserId and datediff(mi,convert(char(8),SignTime,108),OnDutyTime) "+
"between -9 and 0 and (SignTime between '" + start + "' and '" + end + "')) as cidao , " +
"(select count(*) from ManualSign ms,WorkTime wt "+
"where SignTag=0 and ms.UserId=u.UserId and datediff(mi,convert(char(8),SignTime,108),OffDutyTime) "+
"between 1 and 10 and (SignTime between '" + start + "' and '" + end + "')) as zaotui, " +
"(select count(*) from ManualSign ms,WorkTime wt "+
"where SignTag=1 and ms.UserId=u.UserId and datediff(mi,convert(char(8),SignTime,108),OnDutyTime) "+
"<=-10 and (SignTime between '" + start + "' and '" + end + "')) + "+
"(select count(*) from ManualSign ms,WorkTime wt "+
"where SignTag=0 and ms.UserId=u.UserId and datediff(mi,convert(char(8),SignTime,108),OffDutyTime) "+
">=11 and (SignTime between '" + start + "' and '" + end + "')) + "+
"(select datediff(d,'" + start + "','" + end + "') - (select count(*) from ManualSign ms where SignTag=1 and "+
"(SignTime between '" + start + "' and '" + end + "') and ms.UserId=u.UserId)) + "+
"(select datediff(d,'" + start + "','" + end + "') - (select count(*) from ManualSign ms where SignTag=0 and "+
"(SignTime between '" + start + "' and '" + end + "') and ms.UserId=u.UserId)) as kgong "+
"from UserInfo u,DepartInfo d,BranchInfo b "+
"where b.BranchId = d.BranchId and d.DepartId = u.DepartId";
DateTime end = Convert.ToDateTime(endTime).AddDays(1);//考勤統計開始時間
//DateTime end = Convert.ToDateTime(endTime); //結束時間
string sql = "select u.UserId,u.UserName,d.DepartName,b.BranchName, "+
"datediff(d,'" + start + "','" + end + "') as Total, "+
"(select count(*) from ManualSign ms,WorkTime wt "+
"where SignTag=1 and ms.UserId=u.UserId and datediff(mi,convert(char(8),SignTime,108),OnDutyTime) "+
"between -9 and 0 and (SignTime between '" + start + "' and '" + end + "')) as cidao , " +
"(select count(*) from ManualSign ms,WorkTime wt "+
"where SignTag=0 and ms.UserId=u.UserId and datediff(mi,convert(char(8),SignTime,108),OffDutyTime) "+
"between 1 and 10 and (SignTime between '" + start + "' and '" + end + "')) as zaotui, " +
"(select count(*) from ManualSign ms,WorkTime wt "+
"where SignTag=1 and ms.UserId=u.UserId and datediff(mi,convert(char(8),SignTime,108),OnDutyTime) "+
"<=-10 and (SignTime between '" + start + "' and '" + end + "')) + "+
"(select count(*) from ManualSign ms,WorkTime wt "+
"where SignTag=0 and ms.UserId=u.UserId and datediff(mi,convert(char(8),SignTime,108),OffDutyTime) "+
">=11 and (SignTime between '" + start + "' and '" + end + "')) + "+
"(select datediff(d,'" + start + "','" + end + "') - (select count(*) from ManualSign ms where SignTag=1 and "+
"(SignTime between '" + start + "' and '" + end + "') and ms.UserId=u.UserId)) + "+
"(select datediff(d,'" + start + "','" + end + "') - (select count(*) from ManualSign ms where SignTag=0 and "+
"(SignTime between '" + start + "' and '" + end + "') and ms.UserId=u.UserId)) as kgong "+
"from UserInfo u,DepartInfo d,BranchInfo b "+
"where b.BranchId = d.BranchId and d.DepartId = u.DepartId";
你是不是學程序的呀!這個都看不懂呀!就算是北大青鳥的Y2學員也看得懂了。
不懂就去問你們的教員了,教員肯定知道的了,北大青鳥的教員有些還是蠻牛逼的了。