Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).
Course Of The Month
3 days, 17 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
Why is this different from all of the other step by step guides? Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month3 days, 17 hours left to enroll
691 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.