SQL 查询考勤的问题！请高手进 sql2000 数据库

Question

本帖最后由 aqiang566971 于 2014-12-03 19:17:58 编辑

Answer 1

DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.* FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId,CONVERT(VARCHAR(7),add_date,120)add_month'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<32
SET @SQL=@SQL+'FROM Ribaobiao2 GROUP BY UserId,CONVERT(VARCHAR(7),add_date,120))T2 ON T1.UserId=T2.UserId'
EXEC(@SQL)

Answer 2

请问这个条件怎么加到 Sys_Userinfo T1 表的筛选上
t1.UserDept='1014' OR t1.UserDept='1015' OR t1.UserDept='1016' OR t1.UserDept='1017' OR t1.UserDept='1019' OR t1.UserDept='1020'
还有就是在Ribaobiao2 表上加上 月份的赛选条件呢

Answer 3

--月份可以加到如下位置，月份我这边假设是日期格式2014-12-01
--不是也可以，再一步转换
DECLARE @MONTH DATETIME
SET @MONTH='2014-11-01'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.* FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId,CONVERT(VARCHAR(7),add_date,120)add_month'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<=DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH)))--列数随月份变化
SET @SQL=@SQL+'FROM Ribaobiao2'
--月份条件在这过滤最好
--查询列不进行计算
+' WHERE add_date>='''+CAST(@MONTH AS VARCHAR(30))+'''AND add_date<'''+CAST(DATEADD(MONTH,1,@MONTH)AS VARCHAR(30))
+''' GROUP BY UserId,CONVERT(VARCHAR(7),add_date,120))T2 ON T1.UserId=T2.UserId'SET @SQL=@SQL+' WHERE T1.UserDept IN(''1014'',''1015'',''1016'',''1017'',''1019'',''1020'')'
--UserDept可以加到上面，T1表的条件都可以加到上面
--都是常量，可以直接用IN
EXEC(@SQL)

Answer 4

大侠，为什么我把 add_date 换成 bfdate 就不好使了呢，bfdate（nvarchar 50） 是这个类型 数据格式是 2014-12-01
 add_date是 （datetime 8） 这类型 时间格式是 2014-12-01 8:34请大侠指教

Answer 5

如果是这样，可以这样，既然你只查一个月份，我把调优了一下--月份可以加到如下位置，月份我这边假设是日期格式2014-12-01
--不是也可以，再一步转换
DECLARE @MONTH DATETIME
SET @MONTH='2014-12-01'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.* FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<=DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH)))--列数随月份变化
SET @SQL=@SQL+'FROM Ribaobiao2'
--月份条件在这过滤最好
--查询列不进行计算
    +' WHERE add_date>='''+CONVERT(VARCHAR(10),@MONTH,120)+'''AND add_date<'''+CONVERT(VARCHAR(10),DATEADD(MONTH,1,@MONTH),120)
    +''' GROUP BY UserId)T2 ON T1.UserId=T2.UserId'
 
SET @SQL=@SQL+' WHERE T1.UserDept IN(''1014'',''1015'',''1016'',''1017'',''1019'',''1020'')'
--UserDept可以加到上面，T1表的条件都可以加到上面
--都是常量，可以直接用IN
EXEC(@SQL)然后，建议日期还是存日期格式吧，精确到3.33毫秒，而且只需要8个字节的存储空间

Answer 6

Ky_min 的写法已经很好了，但你也可以这样写：
create table Sys_userinfo
(
Username varchar(10),
add_date varchar(10)
)
insert into Sys_userinfo
select '张三','2014-12-01' union all
select '张三','2014-12-02' union all
select '李四','2014-12-01' union all
select '王二','2014-12-02' select Username,
sum(case when datepart(dd,add_date)=1 then 1 
--  当然，你可以从这里排除节假日的判断 ，when 节假日 then -1  *
 else 0 end) as '1',
sum(case when datepart(dd,add_date)=2 then 1 else 0 end) as '2',
sum(case when datepart(dd,add_date)=3 then 1 else 0 end) as '3'
from Sys_Userinfo as u
join Ribaobiao2 as r on r.UserId=u.UserId
group by Username
-- 1 表示出勤，0 表示缺勤

Answer 7

--月份可以加到如下位置，月份我这边假设是日期格式2014-12-01
--不是也可以，再一步转换
DECLARE @MONTH DATETIME
SET @MONTH='2014-12-01'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.*,'+CAST(DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH))) AS VARCHAR(10))+'-[合计出勤天数] [合计缺勤天数] FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<=DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH)))--列数随月份变化
SET @SQL=@SQL+',ISNULL(SUM(1),0)[合计出勤天数]'
SET @SQL=@SQL+'FROM Ribaobiao2'
--月份条件在这过滤最好
--查询列不进行计算
    +' WHERE add_date>='''+CONVERT(VARCHAR(10),@MONTH,120)+'''AND add_date<'''+CONVERT(VARCHAR(10),DATEADD(MONTH,1,@MONTH),120)
    +''' GROUP BY UserId)T2 ON T1.UserId=T2.UserId'
 
SET @SQL=@SQL+' WHERE T1.UserDept IN(''1014'',''1015'',''1016'',''1017'',''1019'',''1020'')'
--UserDept可以加到上面，T1表的条件都可以加到上面
--都是常量，可以直接用IN
EXEC(@SQL)没什么关系，你试下

Answer 8

--月份可以加到如下位置，月份我这边假设是日期格式2014-12-01
--不是也可以，再一步转换
DECLARE @MONTH DATETIME
SET @MONTH='2014-12-01'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.*,'+CAST(DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH))) AS VARCHAR(10))+'-[合计出勤天数] [合计缺勤天数] FROM Sys_Userinfo T1 LEFT JOIN(SELECT UserId'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(add_date)='+CAST(number AS VARCHAR(2))+' THEN''出勤''END),''缺勤'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<=DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH)))--列数随月份变化
SET @SQL=@SQL+',ISNULL(COUNT(DISTINCT add_date),0)[合计出勤天数]'
SET @SQL=@SQL+'FROM Ribaobiao2'
--月份条件在这过滤最好
--查询列不进行计算
    +' WHERE add_date>='''+CONVERT(VARCHAR(10),@MONTH,120)+'''AND add_date<'''+CONVERT(VARCHAR(10),DATEADD(MONTH,1,@MONTH),120)
    +''' GROUP BY UserId)T2 ON T1.UserId=T2.UserId'
 
SET @SQL=@SQL+' WHERE T1.UserDept IN(''1014'',''1015'',''1016'',''1017'',''1019'',''1020'')'
--UserDept可以加到上面，T1表的条件都可以加到上面
--都是常量，可以直接用IN
EXEC(@SQL)试这个

Answer 9

这是个典型的交叉报表，用SQL生成动态列的结果集比较麻烦，代码很长看着也很乱。但实际上，这类报表对于任何一种支持交叉表且有表达式计算能力的报表工具都较容易完成，比如使用润乾集算报表可以这样做：（直接使用那个表作为数据源，即select * from ribaobiao2）。         通过A2横向扩展出31列，这个也可以根据参数指定的月份动态扩展（28、29、30或31），在B2中按照出勤日期的日关联上面的扩展日，最后在B2的显示值表达式中写判断：if(value()==null,"缺勤","出勤")即可。
        你能快速得到这样的结果：
               报表也可以发布到web上。