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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
This award recognizes an author who contributes the highest volume of original works or content. Recipients of this award produce extremely valuable content that prioritizes accuracy, relevancy, and professionalism.