locked
SQL Query not working as expected RRS feed

  • Question

  • User1804579801 posted

    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

     

     

    Table_Trainee_Attendance_Info

    Trainee_atten_logId

    Corporation_Trainee_Id

    BatchId

    Attendance

    AttendanceDate

    SessionType

    TrainerId

    1

    261

    63

    Present

    12-9-19 15:31

    Session1

    1

    2

    262

    63

    Present

    12-9-19 15:31

    Session1

    1

    3

    264

    63

    Present

    12-9-19 15:31

    Session1

    1

    4

    265

    63

    Present

    12-9-19 15:31

    Session1

    1

    5

    261

    63

    Present

    12-10-19 15:34

    Session1

    1

     

    Required Out put

    Current Date

    12-09-2019

     

     

    Corporation_Trainee_Id

    Name

    Mobile

    Present

    261

    ankitha1

    9555962321

    Present

    262

    ankitha2

    9555962322

    Present

    263

    ankitha3

    9555962322

    NULL

    264

    ankitha4

    9555962323

    Present

    265

    ankitha5

    9555962323

    Present

    266

    ankitha6

    9555962324

    NULL

    267

    ankitha7

    9555962324

    NULL

    268

    ankitha8

    9555962325

    NULL

     

     

     

     

      SELECT t1.Corporation_Trainee_Id AS iParticipantId, t1.Name AS vName, t1.Mobile AS vPhonenumber, '' AS vProfile_image,t2.Attendance as present
    FROM Table_Trainee_Corporate_Info AS t1 LEFT JOIN
    Table_Trainee_Attendance_Info AS t2 ON t1.Corporation_Trainee_Id = t2.TraineeId
    WHERE (t1.BatchId = 63)
    OR ((CONVERT(varchar(50), t2.AttendanceDate, 103) = CONVERT(varchar(50), GETDATE(), 103)) )

    displyas data twice 261.Please suggest 

    iParticipantId vName vPhonenumber vProfile_image present
    261 ankitha1 9555962321 Present
    261 ankitha1 9555962321 Present
    262 ankitha2 9555962322 Present
    264 ankitha4 9555962324 Present
    265 ankitha5 9555962325 Present
    266 ankitha6 9555962326 NULL
    267 ankitha7 9555962327 NULL
    268 ankitha8 9555962328 NULL
    263 ankitha3 9555962323 NULL

     

    Monday, December 9, 2019 10:36 AM

All replies

  • User-719153870 posted

    Hi nagapavanich,

    nagapavanich

    displyas data twice 261.Please suggest 

    The condition in your query: WHERE (t1.BatchId = 63) OR ((CONVERT(varchar(50), t2.AttendanceDate, 103) = CONVERT(varchar(50), GETDATE(), 103)) ).

    Both of the 261 records meet the requirement t1.BatchId = 63, it will display twice is a logical performance.

    Change your condition like below using the AND in sql and all the conditions must be met at the same time:

    SELECT t1.Corporation_Trainee_Id AS iParticipantId, t1.Name AS vName, t1.Mobile AS vPhonenumber, '' AS vProfile_image,t2.Attendance as present
    FROM Table_Trainee_Corporate_Info AS t1 LEFT JOIN
    Table_Trainee_Attendance_Info AS t2 ON t1.Corporation_Trainee_Id = t2.TraineeId
    WHERE (t1.BatchId = 63)
    AND ((CONVERT(varchar(50), t2.AttendanceDate, 103) = CONVERT(varchar(50), GETDATE(), 103)) )

    Best Regard,

    Yang Shen

    Tuesday, December 10, 2019 1:52 AM
  • User1804579801 posted

    IF I Use  "AND" then I will get only data present in the attendance table I will not get all trainee's data so I modified my query as below. Please check and suggest is it a good way of writing the query 

    SELECT t1.Corporation_Trainee_Id AS iParticipantId, t1.Name AS vName, t1.Mobile AS vPhonenumber, '' AS vProfile_image, t2.Attendance AS present
    FROM Table_Trainee_Corporate_Info AS t1 Left OUTER JOIN
    Table_Trainee_Attendance_Info AS t2 ON t1.Corporation_Trainee_Id = t2.TraineeId
    where (t1.BatchId = 63) OR
    (CONVERT(varchar(50), t2.AttendanceDate, 103) = CONVERT(varchar(50), GETDATE(), 103))
    group by t1.Corporation_Trainee_Id , t1.Name , t1.Mobile , t2.Attendance

    today date is Dec 10, 2019

    This gives data but it is wrong then

    it should display only one record  261 with present and all other Ids present column should be null 

    Tuesday, December 10, 2019 5:42 AM
  • User1804579801 posted

    used case and solved problems.Please check  is it good way 

    SELECT
    ci.Corporation_Trainee_Id AS iParticipantId,
    ci.Name AS vName,
    ci.Mobile AS vPhonenumber,'' AS vProfile_image,
    CASE WHEN ai.TraineeId IS NOT NULL THEN 'Present' END AS present,ci.BatchId as iTrainingId
    FROM Table_Trainee_Corporate_Info ci
    LEFT JOIN Table_Trainee_Attendance_Info ai
    ON ci.Corporation_Trainee_Id = ai.TraineeId AND

    (CONVERT(varchar(50), ai.AttendanceDate, 103) = CONVERT(varchar(50), GETDATE(), 103))
    --CAST(ai.AttendanceDate AS date) = CAST(GETDATE() AS date)
    AND
    ai.BatchId = 63
    WHERE
    ci.BatchId = 63
    ORDER BY ci.Corporation_Trainee_Id

    Tuesday, December 10, 2019 6:53 AM
  • User-719153870 posted

    Hi nagapavanich,

    I assume the tables and their data are just for sample, your real time data should be much more and that's why you put some extra conditions in your query like AND ai.BatchId = 63 WHERE ci.BatchId = 63.

    But for current sample tables and data, this query is concise enough and if it suits for your real time data then it's perfect.

    Best Regard,

    Yang Shen

    Tuesday, December 10, 2019 8:08 AM
  • User753101303 posted

    Hi,

    Could it be a problem in your data? You have the same row twice in Table_Trainee_Attendance_Info one for 12-9-19 15:31 and one for 12-9-19 15:34.

    Tuesday, December 10, 2019 8:31 AM
  • User-1340885213 posted

    SELECT DISTINCT eliminates duplicate records from the results. You may have a try.

    Wednesday, December 11, 2019 10:56 AM