sql - 显示月份的所有日期以及日期名称

Question

我有员工出勤表。我想获得特定月份的所有天数以及日期名称。我能够获取存在数据的记录，但我也想获取那些员工不存在或包含 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

Answer 1

就这样试试吧。我还没有添加所有的连接，请根据您的需要进行更新。

   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