日期:2014-05-17  浏览次数:20652 次

考勤统计sql
用户表:users

id name
1 张三
2 李四
3 王五

签到记录表:sign_record

id userid date
1 1 2012-10-1 7:14:03.000
2 2 2012-10-2 7:14:10.000
3 1 2012-10-4 8:14:59.000
4 1 2012-10-3 8:15:02.000
5 3 2012-10-3 7:15:08.000

查询结果:
显示从1号到最后一天的所有人的考勤情况:1、y为正常;2、n为当天没有签到;3、超过8点签到的记录迟到分钟数;

name 1 2 3 4 5 6 ... 31
张三 y n 15 n n n n
李四 n y n n n n n
王五 n n y 14 n n n

------解决方案--------------------
行转列, 

select userid, (case when day(date) = 1 and time(date)<=8 then 'y' when when day(date) = 1 and time(date)>8 then time(date)-8 when day(date) <> 1 then 'n') as "1",
.
.
.--一直写到31号
from sign_record
group by userid;


------解决方案--------------------
SQL code


--查询结果:
--显示从1号到最后一天的所有人的考勤情况:1、y为正常;2、n为当天没有签到;3、超过8点签到的记录迟到分钟数;
--name 1 2 3 4 5 6 ... 31
--张三 y n 15 n n n n
--李四 n y n n n n n
--王五 n n y 14 n n n

USE test
go


--if object_id('users') is not null 
--    drop table users
--Go
--Create table users([id] smallint,[name] nvarchar(2))
--Insert into users
--Select 1,N'张三'
--Union all Select 2,N'李四'
--Union all Select 3,N'王五'
--
--
--
--if object_id('sign_record') is not null 
--    drop table sign_record
--Go
--Create table sign_record([id] smallint,[userid] smallint,[date] datetime)
--Insert into sign_record
--Select 1,1,'2012-10-1 7:14:03.000'
--Union all Select 2,2,'2012-10-2 7:14:10.000'
--Union all Select 3,1,'2012-10-4 8:14:59.000'
--Union all Select 4,1,'2012-10-3 8:15:02.000'
--Union all Select 5,3,'2012-10-3 7:15:08.000'


;WITH Result_tmp AS (
    select 
        b.id
        ,b.name
        ,Convert(nvarchar(50),datediff(mi,convert(nvarchar(10),a.date,120)+' 08:00:00',a.date)) as datediff
        ,DAY(a.date) AS mark 
        from sign_record        AS a
            INNER JOIN users    AS b ON a.userid=b.id
),Result as (
    select 
            id
            ,name
            ,Case when datediff<=0 then 'y' Else datediff End as datediff
            ,mark
        from Result_tmp
)SELECT
        name,isnull([1],'n') As [1],isnull([2],'n') As [2],isnull([3],'n') As [3],isnull([4],'n') As [4],isnull([5],'n') As [5],isnull([6],'n') As [6],isnull([7],'n') As [7],isnull([8],'n') As [8],isnull([9],'n') As [9],isnull([10],'n') As [10],isnull([11],'n') As [11],isnull([12],'n') As [12],isnull([13],'n') As [13],isnull([14],'n') As [14],isnull([15],'n') As [15],isnull([16],'n') As [16],isnull([17],'n') As [17],isnull([18],'n') As [18],isnull([19],'n') As [19],isnull([20],'n') As [20],isnull([21],'n') As [21],isnull([22],'n') As [22],isnull([23],'n') As [23],isnull([24],'n') As [24],isnull([25],'n') As [25],isnull([26],'n') As [26],isnull([27],'n') As [27],isnull([28],'n') As [28],isnull([29],'n') As [29],isnull([30],'n') As [30],isnull([31],'n') As [31]
    FROM Result
    PIVOT(
        MAX(datediff) FOR mark IN([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],[31])
    )p
    Order by id
Go