Hi Experts,
I have this query.
SELECT U.UserID,ltrim(rtrim(upper(U.UserFirstName))) + ' ' +
ltrim(rtrim(upper(U.UserLastName))) as UserName,
ltrim(rtrim(U.AttendenceDay)) as AttendenceDay,
D.DayName as AttDayName FROM USERDETAILS as U
LEFT OUTER JOIN DAYMST As D on U.AttendenceDay = D.DayID
where U.[UserType]=1 and U.[UserActive]='Y'
and D.DayName=datename(dw,getdate())
output
------------
UserID UserName Attendence AttDayName
Day
22 VEDANTA_THUMB 1 Monday
43 MICHAEL BECKER 1 Monday
44 ERIC BEDOLLA 1 Monday
45 AUSTIN BEESON 1 Monday
46 JACOB BEGLER 1 Monday
49 JEREMY BELOUSEK 1 Monday
50 KEVIN BENAK 1 Monday
51 STEVEN BEREZEWSK 1 Monday
52 CHRISTOPHER BERGER 1 Monday
If the user name is present in another table called LOGINDETAILS then it should not be in this table
LOGINDETAILS
---------------------
UserID StdTimeIn StdTimeout TodaysDate
52 2016-07-01 12:46:23.430 NULL 08082016
38 2016-08-02 12:46:23.430 2016-08-02 12:46:23.430 08022016
38 2016-08-03 12:46:23.430 2016-08-03 12:46:23.430 08082016
40 2016-08-03 12:46:23.437 NULL 08032016
38 2016-08-04 11:16:28.927 2016-08-04 11:30:44.780 08042016
38 2016-08-04 11:39:34.237 NULL 08072016
22 2016-08-08 11:34:10.847 NULL 08082016
38 2016-08-04 11:30:38.137 2016-08-04 11:31:18.547 08042016
38 2016-08-04 11:34:10.847 2016-08-04 11:30:44.780 08082016
If the UserID has a StdTimeIn and StdTimeout is null then I do not want that record in the first table. For example:- UserID 52 and 22 has StdTimeIn but no StdTimeout . So I do not want that record. Please help.
Thanks in advance.
Open in new window
Now give this a whirl..Open in new window