如何根据条件循环列数据和求和值 [英] How to loop columns data and sum values based on condition
问题描述
嗨朋友
我有考勤表,我有专栏:EmpCode,EmpName,1,2,3 ....... upt0 31(我正在倾销excel表的出席情况。
Ex:
EmpCode EmpName 1 2 3
1013 Ch.Krishna Mohan A PP
现在我要找的是
来自1的循环列到31,找到现在的天数和绝对天数。
o / p:
EmpCode EmpName No.ofpresentdays No .ofAbscentDays
1013 Ch.Krishna Mohan 2 1
如何在sqlserver中实现这个? br />
我尝试过:
SELECT'select'
+ QUOTENAME(c.name)
+',来自[dbo]的计数(*)。[EmpAttendance] group by';
+ QUOTENAME(c.name)
来自sys.columns c
WHERE c.object_id = OBJECT_ID (dbo.EmpAttendance;)
选择[1],从[dbo]计算(*)。[EmpAttendance]其中Empcode = 1020 group by [1]
请试试这个
SELECT
EmpCode,EmpName,[No.ofpresentdays],[ No.ofAbscentDays]
FROM
dbo.attendance T
CROSS APPLY(
SELECT Count(*)
FROM(VALUES([ 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 ]))C(Val)
WHERE Val = ' P'
)A([发布日期])
交叉申请(
SELECT Count(*)
FROM(VALUES([ 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 ]))C(Val)
WHERE Val = ' A'
)P([No.ofAbscentDays])
hi friends
I have Attendance table in that I have Columns: EmpCode,EmpName,1,2,3.......upt0 31(days).I am dumping attendance from excel sheet.
Ex:
EmpCode EmpName 1 2 3
1013 Ch.Krishna Mohan A P P
Now what I am looking for is
loop columns from 1 to 31 and find no.of present days and no.of abscent days count.
o/p:
EmpCode EmpName No.ofpresentdays No.ofAbscentDays
1013 Ch.Krishna Mohan 2 1
how to acheive this in sqlserver??
What I have tried:
SELECT 'select '
+ QUOTENAME(c.name)
+ ',count(*) from [dbo].[EmpAttendance] group by ';
+ QUOTENAME(c.name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(dbo.EmpAttendance;)
select [1],count(*) from [dbo].[EmpAttendance] where Empcode=1020 group by [1]
Please try this
SELECT EmpCode,EmpName,[No.ofpresentdays],[No.ofAbscentDays] FROM dbo.attendance T CROSS APPLY ( SELECT Count(*) FROM (VALUES ([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])) C (Val) WHERE Val = 'P' ) A ([No.ofpresentdays]) CROSS APPLY ( SELECT Count(*) FROM (VALUES ([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])) C (Val) WHERE Val = 'A' ) P ([No.ofAbscentDays])
这篇关于如何根据条件循环列数据和求和值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!