有一张考勤数据表kaoqin
id userid userName cardid eventTime
1 1 A 1 2014-01-02 08:00:00
2 1 A 1 2014-01-02 18:00:00
3 1 A 1 2014-01-02 19:00:00
4 2 B 2 2014-01-02 10:00:00
5 1 A 1 2014-01-03 8:00:00
6 1 A 1 2014-01-03 18:50:00
....................................
传入参数时间段，如:2014-01-01 - 2014-05-21 ,得到每一天（0点到23点）最早刷卡、最晚刷卡记录
id userid userName cardid startTime endTime
1 1 A 1 2014-01-02 08:00:00 2014-01-02 19:00:00
4 2 B 2 2014-01-02 10:00:00 2014-01-02 10:00:00
5 1 A 1 2014-01-03 8:00:00 2014-01-03 18:50:00
...........................
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-21 19:51:43
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[kaoqin]
if object_id('[kaoqin]') is not null drop table [kaoqin]
go
create table [kaoqin]([id] int,[userid] int,[userName] nvarchar(2),[cardid] int,[eventTime] datetime)
insert [kaoqin]
select 1,1,'A',1,'2014-01-02 08:00:00' union all
select 2,1,'A',1,'2014-01-02 18:00:00' union all
select 3,1,'A',1,'2014-01-02 19:00:00' union all
select 4,2,'B',2,'2014-01-02 10:00:00' union all
select 5,1,'A',1,'2014-01-03 8:00:00' union all
select 6,1,'A',1,'2014-01-03 18:50:00'
--------------生成数据--------------------------
DECLARE @startTime DATETIME
DECLARE @endtime DATETIME
SET @startTime='2014-01-01'
SET @endtime='2014-05-21'
SELECT a.id ,
b.userid ,
b.username ,
b.cardid ,
a.[eventTime] startTime ,
c.[eventTime] AS endTime
FROM [kaoqin] a
INNER JOIN ( SELECT [userid] ,
username ,
cardid ,