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: 2016-08-08

Who is Participating?

Solutions

LVL 66

Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented: 2016-08-08

>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

LVL 66

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented: 2016-08-08

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: 2016-08-08

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.