SQL Query not working as expected

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

 

Answer 1

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

Answer 2

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 

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

User-1340885213 posted

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