With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf
COURSE of the MONTH
14 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Need help with a query again
Posted on 2016-08-08
Hi Experts,
I have this query.
SELECT U.UserID,ltrim(rtrim(upper
ltrim(rtrim(upper(U.UserLa
ltrim(rtrim(U.AttendenceDa
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,getd
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.
I have this query.
SELECT U.UserID,ltrim(rtrim(upper
ltrim(rtrim(upper(U.UserLa
ltrim(rtrim(U.AttendenceDa
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,getd
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.
[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
Active today
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())
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
Active today
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.
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.
Active today
>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.
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Course of the Month14 days, 6 hours left to enroll
771 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.