February 3, 2017 at 9:14 am
Hello,
I am trying to write a query that gives me a count of all users who have been online for 24 hours, and no inactive period for 4 hours. I was able to get the query down to the day, but I can't seem to figure out the inactive periods. Here is what I have so far.
SELECT top 100 count(uid), uid,datetimestamp
FROM TABLE
WHERE (datetimestamp >= DATEADD(day,-1, datetimestamp))
and (datetimestamp > dateadd(minute,0,access_time)) and (datetimestamp < dateadd(minute,240,access_time))
GROUP BY UID,datetimestamp
HAVING COUNT(UID) >= 20
ORDER BY UID DESC
The are no problems, only solutions. --John Lennon
February 3, 2017 at 9:26 am
I'm not quite sure on the question, sorry, however, what is the point of this statement: datetimestamp >= DATEADD(day,-1, datetimestamp). datetimestamp is always going to be greater than it's own value minus a time period. Just like x >= x-10 will always be true, where x is a positive number.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 3, 2017 at 9:34 am
Hi Thom,
Thanks for this. Yea, I am struggling with this a bit.
Basically, I need to find Users that are on for 24 hours, with no inactive period for 4 hours. I am only selecting the UID and date, so essentially the UID is an email address.
See adjustment on query.
SELECT top 100 count(uid), uid,datetimestamp
FROM TABLE
WHERE (datetimestamp>= DATEADD(hour, 1, '01-03-2016')) and (datetimestamp< DATEADD(hour, 24, '01-03-2016'))
and (datetimestamp> dateadd(minute,0,access_time)) --and (datetimestamp< dateadd(minute,240,access_time))
and uid is not null and uid !='' and uid != '-'
GROUP BY UID,datetimestamp
HAVING COUNT(UID) > 10
ORDER BY UID DESC
The are no problems, only solutions. --John Lennon
February 3, 2017 at 10:00 am
I think i understand what you're saying.
I've written this for my own log table for our website (so you'll need to amend for your own table names) which did seem to give me the expected results, but I'm not really 100%. Does this get your close?
DECLARE @StartDate DateTime,
@EndDate DateTime;
SET @StartDate = '01-Feb-2017';
SET @EndDate = DATEADD(hour, 24,@StartDate);
WITH ActiveHours AS (
SELECT DISTINCT
EM.ipaddress,
DATEPART(HOUR, EM.errordate) AS ActiveHour
FROM Errormessages EM
WHERE EM.errordate BETWEEN @StartDate AND @EndDate)
, Inactivity AS(
SELECT *,
ActiveHour - LAG(ActiveHour) OVER (PARTITION BY ipaddress ORDER BY ActiveHour ASC) AS Inactive
FROM ActiveHours)
SELECT ipaddress
FROM Inactivity
GROUP BY ipaddress
HAVING MAX(Inactive) <=4
AND COUNT(DISTINCT ActiveHour) >= 20;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 3, 2017 at 10:17 am
Thank you very much!!!!!!!!!!!!!!!
I will try this.
The are no problems, only solutions. --John Lennon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply