Styjun

I have found ports on my Samsung smart tv running a display service. What can I do with it?

How is linear momentum conserved in circular motion?

How would Japanese people react to someone refusing to say “itadakimasu” for religious reasons?

Using roof rails to set up hammock

How to recover a single blank shot from a film camera

What is this plant I saw for sale at a Romanian farmer's market?

Co-worker is now managing my team. Does this mean that I'm being demoted?

I'm yearning in grey

Explicit song lyrics checker

Is it a bad idea to have a pen name with only an initial for a surname?

How do I become a better writer when I hate reading?

If the mass of the Earth is decreasing by sending debris in space, does its angular momentum also decrease?

First occurrence in the Sixers sequence

Are there any individual aliens that have gained superpowers in the Marvel universe?

How can I ping multiple IP addresses at the same time?

Is swap gate equivalent to just exchanging the wire of the two qubits?

Are there examples of rowers who also fought?

Do my partner and son need an SSN to be dependents on my taxes?

I just entered the USA without passport control at Atlanta airport

How can I detect if I'm in a subshell?

Is a sequel allowed to start before the end of the first book?

Why was New Asgard established at this place?

How can caller ID be faked?

Basic power tool set for Home repair and simple projects

Count Number of users logged-in in every 10 minutes

Count(*) vs Count(1) - SQL ServerHow to use count and group by at the same select statementMinute by minute breakdown of log dataBest practices for SQL varchar column lengthSQL COUNT users with MIN date of login?SQL select one row for every n minutesCounting date interval on each minutes (Tracking numbers of calls on each minutes)Microsoft SQL server count distinct every 30 minutesGetting count of number of hosts per log messagehow to get data grouped by every 5 minutes?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

-1

1

I need to count total unique users logged into the system in every 10 minutes.

I have a table called v_WebTMASessionLog and the columns are sessionstart, sessionend, user_login_id. The total user need to repeat count who continued using

I'm expecting an output like below

Date and time Number of users
3/25/19 8:00 5
3/25/19 8:10 5
3/25/19 8:20 8
3/25/19 8:30 9
3/25/19 8:40 8
3/25/19 8:50 7
3/25/19 9:00 2
3/25/19 9:10 1

sql sql-server

share|improve this question

edited Mar 25 at 11:33

Gordon Linoff

822k38333441

asked Mar 25 at 4:55

Raneesh RaveendranRaneesh Raveendran

305

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  mysql or sql server? which database you are using?
  
  – PSK
  Mar 25 at 4:58
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  first see your work ? then ask question (stackoverflow.com/help/how-to-ask). stack overflow is not code factory.
  
  – Ashish Detroja
  Mar 25 at 5:10
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ashish, answer to question is optional for you, not mandatory.
  
  – Raneesh Raveendran
  Mar 25 at 6:37
  

  
  
  
  

add a comment | 

-1

1

I need to count total unique users logged into the system in every 10 minutes.

I have a table called v_WebTMASessionLog and the columns are sessionstart, sessionend, user_login_id. The total user need to repeat count who continued using

I'm expecting an output like below

Date and time Number of users
3/25/19 8:00 5
3/25/19 8:10 5
3/25/19 8:20 8
3/25/19 8:30 9
3/25/19 8:40 8
3/25/19 8:50 7
3/25/19 9:00 2
3/25/19 9:10 1

sql sql-server

share|improve this question

edited Mar 25 at 11:33

Gordon Linoff

822k38333441

asked Mar 25 at 4:55

Raneesh RaveendranRaneesh Raveendran

305

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  mysql or sql server? which database you are using?
  
  – PSK
  Mar 25 at 4:58
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  first see your work ? then ask question (stackoverflow.com/help/how-to-ask). stack overflow is not code factory.
  
  – Ashish Detroja
  Mar 25 at 5:10
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ashish, answer to question is optional for you, not mandatory.
  
  – Raneesh Raveendran
  Mar 25 at 6:37
  

  
  
  
  

add a comment | 

I need to count total unique users logged into the system in every 10 minutes.

I have a table called v_WebTMASessionLog and the columns are sessionstart, sessionend, user_login_id. The total user need to repeat count who continued using

I'm expecting an output like below

Date and time Number of users
3/25/19 8:00 5
3/25/19 8:10 5
3/25/19 8:20 8
3/25/19 8:30 9
3/25/19 8:40 8
3/25/19 8:50 7
3/25/19 9:00 2
3/25/19 9:10 1

sql sql-server

share|improve this question

edited Mar 25 at 11:33

Gordon Linoff

822k38333441

asked Mar 25 at 4:55

Raneesh RaveendranRaneesh Raveendran

305

Date and time Number of users
3/25/19 8:00 5
3/25/19 8:10 5
3/25/19 8:20 8
3/25/19 8:30 9
3/25/19 8:40 8
3/25/19 8:50 7
3/25/19 9:00 2
3/25/19 9:10 1

sql sql-server

sql sql-server

share|improve this question

edited Mar 25 at 11:33

Gordon Linoff

822k38333441

asked Mar 25 at 4:55

Raneesh RaveendranRaneesh Raveendran

305

share|improve this question

edited Mar 25 at 11:33

Gordon Linoff

822k38333441

asked Mar 25 at 4:55

Raneesh RaveendranRaneesh Raveendran

305

edited Mar 25 at 11:33

Gordon Linoff

822k38333441

edited Mar 25 at 11:33

Gordon Linoff

822k38333441

asked Mar 25 at 4:55

Raneesh RaveendranRaneesh Raveendran

305

asked Mar 25 at 4:55

Raneesh RaveendranRaneesh Raveendran

305

1 Answer
1

active

oldest

votes

0

In SQL Server, you can do it like following.

;WITH cte 
 AS (SELECT Dateadd(minute, ( Datediff(minute, CONVERT(CHAR(8), 
 your_date_column, 
 112),Dateadd(second,10 * 60 / 2,your_date_column)) /10 ) * 10, 
 CONVERT(CHAR(8), Getdate(), 112)) AS your_date_column 
 FROM [yourtable]) 
SELECT your_date_column, 
 Count(*) AS Users 
FROM cte 
GROUP BY your_date_column 
ORDER BY your_date_column 

Note: Replace [yourtable] with table name and your_date_column with date column name.

Edit:

is is possible to start this query with "Select"

Without CTE you can do it like following.

SELECT sessionstart, 
 Count(*) AS Users 
FROM (SELECT Dateadd(minute, ( Datediff(minute, CONVERT(CHAR(8), sessionstart, 
 112), 
 Dateadd(second, 
 10 * 60 / 2, sessionstart)) / 
 10 ) * 10, 
 CONVERT(CHAR(8), Getdate(), 112)) AS 
 sessionstart 
 FROM v_webtmasessionlog)t 
GROUP BY sessionstart 
ORDER BY sessionstart 

share|improve this answer

edited Mar 25 at 6:56

answered Mar 25 at 5:17

PSKPSK

13.4k31835

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thanks @PKS, is is possible to start this query with "Select"
  
  – Raneesh Raveendran
  Mar 25 at 6:35
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You mean you don't want to use CTE?
  
  – PSK
  Mar 25 at 6:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RaneeshRaveendran, I have updated the answer.
  
  – PSK
  Mar 25 at 6:39
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  when i tried below query it showing "Invalid column name 'sessionstart'." SELECT sessionstart, Count(*) AS Users FROM ( SELECT Dateadd(minute, ( Datediff(minute, CONVERT(CHAR(8), sessionstart, 112),Dateadd(second,10 * 60 / 2,sessionstart)) /10 ) * 10, CONVERT(CHAR(8), Getdate(), 112)) AS your_date_column FROM v_WebTMASessionLog )t GROUP BY your_date_column ORDER BY your_date_column
  
  – Raneesh Raveendran
  Mar 25 at 6:50
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Change your_date_column to sessionstart in your inner query.
  
  – PSK
  Mar 25 at 6:55
  

  
  
  
  

 | 
show 4 more comments

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55331446%2fcount-number-of-users-logged-in-in-every-10-minutes%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

In SQL Server, you can do it like following.

;WITH cte 
 AS (SELECT Dateadd(minute, ( Datediff(minute, CONVERT(CHAR(8), 
 your_date_column, 
 112),Dateadd(second,10 * 60 / 2,your_date_column)) /10 ) * 10, 
 CONVERT(CHAR(8), Getdate(), 112)) AS your_date_column 
 FROM [yourtable]) 
SELECT your_date_column, 
 Count(*) AS Users 
FROM cte 
GROUP BY your_date_column 
ORDER BY your_date_column 

Note: Replace [yourtable] with table name and your_date_column with date column name.

Edit:

is is possible to start this query with "Select"

Without CTE you can do it like following.

SELECT sessionstart, 
 Count(*) AS Users 
FROM (SELECT Dateadd(minute, ( Datediff(minute, CONVERT(CHAR(8), sessionstart, 
 112), 
 Dateadd(second, 
 10 * 60 / 2, sessionstart)) / 
 10 ) * 10, 
 CONVERT(CHAR(8), Getdate(), 112)) AS 
 sessionstart 
 FROM v_webtmasessionlog)t 
GROUP BY sessionstart 
ORDER BY sessionstart 

share|improve this answer

edited Mar 25 at 6:56

answered Mar 25 at 5:17

PSKPSK

13.4k31835

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thanks @PKS, is is possible to start this query with "Select"
  
  – Raneesh Raveendran
  Mar 25 at 6:35
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You mean you don't want to use CTE?
  
  – PSK
  Mar 25 at 6:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RaneeshRaveendran, I have updated the answer.
  
  – PSK
  Mar 25 at 6:39
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  when i tried below query it showing "Invalid column name 'sessionstart'." SELECT sessionstart, Count(*) AS Users FROM ( SELECT Dateadd(minute, ( Datediff(minute, CONVERT(CHAR(8), sessionstart, 112),Dateadd(second,10 * 60 / 2,sessionstart)) /10 ) * 10, CONVERT(CHAR(8), Getdate(), 112)) AS your_date_column FROM v_WebTMASessionLog )t GROUP BY your_date_column ORDER BY your_date_column
  
  – Raneesh Raveendran
  Mar 25 at 6:50
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Change your_date_column to sessionstart in your inner query.
  
  – PSK
  Mar 25 at 6:55
  

  
  
  
  

 | 
show 4 more comments

0

In SQL Server, you can do it like following.

;WITH cte 
 AS (SELECT Dateadd(minute, ( Datediff(minute, CONVERT(CHAR(8), 
 your_date_column, 
 112),Dateadd(second,10 * 60 / 2,your_date_column)) /10 ) * 10, 
 CONVERT(CHAR(8), Getdate(), 112)) AS your_date_column 
 FROM [yourtable]) 
SELECT your_date_column, 
 Count(*) AS Users 
FROM cte 
GROUP BY your_date_column 
ORDER BY your_date_column 

Note: Replace [yourtable] with table name and your_date_column with date column name.

Edit:

is is possible to start this query with "Select"

Without CTE you can do it like following.

SELECT sessionstart, 
 Count(*) AS Users 
FROM (SELECT Dateadd(minute, ( Datediff(minute, CONVERT(CHAR(8), sessionstart, 
 112), 
 Dateadd(second, 
 10 * 60 / 2, sessionstart)) / 
 10 ) * 10, 
 CONVERT(CHAR(8), Getdate(), 112)) AS 
 sessionstart 
 FROM v_webtmasessionlog)t 
GROUP BY sessionstart 
ORDER BY sessionstart 

share|improve this answer

edited Mar 25 at 6:56

answered Mar 25 at 5:17

PSKPSK

13.4k31835

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thanks @PKS, is is possible to start this query with "Select"
  
  – Raneesh Raveendran
  Mar 25 at 6:35
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You mean you don't want to use CTE?
  
  – PSK
  Mar 25 at 6:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RaneeshRaveendran, I have updated the answer.
  
  – PSK
  Mar 25 at 6:39
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  when i tried below query it showing "Invalid column name 'sessionstart'." SELECT sessionstart, Count(*) AS Users FROM ( SELECT Dateadd(minute, ( Datediff(minute, CONVERT(CHAR(8), sessionstart, 112),Dateadd(second,10 * 60 / 2,sessionstart)) /10 ) * 10, CONVERT(CHAR(8), Getdate(), 112)) AS your_date_column FROM v_WebTMASessionLog )t GROUP BY your_date_column ORDER BY your_date_column
  
  – Raneesh Raveendran
  Mar 25 at 6:50
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Change your_date_column to sessionstart in your inner query.
  
  – PSK
  Mar 25 at 6:55
  

  
  
  
  

 | 
show 4 more comments

In SQL Server, you can do it like following.

;WITH cte 
 AS (SELECT Dateadd(minute, ( Datediff(minute, CONVERT(CHAR(8), 
 your_date_column, 
 112),Dateadd(second,10 * 60 / 2,your_date_column)) /10 ) * 10, 
 CONVERT(CHAR(8), Getdate(), 112)) AS your_date_column 
 FROM [yourtable]) 
SELECT your_date_column, 
 Count(*) AS Users 
FROM cte 
GROUP BY your_date_column 
ORDER BY your_date_column 

Note: Replace [yourtable] with table name and your_date_column with date column name.

Edit:

is is possible to start this query with "Select"

Without CTE you can do it like following.

SELECT sessionstart, 
 Count(*) AS Users 
FROM (SELECT Dateadd(minute, ( Datediff(minute, CONVERT(CHAR(8), sessionstart, 
 112), 
 Dateadd(second, 
 10 * 60 / 2, sessionstart)) / 
 10 ) * 10, 
 CONVERT(CHAR(8), Getdate(), 112)) AS 
 sessionstart 
 FROM v_webtmasessionlog)t 
GROUP BY sessionstart 
ORDER BY sessionstart 

share|improve this answer

edited Mar 25 at 6:56

answered Mar 25 at 5:17

PSKPSK

13.4k31835

;WITH cte 
 AS (SELECT Dateadd(minute, ( Datediff(minute, CONVERT(CHAR(8), 
 your_date_column, 
 112),Dateadd(second,10 * 60 / 2,your_date_column)) /10 ) * 10, 
 CONVERT(CHAR(8), Getdate(), 112)) AS your_date_column 
 FROM [yourtable]) 
SELECT your_date_column, 
 Count(*) AS Users 
FROM cte 
GROUP BY your_date_column 
ORDER BY your_date_column 

SELECT sessionstart, 
 Count(*) AS Users 
FROM (SELECT Dateadd(minute, ( Datediff(minute, CONVERT(CHAR(8), sessionstart, 
 112), 
 Dateadd(second, 
 10 * 60 / 2, sessionstart)) / 
 10 ) * 10, 
 CONVERT(CHAR(8), Getdate(), 112)) AS 
 sessionstart 
 FROM v_webtmasessionlog)t 
GROUP BY sessionstart 
ORDER BY sessionstart 

share|improve this answer

edited Mar 25 at 6:56

answered Mar 25 at 5:17

PSKPSK

13.4k31835

answered Mar 25 at 5:17

PSKPSK

13.4k31835

answered Mar 25 at 5:17

PSKPSK

13.4k31835