大概是这样,你试试,我没空给你试试.你试试把.
select a.compnyid, a.clinam, ,b.迟到次数 ,b.早退次数 from employee a
join (select compnyid,迟到次数= sum(case when idtime >'8:00' and idtime<12:00 then 1 else 0 end),早退次数= sum(case when idtime >'12:00' and idtime<17:00 then 1 else 0 end) from timeclk group by compnyid where..) b on a.comnyid=b.comnyid
select a.compnyid, a.clinam, ,b.迟到次数 ,b.早退次数 from employee a
join (select compnyid,迟到次数= sum(case when idtime >'8:00' and idtime<12:00 then 1 else 0 end),早退次数= sum(case when idtime >'12:00' and idtime<17:00 then 1 else 0 end) from timeclk group by compnyid where..) b on a.comnyid=b.comnyid
迟到次数= sum(case when b.iotime >'8:00' and b.iotime<12:00 then 1 else 0 end),
早退次数= sum(case when b.iotime >'12:00' and b.iotime<17:00 then 1 else 0 end)
from Employee a join TimeCLK b on a.CompnyID=b.CompnyID
where b.IODate between ? and ?
group by a.compnyid, a.clinam,a.DeptMntC,a.Function
set @b = '2003-01-01'
declare @e datetime
set @e = '2003-02-01'
select CompnyID
,(select count(*)
from TimeCLK b
where CompnyID = a.CompnyID
and IODate >= @b
and IODate <= @e
and (select min(iotime)
from TimeCLK
where IODate = b.IODate
) >= '08:00'
)
,(select count(*)
from TimeCLK
where CompnyID = a.CompnyID
and IODate >= @b
and IODate <= @e
and (select max(iotime)
from TimeCLK
where IODate = b.IODate
) <= '17:00' )
from TimeCLK a
group by CompnyID
set @b = '2003-01-01'
declare @e datetime
set @e = '2003-02-01'
select CompnyID
,min(ChiNam) as 员工姓名
,max(DeptMntC) as 部门名称
,min([Function]) as 职位名称
,(select count(*)
from TimeCLK b
where CompnyID = a.CompnyID
and IODate >= @b
and IODate <= @e
and (select min(iotime)
from TimeCLK
where IODate = b.IODate
) >= '08:00'
) as 迟到次数
,(select count(*)
from TimeCLK
where CompnyID = a.CompnyID
and IODate >= @b
and IODate <= @e
and (select max(iotime)
from TimeCLK
where IODate = b.IODate
) <= '17:00' ) as 早退次数
from TimeCLK a
left join Employee on a.CompnyID = Employee.CompnyID
group by CompnyID
INSERT INTO TEST_INFO(COMID,CHINAME,DEPARTNAME,FUNCATIONNAME) VALUES(102,'YI','W','SALES')
INSERT INTO TEST_INFO(COMID,CHINAME,DEPARTNAME,FUNCATIONNAME) VALUES(103,'ER','L','SALES')insert into test2(comid,iodate,iotime) values( 102,'2004-01-02','08:00:00')
insert into test2(comid,iodate,iotime) values( 102,'2004-01-02','17:01:00')
insert into test2(comid,iodate,iotime) values( 102,'2004-01-03','08:00:00')
insert into test2(comid,iodate,iotime) values( 102,'2004-01-03','17:01:00')
insert into test2(comid,iodate,iotime) values( 102,'2004-01-04','08:01:00')--
insert into test2(comid,iodate,iotime) values( 102,'2004-01-04','17:01:00')
insert into test2(comid,iodate,iotime) values( 102,'2004-01-05','08:01:00') --
insert into test2(comid,iodate,iotime) values( 102,'2004-01-05','17:01:00')
insert into test2(comid,iodate,iotime) values( 102,'2004-01-06','08:01:00') --
insert into test2(comid,iodate,iotime) values( 102,'2004-01-06','17:01:00')
insert into test2(comid,iodate,iotime) values( 102,'2004-01-08','08:00:00') --
insert into test2(comid,iodate,iotime) values( 102,'2004-01-08','17:01:00')
insert into test2(comid,iodate,iotime) values( 102,'2004-01-09','08:01:00') --
insert into test2(comid,iodate,iotime) values( 102,'2004-01-09','17:01:00')
insert into test2(comid,iodate,iotime) values( 102,'2004-01-11','08:01:00') --
insert into test2(comid,iodate,iotime) values( 102,'2004-01-11','17:01:00')
insert into test2(comid,iodate,iotime) values( 102,'2004-01-21','08:01:00')--
insert into test2(comid,iodate,iotime) values( 102,'2004-01-21','17:01:00')
insert into test2(comid,iodate,iotime) values( 102,'2004-01-25','08:01:00') --
insert into test2(comid,iodate,iotime) values( 102,'2004-01-26','17:01:00')
insert into test2(comid,iodate,iotime) values( 102,'2004-01-26','08:00:00') --
insert into test2(comid,iodate,iotime) values( 102,'2004-01-27','08:00:10') --
insert into test2(comid,iodate,iotime) values( 102,'2004-01-27','17:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-02','08:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-02','17:00:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-03','08:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-03','17:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-04','08:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-04','17:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-05','08:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-05','17:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-06','08:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-06','17:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-08','08:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-08','17:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-09','08:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-09','17:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-11','08:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-11','17:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-21','08:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-21','17:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-25','08:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-26','17:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-26','08:01:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-27','08:00:00')
insert into test2(comid,iodate,iotime) values( 103,'2004-01-27','17:01:00')
SELECT b.comid as 工号,b.chiname as 名称,b.DEPARTNAME AS 部门,b.FUNCATIONNAME as 职位,
SUM(CASE when(a.iotime > '08:00:00' AND a.iotime!>'17:00:00'
) THEN 1 ELSE 0 END) AS '迟到',
SUM(CASE when a.iotime>'17:00:00' THEN 1 ELSE 0 END) AS '早退' FROM TEST2 a
inner join test_info b on a.comid=b.comid
where a.iodate>='2004-01-01'and a.iodate<='2004-01-30'
group by b.comid,b.chiname,b.DEPARTNAME,b.FUNCATIONNAMEDROP TABLE TEST2
DROP TABLE TEST_info
/*工号 名称 部门 职位 迟到 早退
----------- ------------ ------------ ------------ ----------- -----------
102 YI W SALES 8 11
103 ER L SALES 12 10*/
b.DEPARTNAME AS 部门,b.FUNCATIONNAME as 职位,
SUM(iif(format(a.iotime,"hh:mm:ss")>"08:00:00"
AND format(a.iotime,"hh:mm:ss")<="17:00:00"
,1,0)) AS '迟到',
SUM(iif(format(a.iotime,"hh:mm:ss")>"17:00:00",1,0)) AS '早退'
FROM TEST2 a
inner join test_info b on a.comid=b.comid
where a.iodate>=#2004-01-01#and a.iodate<=#2004-01-30#
group by b.comid,b.chiname,b.DEPARTNAME,b.FUNCATIONNAME
,(select count(*)
from TimeCLK b
where CompnyID = a.CompnyID
and IODate >= @b
and IODate <= @e
and (select min(iotime)
from TimeCLK
where IODate = b.IODate
) >= '08:00'
)
,(select count(*)
from TimeCLK
where CompnyID = a.CompnyID
and IODate >= @b
and IODate <= @e
and (select max(iotime)
from TimeCLK
where IODate = b.IODate
) <= '17:00' )
from TimeCLK a
group by CompnyID
,(select count(*)
from TimeCLK b
where CompnyID = a.CompnyID
and IODate >= @b
and IODate <= @e
and (select min(iotime)
from TimeCLK
where IODate = b.IODate
) >= '08:00:00'
)
,(select count(*)
from TimeCLK
where CompnyID = a.CompnyID
and IODate >= @b
and IODate <= @e
and (select max(iotime)
from TimeCLK
where IODate = b.IODate
) <= '17:00:00' )
from TimeCLK a
group by CompnyID
,(select count(*)
from TimeCLK b
where CompnyID = a.CompnyID
and IODate >= @b
and IODate <= @e
and (select min(iotime)
from TimeCLK
where CompnyID = b.CompnyID
and IODate = b.IODate
) >= '08:00:00'
)
,(select count(*)
from TimeCLK c
where CompnyID = a.CompnyID
and IODate >= @b
and IODate <= @e
and (select max(iotime)
from TimeCLK
where CompnyID = c.CompnyID
and IODate = c.IODate
) <= '17:00:00' )
from TimeCLK a
group by CompnyID
,min(ChiNam) as 员工姓名
,max(DeptMntC) as 部门名称
,min([Function]) as 职位名称
,(select count(*)
from (select distinct IODate
from TimeCLK b
where CompnyID = a.CompnyID
and IODate >= @b
and IODate <= @e
and (select min(iotime)
from TimeCLK
where IODate = b.IODate
and CompnyID = c.CompnyID
) >= '08:00:00') T1
) as 迟到次数
,(select count(*)
from (select distinct IODate
from TimeCLK
where CompnyID = a.CompnyID
and IODate >= @b
and IODate <= @e
and (select max(iotime)
from TimeCLK
where IODate = c.IODate
and CompnyID = c.CompnyID
) <= '17:00:00') T2 ) as 早退次数
from TimeCLK a
left join Employee on a.CompnyID = Employee.CompnyID
group by CompnyID
use iif as ZJ or pure SQL like playyuer
Access?跟我不是很熟!
----------------------