Merge Three Tables and get one output using SQL query

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

This 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 the three tables and get one output as batch candidate attendance report using a SQL query or possible way.

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            No Record           No Record           Present          3/1
  246           45      demo204          9888562344            No Record           Absent              Present          3/1

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, finally score Present will 1 value and out of 3 days.

Divyesh patel · Accepted Answer

I'm not sure how close to solution it but you may need dynamic pivot.

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

Date CTE

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



Dynamic Pivot

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


Demo

Merge Three Tables and get one output using SQL query

Answers (2)

Related Questions