Pivot table with reference to input parameters as StartDate and EndDate

Question

Conditions:

Input parameters with value:
@StartDate DATE='08/10/2019', @EndDate DATE='08/16/2019'

Need to pivot table tempTR along with input parameters date range @StartDate ='08/10/2019', @EndDate ='08/16/2019'

Required to compare tempTR tables StartDate and EndDate values with input parameters for matching RequestID. If Pivoted Date column matches then it has to be specified as 'Earned' else 'NA'

How is it possible to get below output ? Seeking for experts input.

Table Schema and Data

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')

Test script is good, but the question is not clear, if you could include same description (which column to be pivoted, which to be aggregated etc..) on your question that would make easier to understand and assist you. — Shekar Kola

mkRabbani mkRabbani · Accepted Answer · 2019-08-28T12:24:32

Can you please check this following option with Dynamic script execution-

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)

Output is-

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

Pivot table with reference to input parameters as StartDate and EndDate

1 Answers