22,210
社区成员
发帖
与我相关
我的任务
分享
--月份可以加到如下位置,月份我这边假设是日期格式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)
没什么关系,你试下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 表示缺勤
--月份可以加到如下位置,月份我这边假设是日期格式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个字节的存储空间--月份可以加到如下位置,月份我这边假设是日期格式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)
试这个--月份可以加到如下位置,月份我这边假设是日期格式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)
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)