To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.
Course Of The Month
6 days, 8 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
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| connection to SQL 2012 error in windows 10 | 18 | 46 | |
| Moving away from Access 2003 adp files | 4 | 47 | |
| VB.NET Parsing UDP Bytes | 15 | 36 | |
| STUFF function - proper use to update column? | 6 | 16 |
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.
Suggested Courses
Course of the Month6 days, 8 hours left to enroll
734 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.