0

I am attempting to create a query that will show users who have not logged on in the past 30 days. The way I can do this is by using my 3 Separate WIP tables. I want to display the out put where USER is not in WIP and WIP7Hist but only in WIP30Hist Here is is what I have written in an attempt to do the is:

Select Distinct wc.UserID
       , CONVERT(Datetime,wc.ShiftDate) as TimeDate
From WIP30Hist wc With(nolock)
Join WIP wa with(nolock) on wc.USerID = wa.UserID
Join WIP7Hist wb with(nolock) on wc.UserID = wa.UserID
Where wc.TimeDate <= GETDATE()-30 and wc.UserID Not in(select wa.UserID from WIP) 
and wc.UserID Not in(select wb.UserID from WIP7Hist)
Group By wc.UserID, wc.TimeDate, wc.ShiftDate
Order By TimeDate
Tom H
  • 46,766
  • 14
  • 87
  • 128
Bholl03
  • 1
  • 1
  • This is for Microsoft SQL I didn't see a tag for this and I am new to the site. Thanks in advance for any help! – Bholl03 Mar 28 '16 at 16:34

1 Answers1

0

We can do it with not in operator along with union query, e.g:

select userID
from WIP30Hist 
where //other conditions
and userID not in (
 select userID from WIP 

 union

 select userID from WIP7Hist
);
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • Thank you for the quick response just curious could you tell me if I am doing the date correctly or would I need to start a new thread I want the date to be 30 days in the past. – Bholl03 Mar 28 '16 at 18:15
  • You can have a look at this answer to get last 30 days' data : http://stackoverflow.com/questions/16645973/how-to-select-data-from-30-days – Darshan Mehta Mar 28 '16 at 18:17