Asked by:
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 Monday, December 9, 2019 10:36 AM
All replies
-
User-719153870 posted
Hi nagapavanich,
nagapavanich
displyas data twice 261.Please suggestThe 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.Attendancetoday 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_IdTuesday, 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