select a.[uid] as [用户ID],CONVERT(varchar(10), a.udate,121) as [日期], a.[udate] as [签到时间]
, a.[ulocation] as [签到地点], b.[udate] as [签退时间], b.[ulocation] as [签退地点] from
(
select * from [考勤表] where utype=0
) a
left join (
select * from [考勤表] where utype=1
) b on a.[uid]=b.[uid]
create table kq
(uid varchar(10),utype int,udate datetime,ulocation varchar(30))insert into kq
select '10001',0,'2014-04-15 08:32:18','海淀区复兴路47号' union all
select '10001',1,'2014-04-15 18:32:18','海淀区复兴路47号' union all
select '10002',0,'2014-04-15 08:33:19','海淀区复兴路36号' union all
select '10002',1,'2014-04-15 19:31:19','海淀区复兴路33号' union all
select '10001',0,'2014-04-16 08:09:18','海淀区复兴路47号' union all
select '10001',1,'2014-04-16 18:32:18','海淀区复兴路47号' union all
select '10002',0,'2014-04-16 09:33:19','海淀区复兴路36号' union all
select '10002',1,'2014-04-16 19:31:19','海淀区复兴路33号'
-- 查询一
select a.[uid] '用户ID',
convert(varchar(16),a.udate,23) '日期',
max(case when [uid]=a.[uid]
and convert(varchar,udate,23)=convert(varchar,a.udate,23)
and utype=0 then convert(varchar(16),udate,108) else '' end) '签到时间',
max(case when [uid]=a.[uid]
and convert(varchar,udate,23)=convert(varchar,a.udate,23)
and utype=0 then ulocation else '' end) '签到地点',
max(case when [uid]=a.[uid]
and convert(varchar,udate,23)=convert(varchar,a.udate,23)
and utype=1 then convert(varchar(16),udate,108) else '' end) '签退时间',
max(case when [uid]=a.[uid]
and convert(varchar,udate,23)=convert(varchar,a.udate,23)
and utype=1 then ulocation else '' end) '签退地点'
from kq a
group by a.[uid],convert(varchar(16),a.udate,23)
order by convert(varchar(16),a.udate,23)/*
用户ID 日期 签到时间 签到地点 签退时间 签退地点
---------- ---------------- ---------------- ------------------------------ ---------------- ------------------------------
10001 2014-04-15 08:32:18 海淀区复兴路47号 18:32:18 海淀区复兴路47号
10002 2014-04-15 08:33:19 海淀区复兴路36号 19:31:19 海淀区复兴路33号
10001 2014-04-16 08:09:18 海淀区复兴路47号 18:32:18 海淀区复兴路47号
10002 2014-04-16 09:33:19 海淀区复兴路36号 19:31:19 海淀区复兴路33号(4 row(s) affected)
*/
DROP TABLE KQ
GO
CREATE TABLE KQ(uid VARCHAR(10),utype INT,udate DATETIME,ulocation VARCHAR(50))
INSERT INTO KQ
SELECT '10001',0,'2014-04-15 08:32:18','海淀区复兴路47号' UNION ALL
SELECT '10001',1,'2014-04-15 18:32:18','海淀区复兴路47号' UNION ALL
SELECT '10002',0,'2014-04-15 08:33:19','海淀区复兴路36号' UNION ALL
SELECT '10002',1,'2014-04-15 19:31:19','海淀区复兴路33号' UNION ALL
SELECT '10001',0,'2014-04-16 08:09:18','海淀区复兴路47号' UNION ALL
SELECT '10001',1,'2014-04-16 18:32:18','海淀区复兴路47号' UNION ALL
SELECT '10002',0,' 2014-04-16 09:33:19','海淀区复兴路36号' UNION ALL
SELECT '10002',1,' 2014-04-16 19:31:19','海淀区复兴路33号'
-----------------------------------查询--------------------------------------------
SELECT uid,CONVERT(DATE, udate) '日期',
CONVERT(VARCHAR,MAX(CASE utype WHEN 0 THEN udate END),24) '签到时间',
MAX(CASE utype WHEN 0 THEN ulocation END) '签到地点',
CONVERT(VARCHAR,MAX(CASE utype WHEN 1 THEN udate END),24) '签退时间',
MAX(CASE utype WHEN 1 THEN ulocation END) '签退地点'
FROM KQ GROUP BY uid,CONVERT(DATE,udate)
/*
uid 日期 签到时间 签到地点 签退时间 签退地点
---------- ---------- ------------------------------ -------------------------------------------------- ------------------------------ --------------------------------------------------
10001 2014-04-15 08:32:18 海淀区复兴路47号 18:32:18 海淀区复兴路47号
10002 2014-04-15 08:33:19 海淀区复兴路36号 19:31:19 海淀区复兴路33号
10001 2014-04-16 08:09:18 海淀区复兴路47号 18:32:18 海淀区复兴路47号
10002 2014-04-16 09:33:19 海淀区复兴路36号 19:31:19 海淀区复兴路33号(4 行受影响)
*/
GO
----------------------------------统计--------------------------------------------
DECLARE @sql VARCHAR(8000) ,
@month INT ,--月份
@mcount INT ,--月天数
@mdate DATETIME ,--月初始值
@i INT--计数
BEGIN
SET @month = 4
SET @i = 1
SET @mdate = STR(YEAR(GETDATE())) + '-' + LTRIM(@month) + '-' + '1'
SELECT @mcount = DATEDIFF(dd, @mdate, DATEADD(mm, 1, @mdate))
SET @sql = 'select a.*,b.[正常天数(出勤)],b.[缺勤],b.[迟到早退] from (select uid'
WHILE @i <= @mcount
BEGIN
SET @sql = ISNULL(@sql + ',', '') + 'max(case day(udate) when '
+ LTRIM(@i) + ' then [utype] else '''' end) as [' + LTRIM(@i)
+ ']'
SET @i = @i + 1
END
SELECT @sql = @sql + ' from KQ where month(udate)=' + LTRIM(@month)
+ ' group by uid ) a'
SELECT @sql = @sql + ',(SELECT uid, SUM(CASE [utype] WHEN 1 THEN 1 ELSE 0 END) ''正常天数(出勤)'' ,'
SELECT @sql = @sql + ''+CONVERT(VARCHAR(20),@mcount)+'-SUM(CASE [utype] WHEN 1 THEN 1 ELSE 0 END) AS ''缺勤'' ,
SUM(
CASE WHEN utype=0 AND Convert(varchar(20),udate,108)>Convert(varchar(20),''08:30:00'',108) THEN 1
WHEN utype=1 AND Convert(varchar(20),udate,108)<Convert(varchar(20),''18:30:00'',108) THEN 1
ELSE 0 END) AS ''迟到早退''
FROM KQ WHERE MONTH(udate)=4 GROUP BY uid) b WHERE a.uid=b.uid'
EXEC(@sql)
END
/*
uid 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 正常天数(出勤) 缺勤 迟到早退
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
10001 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 28 1
10002 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 28 2(2 行受影响)
*/