asked on

Need help with a query again

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.

Jim Horn

For starters, please place your code in a code block (see the CODE button in the comment toolbar). Indenting would also help, as would eliminating unnecessary characters like square brackets.

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())

Open in new window

Now give this a whirl..

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  U
	LEFT JOIN DAYMST D on U.AttendenceDay = D.DayID
	LEFT JOIN LOGINDETAILS ld ON u.UserID = ld.UserID
WHERE U.UserType=1 and U.UserActive='Y'
	AND D.DayName=datename(dw,getdate()) 
	AND ld.UserID IS NULL

Open in new window

RadhaKrishnaKiJaya

ASKER

Hi,

Thanks for your reply. But my requirement is if the record is present in Login details (With stdTimein not null and stdtimeout is null) then I want to exclude it.

Thanks.

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RadhaKrishnaKiJaya

ASKER

Thanks you very much.