--查询结果：
--显示从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