sql - 显示月份的所有日期以及日期名称
问题描述
我有员工出勤表。我想获取特定月份的所有日期以及日期名称。我能够获取存在数据的记录,但我也想获取那些员工不存在或包含 NULL 的日期。
DROP TABLE [Attendance];
DROP TABLE [Employee];
CREATE TABLE [Employee]
(
[ID] Int NOT NULL PRIMARY KEY,
[FirstName] Varchar(25)
)
INSERT INTO [Employee] VALUES (1, 'Asim');
CREATE TABLE [Attendance]
(
ID Int NOT NULL PRIMARY KEY,
[Date] Date,
Status Varchar(25),
[EmpCode] Int,
CONSTRAINT FK_EmpCode FOREIGN KEY ([EmpCode])
REFERENCES [Employee](ID)
)
INSERT INTO [Attendance] VALUES (1, '2018-05-02', 'Present', 1);
INSERT INTO [Attendance] VALUES (2, '2018-05-03', 'Present', 1);
INSERT INTO [Attendance] VALUES (3, '2018-05-04', 'Present', 1);
INSERT INTO [Attendance] VALUES (4, '2018-05-07', 'Present', 1);
INSERT INTO [Attendance] VALUES (5, '2018-05-09', 'Present', 1);
INSERT INTO [Attendance] VALUES (6, '2018-05-10', 'Present', 1);
INSERT INTO [Attendance] VALUES (7, '2018-05-11', 'Present', 1);
INSERT INTO [Attendance] VALUES (8, '2018-05-14', 'Present', 1);
INSERT INTO [Attendance] VALUES (9, '2018-05-15', 'Present', 1);
INSERT INTO [Attendance] VALUES (10, '2018-05-16', 'Present', 1);
DECLARE @month AS INT = 5
DECLARE @Year AS INT = 2018
;WITH N(N)AS
(
SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N) AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
SELECT N DAYNUMBER, datefromparts(@year,@month,N) DATE, DATENAME(weekday, datefromparts(@year,@month,N)) DATEDAY,
[Info].[FirstName],
Att.date [Date],
DATENAME(month, att.date) AS 'Month Name'
FROM
[Employee] [Info], [Attendance] [Att], tally
WHERE
info.id = att.empcode and
N <= day(EOMONTH(datefromparts(@year,@month,1)))
AND DATENAME(month, Att.Date) = 'May'
AND datefromparts(@year,@month,N) = att.date
GROUP BY
[Info].[FirstName], tally.N,
[Att].[Date]
ORDER BY [Att].[Date]
这是我到目前为止所尝试的。任何人都可以看看并指导我正确的方向吗?
虽然我希望按顺序显示缺失的日期,但结果目前看起来像这样。
DAYNUMBER DATE DATEDAY FirstName Date Month Name
-------------------- ---------- ------------------------------ ------------------------- ---------- ------------------------------
2 2018-05-02 Wednesday Asim 2018-05-02 May
3 2018-05-03 Thursday Asim 2018-05-03 May
4 2018-05-04 Friday Asim 2018-05-04 May
7 2018-05-07 Monday Asim 2018-05-07 May
9 2018-05-09 Wednesday Asim 2018-05-09 May
10 2018-05-10 Thursday Asim 2018-05-10 May
11 2018-05-11 Friday Asim 2018-05-11 May
14 2018-05-14 Monday Asim 2018-05-14 May
15 2018-05-15 Tuesday Asim 2018-05-15 May
16 2018-05-16 Wednesday Asim 2018-05-16 May
解决方案
就这样试试吧。我还没有添加所有的连接,请根据您的需要进行更新。
DECLARE @Month AS VARCHAR(2) = '05'
DECLARE @Year AS VARCHAR(4) = '2018'
DECLARE @StartDate DATE = @month+'/01/'+@Year
DECLARE @EndDate DATE = EOMONTH(@StartDate)
DECLARE @tblDAte AS TABLE (mydate DATE, Monthnam VARCHAR(100))
INSERT INTO @tblDate (mydate,monthnam)
SELECT DATEADD(DAY, nbr - 1, @StartDate), DATENAME(weekday,DATEADD(DAY, nbr - 1, @StartDate))
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
FROM sys.columns c
) nbrs
WHERE nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
SELECT *
FROM @tblDate tmp
LEFT OUTER JOIN Attendance at ON at.Date = tmp.mydate
LEFT OUTER JOIN Employee emp ON emp.Id = at.EmpCode
推荐阅读
- angular - rxjs5 在依赖项中注册时 ng test 命令出现 rxjs 6 错误
- javascript - 为什么 promise 的被拒绝参数在其范围之外起作用?
- javascript - 如何在 wordpress 上包含外部代码/库 javascript?
- maatwebsite-excel - 如何将二维数组数据设置为范围?
- java - 我的代码有什么问题,请帮助我
- javascript - JQuery Datatable - 如有必要,按多列排序
- python-3.x - 如何从子图中的指定子图中选择图例句柄和标签?
- python - 根据两个字典对列表进行排序
- android - Retrofit 2 带数据的多部分图像上传
- ios - 带有手势识别器的 UITextView - 有条件地向前触摸到父视图