sql-server - 枢轴表，引用输入参数为StartDate和EndDate

条件
带值的输入参数：
@起始日期：'08/10/2019'，
@结束日期：2019年8月16日
需要将table tentr与输入参数date range@startdate='08/10/2019'，
@结束日期='08/16/2019'
需要将试探表startdate和enddate值与用于匹配requestid的输入参数进行比较。如果数据透视列匹配，则必须将其指定为“已获得”否则为“不”
怎么可能低于产量？寻求专家的意见。
表架构和数据

CREATE TABLE [dbo].[tempTR](
       [RequestID] [nvarchar](50) ,
       [EID] INT,
       [EmployeeName] [nvarchar](4000) ,
       [JobTitle] [nvarchar](200) NULL,
       [StartDate] [Date] ,
       [EndDate] [Date]
       ) ON [PRIMARY]
  GO

INSERT [dbo].[tempTR] ([RequestID], [EID], [EmployeeName], [JobTitle], [StartDate], [EndDate]) VALUES (N'B754FCF4-F2FD-46EA-B6B4-088E17CC4CB8', 1, N'Admin Gobingoo', N'HR Admin',  N'2019-08-20', N'2019-08-30')
INSERT [dbo].[tempTR] ([RequestID], [EID], [EmployeeName], [JobTitle], [StartDate], [EndDate]) VALUES (N'56ED218C-99F2-416F-AF3D-1DE9F7FCE9AF', 200, N'Robin Craft', NULL,  N'2019-08-20', N'2019-08-30')
INSERT [dbo].[tempTR] ([RequestID], [EID], [EmployeeName], [JobTitle], [StartDate], [EndDate]) VALUES (N'EFD2849F-9A3B-4D90-9140-284BAAA44744', 201, N'William Shakespeare', NULL,  N'2019-08-20', N'2019-08-30')
INSERT [dbo].[tempTR] ([RequestID], [EID], [EmployeeName], [JobTitle], [StartDate], [EndDate]) VALUES (N'E287319C-16DA-473F-9F95-4D208B40E8AC', 202, N'Leo Tolstoy', N'zxcv', N'2019-08-20', N'2019-08-30')
INSERT [dbo].[tempTR] ([RequestID], [EID], [EmployeeName], [JobTitle], [StartDate], [EndDate]) VALUES (N'1C79E1AD-E4FA-48E5-95C7-5F8BC7AD4B8D', 204, N'J. R. R Tolkien', NULL, N'2019-08-20', N'2019-08-30')
INSERT [dbo].[tempTR] ([RequestID], [EID], [EmployeeName], [JobTitle], [StartDate], [EndDate]) VALUES (N'FF1536D3-062B-48F9-9363-5FED17991B38', 205, N'Edgar Allan Poe', NULL, N'2019-08-20', N'2019-08-30')
INSERT [dbo].[tempTR] ([RequestID], [EID], [EmployeeName], [JobTitle], [StartDate], [EndDate]) VALUES (N'29E4FD6C-0F19-4AB3-96D9-987E36EFB107', 207, N'Victor huge', NULL,  N'2019-08-20', N'2019-08-30')
INSERT [dbo].[tempTR] ([RequestID], [EID], [EmployeeName], [JobTitle], [StartDate], [EndDate]) VALUES (N'23A75F87-5AA0-48F1-BAA3-9C38C9768059', 200, N'Jennifer Huston', NULL, N'2019-08-12', N'2019-08-13')
INSERT [dbo].[tempTR] ([RequestID], [EID], [EmployeeName], [JobTitle], [StartDate], [EndDate]) VALUES (N'C78DE75B-E928-4DA2-A94F-995A1A2FB2F5', 1, N'Admin Gobingoo', N'HR Admin',  N'2019-08-10', N'2019-08-15')
INSERT [dbo].[tempTR] ([RequestID], [EID], [EmployeeName], [JobTitle], [StartDate], [EndDate]) VALUES ( N'8A802824-4E63-4005-BAD5-FD38807B8848', 201, N'Rocky Villa', NULL, N'2019-08-06', N'2019-08-11')

最佳答案

请在执行动态脚本时检查以下选项-

DECLARE @StartDate DATE='08/10/2019', @EndDate DATE='08/16/2019'
DECLARE @BuildCase VARCHAR(MAX) = ''
DECLARE @SqlString VARCHAR(MAX) = ''
DECLARE @LoopStartDate DATE=@StartDate

WHILE @LoopStartDate <= @EndDate
BEGIN
    SET @BuildCase =@BuildCase + ', CASE WHEN '''+CAST(@LoopStartDate AS VARCHAR)+''' BETWEEN StartDate AND EndDate THEN ''Earned'' ELSE ''NA'' END AS ['+LEFT(REPLACE(CONVERT(VARCHAR(11), @LoopStartDate, 106), ' ', '-') ,6)+'] '

    SET @LoopStartDate = DATEADD(DD,1,@LoopStartDate)
END

SET @SqlString= 'SELECT * '+@BuildCase+'
    FROM [dbo].[tempTR]
    WHERE EndDate BETWEEN  '''+CAST(@StartDate AS VARCHAR(200))+''' AND '''+CAST(@EndDate AS VARCHAR(200))+''''

EXEC(@SqlString)

输出为-

RequestID                               EID     EmployeeName    JobTitle    StartDate   EndDate     10-Aug  11-Aug  12-Aug  13-Aug  14-Aug  15-Aug  16-Aug
23A75F87-5AA0-48F1-BAA3-9C38C9768059    200     Jennifer Huston NULL        2019-08-12  2019-08-13  NA      NA      Earned  Earned  NA      NA      NA
C78DE75B-E928-4DA2-A94F-995A1A2FB2F5    1       Admin Gobingoo  HR Admin    2019-08-10  2019-08-15  Earned  Earned  Earned  Earned  Earned  Earned  NA
8A802824-4E63-4005-BAD5-FD38807B8848    201     Rocky Villa     NULL        2019-08-06  2019-08-11  Earned  Earned  NA      NA      NA      NA      NA

关于sql-server - 枢轴表，引用输入参数为StartDate和EndDate，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57691055/