如何在SQL中创建关注注册报告 [英] How to create a attendence register report in SQL
问题描述
我有一张桌子表1，表格数据如下所示
attdate inime outtime workinghours empid
03-01-17 08:30 20:00 12 1
03-02-17 08:30 20:00 12 1
03-03-17 08:30 20:00 12 1
03-04-17 08:30 18:30 10 1
03-06-17 08:30 20:10 12 1
03月3日星期五是星期三的星期三，这就是为什么我不进入表中的记录
i有另一张表2，记录如下
empid leavedate
1 03-10-17
1 03-11-17
我的预期结果如下所示我想要，如果工作时间> 8表示P，如果表2中的员工表示L否则AB
empid 1（03-01-17）2 3 4 5 ... 10 11
1 p p p p L L
1表示（03-01-17）第1天为行军
2表示第2天行军
我尝试过：
i尝试如下
attdate present
03-01-14 p
03-02-17 p
i have a table nemed table 1,table data is showed below
attdate inime outtime workedhours empid
03-01-17 08:30 20:00 12 1
03-02-17 08:30 20:00 12 1
03-03-17 08:30 20:00 12 1
03-04-17 08:30 18:30 10 1
03-06-17 08:30 20:10 12 1
03-05-17 date is sunday for march month thats why im not entering into that record in table
i have another table table 2,record shown below
empid leavedate
1 03-10-17
1 03-11-17
my expected result is like below i want,if worked hours >8 means P ,if employee present in table 2 means L otherwise AB
empid 1(03-01-17) 2 3 4 5... 10 11
1 p p p p p L L
1 means(03-01-17) day 1 for march
2 means day 2 for march
What I have tried:
i have tried like below
attdate present
03-01-14 p
03-02-17 p
推荐答案
您正在寻找的术语是PIVOT - 本文中有一个很好的简单示例在SQL查询中使用Pivot的简单方法 [ ^ ]
您还需要了解 CASE（Transact-SQL） [ ^ ]
我不打算给你完整的查询，因为我相信这是作业，但我会真的，非常善良，并给你几个P部分IVOT ...因为你希望列是1月，2日，3等的日子，所以列名必须用方括号[]包围，如下所示：
The term you are looking for is PIVOT - there is a nice easy example in this article Simple Way To Use Pivot In SQL Query[^]
You will also need to know about CASE (Transact-SQL)[^]
I'm not going to give you the full query because I believe this to be homework but I am going to be really, really kind and give you a couple of parts of the PIVOT ... because you want the columns to be the days of the month 1, 2, 3, etc the column names must be surrounded by square brackets [] like this:
([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])这可能很难输入。请注意，这基本上适用于任何一个月 - 你只会在2月，4月等结尾处获得一些空白栏目。
另一个痛苦的一点就是一些这些列将包含NULL值。您可以在表示层中处理此问题，但如果您想在SQL中处理它，那也非常痛苦。这就是它可能的样子：
and that can be a pain to type out. Note that this will essentially work for any month - you'll just get some blank columns at the end for February, April etc.
The other painful bit is that some of those columns are going to contain NULL values. You can handle this in your presentation layer, but if you want to handle it in SQL it is also very painful. This is what it could like:
ISNULL([1],'-') AS [1],ISNULL([2],'-') AS [2],ISNULL([3],'-') AS [3],ISNULL([4],'-') AS [4],ISNULL([5],'-') AS [5],
ISNULL([6],'-') AS [6],ISNULL([7],'-') AS [7],ISNULL([8],'-') AS [8],ISNULL([9],'-') AS [9],ISNULL([10],'-') AS [10],
ISNULL([11],'-') AS [11],ISNULL([12],'-') AS [12],ISNULL([13],'-') AS [13],ISNULL([14],'-') AS [14],ISNULL([15],'-') AS [15],
ISNULL([16],'-') AS [16],ISNULL([17],'-') AS [17],ISNULL([18],'-') AS [18],ISNULL([19],'-') AS [19],ISNULL([20],'-') AS [20],
ISNULL([21],'-') AS [21],ISNULL([22],'-') AS [22],ISNULL([23],'-') AS [23],ISNULL([24],'-') AS [24],ISNULL([25],'-') AS [25],
ISNULL([26],'-') AS [26],ISNULL([27],'-') AS [27],ISNULL([28],'-') AS [28],ISNULL([29],'-') AS [29],ISNULL([30],'-') AS [30],ISNULL([31],'-') AS [31]
然而，结果就像......
However, the results come out like ...
Emp 1 2 3 4 5 6
1 P P P P - P ... etc
这篇关于如何在SQL中创建关注注册报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
