none
Fill Absent text in empty place using pivot sql RRS feed

  • Question


  • 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

    Saturday, December 14, 2019 11:44 PM

All replies

  • Hello,

    You would be better off asking in the Transact SQL forum as the answer to your question is not C# but SQL.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, December 15, 2019 2:54 AM
  • Hi,

    Please check following script .

    Declare @cols NVARCHAR(Max)='';
    Declare @cols1 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;
         
    	 ;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, -2, tb.BatchEndDate)
        )
    	SELECT @cols1 = COALESCE (@cols1 + ',isnull([' + CONVERT(NVARCHAR,CONVERT(VARCHAR(10), l_Date, 111), 106) + '],''Absent'')','isnull([' + CONVERT(NVARCHAR, l_Date, 106) + '],''Absent'')') 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'+@cols1+'  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;
                    ' ;    
    				print (@query)
        EXEC SP_EXECUTESQL @query;
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 25, 2019 8:18 AM