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.

Microsoft SQL ServerC#Visual Basic.NETMySQL Server

Last Comment

RadhaKrishnaKiJaya

8/22/2022 - Mon

Jim Horn

8/8/2016

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

8/8/2016

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

8/8/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

RadhaKrishnaKiJaya