Fill Absent text in empty place using pivot sql

[mohd mazhar]

Below is pivot query for my requiremnt.

Declare @cols NVARCHAR(Max)='';
;With log_date AS (
SELECT BatchStartDate as l_date FROM Table_Batch_Lookup
UNION ALL
SELECT DATEADD(dd, 1, l_date) FROM log_date AS ld , Table_Batch_Lookup AS tb WHERE ld.l_date<DATEADD(dd, -1, tb.BatchEndDate)
)
SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR,CONVERT(VARCHAR(10), l_Date, 111), 106) + ']','[' + CONVERT(NVARCHAR, l_Date, 106) + ']') FROM (SELECT DISTINCT CONVERT(VARCHAR(10), l_Date, 111) AS l_date FROM log_date) PV;

Declare @totalScore INT =len(@cols) - len(replace(@cols, ',', ''))

DECLARE @query NVARCHAR(MAX);
SET @query = 'SELECT t_info.TraineeID,t_batch.BatchId,t_info.Name,t_info.Mobile'+@cols+' FROM Table_TraineeInfo AS t_info Left JOIN
(SELECT * FROM
(
SELECT TraineeID,BatchId,Attendance,CONVERT(VARCHAR(10), l_Date, 111) AS l_date FROM Table_Attendance_Log
) x
PIVOT
(
MAX(Attendance)
FOR l_Date IN (' + right(@cols, len(@cols)-1)+ ')
) p ) AS f_pv ON t_info.TraineeID=f_pv.TraineeID JOIN Table_Batch_Lookup as t_batch ON t_batch.BatchId=t_info.BatchId

WHERE t_batch.BatchId=45;
' ;
EXEC SP_EXECUTESQL @query;
Below is my scrip table creation scripts with data. Table_TraineeInfo is candidate registeration details table, Table_Batch_Lookup is batch detail when batch start date and end date base on filling in pivot, Table_Attendance_Log is candidate attendance log.

CREATE TABLE [dbo].[Table_TraineeInfo](
[TraineeID] [int] NULL,
[BatchId] [int] NULL,
[Name] [varchar](max) NULL,
[Mobile] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (243, 45, N'demo201', N'9888562341')
INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (244, 45, N'demo202', N'9888562342')
INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (246, 45, N'demo204', N'9888562344')
INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (247, 45, N'demo205', N'9999999999')
/****** Object: Table [dbo].[Table_Batch_Lookup] Script Date: 12/15/2019 04:34:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_Batch_Lookup](
[BatchId] [int] NULL,
[BatchStartDate] [datetime] NULL,
[BatchEndDate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Table_Batch_Lookup] ([BatchId], [BatchStartDate], [BatchEndDate]) VALUES (45, CAST(0x0000AB2200000000 AS DateTime), CAST(0x0000AB25018B80D4 AS DateTime))
/****** Object: Table [dbo].[Table_Attendance_Log] Script Date: 12/15/2019 04:34:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_Attendance_Log](
[TraineeID] [int] NULL,
[BatchId] [int] NULL,
[Attendance] [varchar](10) NULL,
[l_date] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB220127842A AS DateTime))
INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB2301281C2A AS DateTime))
INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB240128E416 AS DateTime))
INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (244, 45, N'Present', CAST(0x0000AB24012A05AB AS DateTime))
INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (246, 45, N'Present', CAST(0x0000AB23012B245A AS DateTime))
INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (246, 45, N'Present', CAST(0x0000AB24012B245A AS DateTime))
My current output

Name Mobile

12/13/2019​

12/14/2019​

12/15/2019​

12/16/2019​

demo201

9888562341​

Present Present Present NULL
demo202

9888562342​

NULL NULL Present NULL
demo204

9888562344​

NULL Present Present NULL
demo205

9999999999​

NULL NULL NULL NULL



I want below output if date is less than or equal to today date fill empty vales to Absent(past date) and Future date should be like that no absent in 12/16/2019 is future date no need to fill absent.

Name Mobile

12/13/2019​

12/14/2019​

12/15/2019​

12/16/2019​

demo201

9888562341​

Present Present Present NULL
demo202

9888562342​

Absent Absent Present NULL
demo204

9888562344​

Absent Present Present NULL
demo205

9999999999​

Absent Absent Absent NULL

Continue reading...