创建月度出勤报告 [英] Create Monthly Attendance Report

问题描述

任务是生成月度报告

表：tblAttendance

Task is to generate a Monthly Report
Table: tblAttendance

Code Name AttendanceDate Status
---- ---- -------------- ------
A001 Abc Jan-01-2013 Present
B001 Xyz Jan-01-2013 Present
A001 Abc Jan-02-2013 Absent
B001 Xyz Jan-02-2013 Late
A001 Abc Jan-03-2013 Late
.
.

现在我必须按以下格式创建月度报告

Now I have to create Monthly Report in Format given below

Jan, 2013
Code Name 1 2 3 .. 29 30 31
---- ---- - - - .. -  -  -
A001 Abc  P A L .. L  P  A 
B001 Xyz  P L L .. L  P  A 
.
.

问题：没有任何想法来设计上述报告

Problem: Not getting any ideas to design the above report

推荐答案

为此，我建议您使用CROSSTAB查询。在SQL PIVOT表中。

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx [ ^ ]



http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx [ ^ ]



尝试自己。如果您有任何问题请随时询问。





---------------- ---------



您的PIVOT聚合问题。你可以使用一些行 MAX（[Status]）

for this I recommend you to use the CROSSTAB query. In SQL PIVOT table.
http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx[^]

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]

try your self. If you have any question feel free to ask.


-------------------------

Problem with your PIVOT aggregation. you can use something line MAX([Status])

SELECT [EmpCode], [ProjectName],[1] , [2],[3],[4],[5]
FROM 
(SELECT Day([AttendanceDate]) as d1, [EmpCode],[ProjectName],[AttendanceDate] , [Status]
FROM [tebs].[dbo].[View_Attendance]) p
PIVOT(
MAX([Status])
FOR d1 IN (  [1] , [2],[3],[4],[5]) ) AS pvt
ORDER BY 
pvt.[EmpCode],  pvt.[ProjectName];

这篇关于创建月度出勤报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！