Avatar of RadhaKrishnaKiJaya
RadhaKrishnaKiJaya
 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.
Microsoft SQL ServerC#Visual Basic.NETMySQL Server

Avatar of undefined
Last Comment
RadhaKrishnaKiJaya

8/22/2022 - Mon
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

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

ASKER
Thanks you very much.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck