?
Solved

Need help with a query again

Posted on 2016-08-08
4
Medium Priority
?
105 Views
Last Modified: 2016-08-08
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.
0
Comment
Question by:RadhaKrishnaKiJaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41747834
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

0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 41747898
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.
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 41747901
>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

0
 

Author Closing Comment

by:RadhaKrishnaKiJaya
ID: 41747915
Thanks you very much.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question