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.
RadhaKrishnaKiJayaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
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

RadhaKrishnaKiJayaAuthor Commented:
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.
Jim HornMicrosoft SQL Server Data DudeCommented:
>But my requirement is if the record is present in Login details then I want to exclude it.

Should do that..
>FROM USERDETAILS  U
>      LEFT JOIN LOGINDETAILS ld ON u.UserID = ld.UserID
The above returns all rows from USERDETAILS regardless of LOGINDETAILS..
>WHERE ...  ld.UserID IS NULL
... but excluding rows not found in USERDETAILS.

>(With stdTimein not null and  stdtimeout is null)
Added here, but you may have to define 'present' in greater detail as I interpreted that as the row exists. 
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
        AND ld.stdTimein not null AND  ld.stdtimeout is null

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RadhaKrishnaKiJayaAuthor Commented:
Thanks you very much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.