none
Merge Three Tables and get one output using Sql Query RRS feed

  • Question



  • I have below candidate table details

    Table_TraineeInfo
    TraineeID     BatchId     Name           Mobile
    243             45      demo201        9888562341
    244             45      demo202        9888562342
    246             45      demo204        9888562344
    Below is my batch details of above candidate have reference id 45 in both common tables

    Table_Batch_Lookup
     BatchId           BatchStartDate                 BatchEndDate
      45             2019-11-27 00:00:00.000        2019-11-29 23:59:59.000
    Below is my Trainee attendance log table have common between Table_TraineeInfo and Table_Attendance_Log is TraineeID

    Table_Attendance_Log
    TraineeID    BatchId    Attendance       Date      
    243           45        Present     2019-11-27 17:55:56.513 
    243           45        Present     2019-11-28 17:58:06.220 
    243           45        Absent      2019-11-29 18:00:56.820 
    244           45        Present     2019-11-29 18:05:03.930 
    246           45        Absent      2019-11-28 18:09:08.567 
    246           45        Present     2019-11-29 18:09:08.567
    I want output like below Merge Three Tables and get one output as batch candidate attendance report using Sql Query or posible way.

    TraineeID    BatchId    Name             Mobile               2019-11-27          2019-11-28          2019-11-29       
      243           45      demo201          9888562341            Present             Present             Absent         
      244           45      demo202          9888562342            No Record           No Record           Present          
      246           45      demo204          9888562344            No Record           Absent              Present          
    I will explain above output first four columns will fill using Table_TraineeInfo and next dataes will fill base on BatchStartDate and BatchEndDate from Table_Batch_Lookup and Present and absent will base on Table_Attendance_Log no data availabe in attendacne list then fill no record. Please help to achieve my result
















    Monday, December 2, 2019 8:38 AM

Answers

  • Here is a solution. If it looks akward, well that is because you are asking for something which is akward for a relational database.

    DECLARE @batchid int = 45
    
    CREATE TABLE #temp (TraineeID    int      NOT NULL,
                        BatchId      int      NOT NULL,
                        Name         varchar(MAX) NULL,
                        Mobile       varchar(10)  NULL,
                        [0]          varchar(20)  NOT NULL,
                        [1]          varchar(20)  NOT NULL,
                        [2]          varchar(20)  NOT NULL,
                        [3]          varchar(20)  NOT NULL,
                        [4]          varchar(20)  NOT NULL,
                        [5]          varchar(20)  NOT NULL,
                        [6]          varchar(20)  NOT NULL,
                        [7]          varchar(20)  NOT NULL,
                        [8]          varchar(20)  NOT NULL,
                        [9]          varchar(20)  NOT NULL,
                        PRIMARY KEY (TraineeID, BatchId)
    )
    
    INSERT #temp 
       SELECT TI.TraineeID, TAL.BatchId, TI.Name, TI.Mobile,
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 0 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 1 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 2 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 3 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 4 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 5 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 6 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 7 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 8 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 9 THEN TAL.Attendance  END),
                     'No Record')
        FROM  Table_TraineeInfo TI
        JOIN  Table_Attendance_Log TAL ON TI.TraineeID = TAL.TraineeID
                                      AND TI.BatchId = TAL.BatchId
        JOIN  Table_Batch_Lookup BL ON TAL.BatchId = BL.BatchId
        WHERE  BL.BatchId = @batchid
        GROUP  BY TI.TraineeID, TAL.BatchId, TI.Name, TI.Mobile
    
    DECLARE @date date,
            @lastdate date,
            @stopdate date,
            @dayno int = 0,
            @colname sysname,
            @datestr  char(10),
            @sql nvarchar(MAX)
    SELECT @date = BatchStartDate, @lastdate = BatchEndDate
    FROM   Table_Batch_Lookup
    WHERE  BatchId = @batchid
    
    SELECT @stopdate = dateadd(DAY, 9, @date)
                              
    WHILE @date <= @stopdate
    BEGIN
       SELECT @colname = concat('#temp.', @dayno)
       IF @date <= @lastdate
       BEGIN 
          SELECT @datestr = convert(char(10), @date, 121)
          EXEC tempdb..sp_rename @colname, @datestr, 'COLUMN'
       END
       ELSE    
       BEGIN
          SELECT @sql = 'ALTER TABLE #temp DROP COLUMN ' + quotename(convert(varchar, @dayno))
          EXEC(@sql)
       END
    
       SELECT @date = dateadd(DAY, 1, @date), @dayno += 1
    END
    
    SELECT * FROM #temp
    go
    DROP TABLE #temp
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by mazhar khan123 Wednesday, December 11, 2019 6:57 AM
    Wednesday, December 4, 2019 10:34 PM
  • Here is a version of my script that handles your new test case:

    CREATE TABLE Table_TraineeInfo (TraineeID  int,BatchId int,Name varchar(max),Mobile varchar(10))
    INSERT INTO Table_TraineeInfo VALUES(243,             45  ,    'demo201' ,        '9888562341')
    INSERT INTO Table_TraineeInfo VALUES(244,             45  ,    'demo202' ,        '9888562342')
    INSERT INTO Table_TraineeInfo VALUES(246,             45  ,    'demo204' ,        '9888562344')
    INSERT INTO Table_TraineeInfo VALUES(247,             45  ,    'demo205' ,        '9888562345')
    
    CREATE TABLE Table_Attendance_Log (TraineeID INT,   BatchId INT,   Attendance VARCHAR(10)  ,   l_date DATETIME)    
    INSERT INTO Table_Attendance_Log VALUES (243,           45  ,      'Present'     ,'2019-11-27 17:55:56.513') 
    INSERT INTO Table_Attendance_Log VALUES (243,           45  ,      'Present'     ,'2019-11-28 17:58:06.220') 
    INSERT INTO Table_Attendance_Log VALUES (243,           45  ,      'Absent'      ,'2019-11-29 18:00:56.820') 
    INSERT INTO Table_Attendance_Log VALUES (244,           45  ,      'Present'     ,'2019-11-29 18:05:03.930') 
    INSERT INTO Table_Attendance_Log VALUES (246,           45  ,      'Absent'      ,'2019-11-28 18:09:08.567') 
    INSERT INTO Table_Attendance_Log VALUES (246,           45  ,      'Present'     ,'2019-11-29 18:09:08.567')
    
    CREATE TABLE Table_Batch_Lookup (BatchId   int    ,    BatchStartDate     DATETIME       ,     BatchEndDate DATETIME)
    INSERT INTO Table_Batch_Lookup VALUES( 45  ,           '2019-11-27 00:00:00.000',        '2019-11-29 23:59:59.000')
    
    
    SELECT * FROM Table_TraineeInfo
    SELECT * FROM Table_Attendance_Log
    SELECT * FROM Table_Batch_Lookup
    
    DECLARE @batchid int = 45
    
    CREATE TABLE #temp (TraineeID    int      NOT NULL,
                        BatchId      int      NOT NULL,
                        Name         varchar(MAX) NULL,
                        Mobile       varchar(10)  NULL,
                        [0]          varchar(20)  NOT NULL,
                        [1]          varchar(20)  NOT NULL,
                        [2]          varchar(20)  NOT NULL,
                        [3]          varchar(20)  NOT NULL,
                        [4]          varchar(20)  NOT NULL,
                        [5]          varchar(20)  NOT NULL,
                        [6]          varchar(20)  NOT NULL,
                        [7]          varchar(20)  NOT NULL,
                        [8]          varchar(20)  NOT NULL,
                        [9]          varchar(20)  NOT NULL,
                        PRIMARY KEY (TraineeID, BatchId)
    )
    
    INSERT #temp 
       SELECT TI.TraineeID, TI.BatchId, TI.Name, TI.Mobile,
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 0 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 1 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 2 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 3 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 4 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 5 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 6 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 7 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 8 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 9 THEN TAL.Attendance  END),
                     'No Record')
        FROM  Table_TraineeInfo TI
        LEFT  JOIN  Table_Attendance_Log TAL ON TI.TraineeID = TAL.TraineeID
                                      AND TI.BatchId = TAL.BatchId
        JOIN  Table_Batch_Lookup BL ON TI.BatchId = BL.BatchId
        WHERE  TI.BatchId = @batchid
        GROUP  BY TI.TraineeID, TI.BatchId, TI.Name, TI.Mobile
    
    DECLARE @date date,
            @lastdate date,
            @stopdate date,
            @dayno int = 0,
            @colname sysname,
            @datestr  char(10),
            @sql nvarchar(MAX)
    SELECT @date = BatchStartDate, @lastdate = BatchEndDate
    FROM   Table_Batch_Lookup
    WHERE  BatchId = @batchid
    
    SELECT @stopdate = dateadd(DAY, 9, @date)
                              
    WHILE @date <= @stopdate
    BEGIN
       SELECT @colname = concat('#temp.', @dayno)
       IF @date <= @lastdate
       BEGIN 
          SELECT @datestr = convert(char(10), @date, 121)
          EXEC tempdb..sp_rename @colname, @datestr, 'COLUMN'
       END
       ELSE    
       BEGIN
          SELECT @sql = 'ALTER TABLE #temp DROP COLUMN ' + quotename(convert(varchar, @dayno))
          EXEC(@sql)
       END
    
       SELECT @date = dateadd(DAY, 1, @date), @dayno += 1
    END
    
    SELECT * FROM #temp
    go
    DROP TABLE #temp
    
    
    go
    DROP TABLE Table_TraineeInfo, Table_Attendance_Log, Table_Batch_Lookup
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by mazhar khan123 Wednesday, December 11, 2019 6:57 AM
    Thursday, December 5, 2019 10:10 PM

All replies

  • Where do you intend to present this result?

    it is not clear from your post, but it seems that depending the length of the batch period, you will want a variable number of columns in the result. This is a non-relatational operation. The normal procedure would be to return one per TraineeID, BatchID and date, and then leave to the presentation layer to pivot the data.

    I would also recommend that you present your data as CREATE TABLE + INSERT statements, since that reduces the work for anyone who want to make a stab with helping you by crafting a solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, December 2, 2019 10:26 PM
  • CREATE TABLE Table_TraineeInfo (TraineeID  int,BatchId int,Name varchar(max),Mobile varchar(10))
    INSERT INTO Table_TraineeInfo VALUES(243,             45  ,    'demo201' ,        '9888562341')
    INSERT INTO Table_TraineeInfo VALUES(244,             45  ,    'demo202' ,        '9888562342')
    INSERT INTO Table_TraineeInfo VALUES(246,             45  ,    'demo204' ,        '9888562344')
    
    CREATE TABLE Table_Attendance_Log (TraineeID INT,   BatchId INT,   Attendance VARCHAR(10)  ,   l_date DATETIME)    
    INSERT INTO Table_Attendance_Log VALUES (243,           45  ,      'Present'     ,'2019-11-27 17:55:56.513') 
    INSERT INTO Table_Attendance_Log VALUES (243,           45  ,      'Present'     ,'2019-11-28 17:58:06.220') 
    INSERT INTO Table_Attendance_Log VALUES (243,           45  ,      'Absent'      ,'2019-11-29 18:00:56.820') 
    INSERT INTO Table_Attendance_Log VALUES (244,           45  ,      'Present'     ,'2019-11-29 18:05:03.930') 
    INSERT INTO Table_Attendance_Log VALUES (246,           45  ,      'Absent'      ,'2019-11-28 18:09:08.567') 
    INSERT INTO Table_Attendance_Log VALUES (246,           45  ,      'Present'     ,'2019-11-29 18:09:08.567')
    
    CREATE TABLE Table_Batch_Lookup (BatchId   int    ,    BatchStartDate     DATETIME       ,     BatchEndDate DATETIME)
    INSERT INTO Table_Batch_Lookup VALUES( 45  ,           '2019-11-27 00:00:00.000',        '2019-11-29 23:59:59.000')
    See batch id is unique in every table so base on batch id map the data with where condition. This is for single batch next batch also will come
    Wednesday, December 4, 2019 7:11 AM
  • Here is a solution. If it looks akward, well that is because you are asking for something which is akward for a relational database.

    DECLARE @batchid int = 45
    
    CREATE TABLE #temp (TraineeID    int      NOT NULL,
                        BatchId      int      NOT NULL,
                        Name         varchar(MAX) NULL,
                        Mobile       varchar(10)  NULL,
                        [0]          varchar(20)  NOT NULL,
                        [1]          varchar(20)  NOT NULL,
                        [2]          varchar(20)  NOT NULL,
                        [3]          varchar(20)  NOT NULL,
                        [4]          varchar(20)  NOT NULL,
                        [5]          varchar(20)  NOT NULL,
                        [6]          varchar(20)  NOT NULL,
                        [7]          varchar(20)  NOT NULL,
                        [8]          varchar(20)  NOT NULL,
                        [9]          varchar(20)  NOT NULL,
                        PRIMARY KEY (TraineeID, BatchId)
    )
    
    INSERT #temp 
       SELECT TI.TraineeID, TAL.BatchId, TI.Name, TI.Mobile,
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 0 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 1 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 2 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 3 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 4 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 5 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 6 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 7 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 8 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 9 THEN TAL.Attendance  END),
                     'No Record')
        FROM  Table_TraineeInfo TI
        JOIN  Table_Attendance_Log TAL ON TI.TraineeID = TAL.TraineeID
                                      AND TI.BatchId = TAL.BatchId
        JOIN  Table_Batch_Lookup BL ON TAL.BatchId = BL.BatchId
        WHERE  BL.BatchId = @batchid
        GROUP  BY TI.TraineeID, TAL.BatchId, TI.Name, TI.Mobile
    
    DECLARE @date date,
            @lastdate date,
            @stopdate date,
            @dayno int = 0,
            @colname sysname,
            @datestr  char(10),
            @sql nvarchar(MAX)
    SELECT @date = BatchStartDate, @lastdate = BatchEndDate
    FROM   Table_Batch_Lookup
    WHERE  BatchId = @batchid
    
    SELECT @stopdate = dateadd(DAY, 9, @date)
                              
    WHILE @date <= @stopdate
    BEGIN
       SELECT @colname = concat('#temp.', @dayno)
       IF @date <= @lastdate
       BEGIN 
          SELECT @datestr = convert(char(10), @date, 121)
          EXEC tempdb..sp_rename @colname, @datestr, 'COLUMN'
       END
       ELSE    
       BEGIN
          SELECT @sql = 'ALTER TABLE #temp DROP COLUMN ' + quotename(convert(varchar, @dayno))
          EXEC(@sql)
       END
    
       SELECT @date = dateadd(DAY, 1, @date), @dayno += 1
    END
    
    SELECT * FROM #temp
    go
    DROP TABLE #temp
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by mazhar khan123 Wednesday, December 11, 2019 6:57 AM
    Wednesday, December 4, 2019 10:34 PM
  • Thanks for your valuable replied, I tried below 

    CREATE TABLE Table_TraineeInfo (TraineeID  int,BatchId int,Name varchar(max),Mobile varchar(10))
    INSERT INTO Table_TraineeInfo VALUES(243,             45  ,    'demo201' ,        '9888562341')
    INSERT INTO Table_TraineeInfo VALUES(244,             45  ,    'demo202' ,        '9888562342')
    INSERT INTO Table_TraineeInfo VALUES(246,             45  ,    'demo204' ,        '9888562344')

    CREATE TABLE Table_Attendance_Log (TraineeID INT,   BatchId INT,   Attendance VARCHAR(10)  ,   l_date DATETIME)    
    INSERT INTO Table_Attendance_Log VALUES (243,           45  ,      'Present'     ,'2019-11-27 17:55:56.513') 
    INSERT INTO Table_Attendance_Log VALUES (243,           45  ,      'Present'     ,'2019-11-28 17:58:06.220') 
    INSERT INTO Table_Attendance_Log VALUES (243,           45  ,      'Absent'      ,'2019-11-29 18:00:56.820') 
    INSERT INTO Table_Attendance_Log VALUES (244,           45  ,      'Present'     ,'2019-11-29 18:05:03.930') 
    INSERT INTO Table_Attendance_Log VALUES (246,           45  ,      'Absent'      ,'2019-11-28 18:09:08.567') 
    INSERT INTO Table_Attendance_Log VALUES (246,           45  ,      'Present'     ,'2019-11-29 18:09:08.567')

    CREATE TABLE Table_Batch_Lookup (BatchId   int    ,    BatchStartDate     DATETIME       ,     BatchEndDate DATETIME)

    INSERT INTO Table_Batch_Lookup VALUES( 45  ,           '2019-11-27 00:00:00.000',        '2019-11-29 23:59:59.000')

    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, ',', ''))
    CREATE TABLE #scoreTable (TraineeID int,Score Varchar(max))
    INSERT INTO #scoreTable SELECT TraineeID,(CAST (@totalScore AS VARCHAR(10)) +'/'+CAST (SUM(CASE WHEN Attendance='Present' THEN 1 ELSE 0 END) AS VARCHAR(10)))AS Score from Table_Attendance_Log GROUP BY TraineeID;
    --SELECT * from #scoreTable
    DECLARE @query NVARCHAR(MAX); 
    SET @query = 'SELECT t_info.TraineeID,t_batch.BatchId,t_info.Name,t_info.Mobile'+@cols+' ,s.Score FROM  Table_TraineeInfo AS t_info  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
    JOIN #scoreTable AS s ON t_info.TraineeID=s.TraineeID
    WHERE t_batch.BatchId=45;
                ' ;    
    EXEC SP_EXECUTESQL @query;

    output

    TraineeID BatchId Name Mobile 2019/11/27 2019/11/28 2019/11/29 Score
    243 45 demo201 9888562341 Present Present Absent 3/2
    244 45 demo202 9888562342 Present 3/1
    246 45 demo204 9888562344 Absent Present 3/1






    But i have problem here is that if i added below one record in Table_traineeInfo this is not vi-sibling in output 

    INSERT INTO Table_TraineeInfo VALUES(247,             45  ,    'demo205' ,        '9888562345')

    then i wrote left join then above record also coming but I need one more score column out of days how many present new feature added to my question currently every think is working but when added left join then score feature is not working please help above url

    SET @query = 'SELECT t_info.TraineeID,t_batch.BatchId,t_info.Name,t_info.Mobile'+@cols+' ,s.Score 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
    JOIN #scoreTable AS s ON t_info.TraineeID=s.TraineeID
    WHERE t_batch.BatchId=45;
                ' ;    





    Thursday, December 5, 2019 8:00 AM
  • Here is a version of my script that handles your new test case:

    CREATE TABLE Table_TraineeInfo (TraineeID  int,BatchId int,Name varchar(max),Mobile varchar(10))
    INSERT INTO Table_TraineeInfo VALUES(243,             45  ,    'demo201' ,        '9888562341')
    INSERT INTO Table_TraineeInfo VALUES(244,             45  ,    'demo202' ,        '9888562342')
    INSERT INTO Table_TraineeInfo VALUES(246,             45  ,    'demo204' ,        '9888562344')
    INSERT INTO Table_TraineeInfo VALUES(247,             45  ,    'demo205' ,        '9888562345')
    
    CREATE TABLE Table_Attendance_Log (TraineeID INT,   BatchId INT,   Attendance VARCHAR(10)  ,   l_date DATETIME)    
    INSERT INTO Table_Attendance_Log VALUES (243,           45  ,      'Present'     ,'2019-11-27 17:55:56.513') 
    INSERT INTO Table_Attendance_Log VALUES (243,           45  ,      'Present'     ,'2019-11-28 17:58:06.220') 
    INSERT INTO Table_Attendance_Log VALUES (243,           45  ,      'Absent'      ,'2019-11-29 18:00:56.820') 
    INSERT INTO Table_Attendance_Log VALUES (244,           45  ,      'Present'     ,'2019-11-29 18:05:03.930') 
    INSERT INTO Table_Attendance_Log VALUES (246,           45  ,      'Absent'      ,'2019-11-28 18:09:08.567') 
    INSERT INTO Table_Attendance_Log VALUES (246,           45  ,      'Present'     ,'2019-11-29 18:09:08.567')
    
    CREATE TABLE Table_Batch_Lookup (BatchId   int    ,    BatchStartDate     DATETIME       ,     BatchEndDate DATETIME)
    INSERT INTO Table_Batch_Lookup VALUES( 45  ,           '2019-11-27 00:00:00.000',        '2019-11-29 23:59:59.000')
    
    
    SELECT * FROM Table_TraineeInfo
    SELECT * FROM Table_Attendance_Log
    SELECT * FROM Table_Batch_Lookup
    
    DECLARE @batchid int = 45
    
    CREATE TABLE #temp (TraineeID    int      NOT NULL,
                        BatchId      int      NOT NULL,
                        Name         varchar(MAX) NULL,
                        Mobile       varchar(10)  NULL,
                        [0]          varchar(20)  NOT NULL,
                        [1]          varchar(20)  NOT NULL,
                        [2]          varchar(20)  NOT NULL,
                        [3]          varchar(20)  NOT NULL,
                        [4]          varchar(20)  NOT NULL,
                        [5]          varchar(20)  NOT NULL,
                        [6]          varchar(20)  NOT NULL,
                        [7]          varchar(20)  NOT NULL,
                        [8]          varchar(20)  NOT NULL,
                        [9]          varchar(20)  NOT NULL,
                        PRIMARY KEY (TraineeID, BatchId)
    )
    
    INSERT #temp 
       SELECT TI.TraineeID, TI.BatchId, TI.Name, TI.Mobile,
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 0 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 1 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 2 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 3 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 4 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 5 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 6 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 7 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 8 THEN TAL.Attendance  END),
                     'No Record'),                            
              isnull(MIN(CASE datediff(day, BL.BatchStartDate, TAL.l_date) WHEN 9 THEN TAL.Attendance  END),
                     'No Record')
        FROM  Table_TraineeInfo TI
        LEFT  JOIN  Table_Attendance_Log TAL ON TI.TraineeID = TAL.TraineeID
                                      AND TI.BatchId = TAL.BatchId
        JOIN  Table_Batch_Lookup BL ON TI.BatchId = BL.BatchId
        WHERE  TI.BatchId = @batchid
        GROUP  BY TI.TraineeID, TI.BatchId, TI.Name, TI.Mobile
    
    DECLARE @date date,
            @lastdate date,
            @stopdate date,
            @dayno int = 0,
            @colname sysname,
            @datestr  char(10),
            @sql nvarchar(MAX)
    SELECT @date = BatchStartDate, @lastdate = BatchEndDate
    FROM   Table_Batch_Lookup
    WHERE  BatchId = @batchid
    
    SELECT @stopdate = dateadd(DAY, 9, @date)
                              
    WHILE @date <= @stopdate
    BEGIN
       SELECT @colname = concat('#temp.', @dayno)
       IF @date <= @lastdate
       BEGIN 
          SELECT @datestr = convert(char(10), @date, 121)
          EXEC tempdb..sp_rename @colname, @datestr, 'COLUMN'
       END
       ELSE    
       BEGIN
          SELECT @sql = 'ALTER TABLE #temp DROP COLUMN ' + quotename(convert(varchar, @dayno))
          EXEC(@sql)
       END
    
       SELECT @date = dateadd(DAY, 1, @date), @dayno += 1
    END
    
    SELECT * FROM #temp
    go
    DROP TABLE #temp
    
    
    go
    DROP TABLE Table_TraineeInfo, Table_Attendance_Log, Table_Batch_Lookup
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by mazhar khan123 Wednesday, December 11, 2019 6:57 AM
    Thursday, December 5, 2019 10:10 PM