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.

So what and where is the problem? You surely have tried some query that failed to produce what you need. — Markus Deibel, Dec 02 '19 at 07:09
Question is not about Merging. I bielive you are fairly accustomed with simple inner join. You issue is to "rotate" the date value into column. I will recommend starting there https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql. — xdtTransform, Dec 02 '19 at 07:19
Yes, any sample related my output achieve or any query . @mkRabbani — Mr doubt, Dec 02 '19 at 07:20
there is two table output only rotation how to achieve absent and present. @xdtTransform — Mr doubt, Dec 02 '19 at 07:23

Divyesh patel · Accepted Answer · 2019-12-02T12:42:59.687

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<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;

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

batch start date and batch end date should come from this table Table_Batch_Lookup, you have miss one table batch lookup table. @FrustratedDeveloper — Mr doubt, Dec 02 '19 at 10:21
Please edit and update the answer you are almost to answer. @FrustratedDeveloper — Mr doubt, Dec 02 '19 at 10:30
i think this line of code you have to bring date from table_batch_lookup: dont bring date from log table 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 Table_Attendance_Log) PV — Mr doubt, Dec 02 '19 at 10:47
And If you want date from `table_batch_lookup` then you may need `CTE` or `LOOP` AS I know — Divyesh patel, Dec 02 '19 at 10:51
no no some time one user attendance available, base on batch startdate and BatchEnddate cols value should fill. @FrustratedDeveloper — Mr doubt, Dec 02 '19 at 10:52
Ya one example is there: https://dba.stackexchange.com/questions/25809/return-a-column-per-date-in-a-range but i am not getting answer. @FrustratedDeveloper — Mr doubt, Dec 02 '19 at 10:53
every 3 table can you pass where condition BatchId=45 because base on batchid only all data will fill, hope understand other batches also will be there. @FrustratedDeveloper — Mr doubt, Dec 02 '19 at 11:59
Score part is not visibling in both answer and link? also where condition also not showing. @FrustratedDeveloper — Mr doubt, Dec 02 '19 at 12:09
i have inserted one record like: INSERT INTO Table_TraineeInfo VALUES(247, 45 , 'demo205' , '9888562345') but this record is not visibling in output because this record attendance not availble in attendance log table, i want to see this record also. @FrustratedDeveloper — Mr doubt, Dec 03 '19 at 10:02

score 0 · Answer 2 · edited Dec 02 '19 at 09:59

0

Impossible to create one query with different column count and column names.

The workaround is creating a script for dynamic SQL query forming. Or I can write a query with columns named [day1],[day2],...,[dayN]...

if between BatchEndDate and BatchStartDate always the same day's count.

edited Dec 02 '19 at 09:59

THE LIFE-TIME LEARNER

1,476
1
8
18

answered Dec 02 '19 at 07:27

Смирнов Евгений

1

Remove the score column, can i get after remove score column. @Смирнов Евгений – Mr doubt Dec 02 '19 at 07:29

Merge Three Tables and get one output using SQL query

2 Answers2