Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL select - 2 tables

Author  Topic 

nkpriya
Starting Member

34 Posts

Posted - 2015-04-27 : 13:34:16
Hi
I have 2 tables -
1st table is weekly timesheets table has employee ID, Period Name, etc
2nd table is employee list from HR which has employee ID, manager ID.
I would like to know who did not submit timesheets each week as have period name in the 1st table.
How can I do that?
Thanks for your help

Kristen
Test

22859 Posts

Posted - 2015-04-27 : 13:39:42
You haven't provided the actual names of the tables and only given vague column names, so you'll have to work that out for yourself from this example:

SELECT *
FROM Table_2 AS EMP
WHERE NOT EXISTS
(
SELECT *
FROM Table_1 AS TS
WHERE TS.EmployeeID = EMP.EmployeeID
AND TS.PeriodName = 'XXX'
)
Go to Top of Page

nkpriya
Starting Member

34 Posts

Posted - 2015-04-27 : 13:52:44
Thank you.
Instead of giving period name, can we do group by Period Name? so that it displays employees missing for each period.Thanks!
Go to Top of Page

nkpriya
Starting Member

34 Posts

Posted - 2015-04-27 : 18:54:59
can you please respond.

SELECT *
FROM Table_2 AS EMP
WHERE NOT EXISTS
(
SELECT *
FROM Table_1 AS TS
WHERE TS.EmployeeID = EMP.EmployeeID
AND TS.PeriodName = 'XXX'
)

This is working for just one period name, I would like period name in select statement and for all periods whatever available. Please help!
Go to Top of Page
   

- Advertisement -