Excluding Weekend Hours 1

[Zurich98]

Hello All,

DB platform: SQL Server 2005

I'm trying to create a daily data pull to check for User that have not login to the system for 48 hours.
the query below is working fine for Tuesday - Friday run. However, on Monday, I need the logic to exclude
Saturday and Sunday hours. For example, if the report run on Monday, 12/8 8am, I want the users that have not
login in since Friday, 12/5 8am. The users are not required to login during the weekend.

select UserName,max(AccessedDate) as LastLogin
into #t1
from tblLogin
group by UserName

select UserName,LastLogin
from #t1
where UserName NOT IN (select UserName from #t1
where LastLogin >= DATEADD(hour, -48, GETDATE())

Your input/sugesstion is greatly appreciated

Thanks

 

[RiverGuy]

Give this a try. Obviously adjust the "Monday Hours" if needed.

select UserName,max(AccessedDate) as LastLogin
into #t1
from tblLogin
group by UserName

select UserName,LastLogin
from #t1
where UserName NOT IN (select UserName from #t1
  where LastLogin >= 
	[b]CASE
		WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN DATEADD(hour, -96, GETDATE())
		ELSE DATEADD(hour, -48, GETDATE())
	END)[/b]

 

[Zurich98]

Thank you for your input. That is what I have but it does not seem to return the right CheckDate. The CheckDate return is 2008-12-04 09:30:00.000 where I'm expecting the CheckDate = 2008-12-05 09:30:00.000. Maybe I'll try -72.

declare @StartDate datetime
set @StartDate = '2008-12-08 09:30:00.000'

select UserName,max(AccessedDate) as LastLogin
into #t1
from tblLogin
group by UserName

select UserName,LastLogin,
CASE
WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN DATEADD(hour, -96, GETDATE())
ELSE DATEADD(hour, -48, GETDATE())
END) as CheckDate

from #t1
where UserName NOT IN (select UserName from #t1
where LastLogin >=
CASE
WHEN DATENAME(dw, GETDATE()) = 2 THEN DATEADD(hour, -96, GETDATE())
ELSE DATEADD(hour, -48, GETDATE())
END)

 

[RiverGuy]

By the way, in your bottom portion of your query, you're comparing DATENAME to an integer value of 2. If you're using DATENAME for day of week, you need to look at the string value (ex: Monday). If you want to use an integer comparison, use the DATEPART function instead.

 

[Zurich98]

Hi River Guy,

You are absolutely right. I use datepart. I just mistyped when posting a shorter version of my query. And yes the -96 I believe is correct. I just mis-count the hours. Thank you for your help.