CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mr doubt
Mr doubt

Reputation: 65

Get today's attendance base on two tables

Below is my registration: Table_Trainee_Corporate_Info

Corporation_Trainee_Id  TraineeId   BatchId     Name        Mobile
 261                    COP000261   63          ankitha1    9555962321
 262                    COP000262   63          ankitha2    9555962322
 264                    COP000264   63          ankitha4    9555962324
 265                    COP000265   63          ankitha5    9555962325
 266                    COP000266   63          ankitha6    9555962326
 267                    COP000267   63          ankitha7    9555962327
 268                    COP000268   63          ankitha8    9555962328
 263                    COP000263   63          ankitha3    9555962323

Below is my daily attendance log table: Table_Trainee_Attendance_Info

 Trainee_atten_logId          Corporation_Trainee_Id     BatchId    Attendance  AttendanceDate
     1                            261                      63         Present   12-09-2019 15:31
     2                            262                      63         Present   12-09-2019 15:31
     3                            264                      63         Present   12-09-2019 15:31
     4                            265                      63         Present   12-09-2019 15:31
     5                            261                      63         Present   12-10-2019 15:34

I want an output like I've shown below; how many are present on today dates and how many nulls on todays date based on Corporation_Trainee_Id of both tables.

 Corporation_Trainee_Id     Name        Mobile         Attendance
         261                ankitha1    9555962321      Present
         262                ankitha2    9555962322      NULL
         263                ankitha3    9555962322      NULL
         264                ankitha4    9555962323      NULL
         265                ankitha5    9555962323      NULL
         266                ankitha6    9555962324      NULL
         267                ankitha7    9555962324      NULL
         268                ankitha8    9555962325      NULL

Upvotes: 1

Views: 45

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

You need a left join here:

SELECT
    ci.Corporation_Trainee_Id,
    ci.Name,
    ci.Mobile,
    CASE WHEN ai.Corporation_Trainee_Id IS NOT NULL THEN 'Present' END AS Attendance
FROM Table_Trainee_Corporate_Info ci
LEFT JOIN Table_Trainee_Attendance_Info ai
    ON ci.Corporation_Trainee_Id = ai.Corporation_Trainee_Id AND
       CAST(ai.AttendanceDate AS date) = CAST(GETDATE() AS date) AND
       ai.BatchId = 63
WHERE
    ci.BatchId = 63
ORDER BY
    ci.Corporation_Trainee_Id;

Upvotes: 1

Related Questions