Solved

Query for Periodic wise grouping.. MSSQL

Posted on 2004-04-28
8
808 Views
Last Modified: 2012-06-27
Hi all experts,

  I am dealing with MSSQL server to solve a problem for showing statistics for a site.

 The table is like below..

Hit Time (SmallDateTime)         User ID

2004-03-28 13:58:00                1567523352      
2004-03-28 14:03:00                1567755616      
2004-03-28 14:08:00                1567974971      
2004-03-28 14:13:00                1568210757      
2004-03-28 14:18:00                1568469156
2004-03-28 14:23:00                1568761121
2004-03-28 14:28:00                1570200596
2004-03-28 14:33:00                1570483122
2004-03-28 14:38:00                1570875401
2004-03-28 14:43:00                571653606      
2004-03-28 14:48:00                1572154098

 From the above data I want a query to fetch number of users logged in an 20min periodic interval.
 Suppose I want to know in each hour for every 20min how many people logged into the site. To show the statistics for every period ..I need to above query.

 Please help me in this regard..waiting for it..

Thanks a lot in advance..
Reg,
lam
0
Comment
Question by:developer007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 5

Assisted Solution

by:bwdowhan
bwdowhan earned 40 total points
ID: 10946051
--Try This

BEGIN
DECLARE @V_START_DATE VARCHAR(10),
      @V_INTERVAL INT,
      @V_CURRENT_START DATETIME,
      @V_CURRENT_END DATETIME,
      @V_HITS INT

SET      @V_START_DATE = '2004-03-28'
SET      @V_INTERVAL = 20  --Minutes
SET      @V_CURRENT_START = CAST(@V_START_DATE AS DATETIME)

WHILE (@V_CURRENT_START < DATEADD(DAY,1,CAST(@V_START_DATE AS DATETIME)))

  BEGIN

      SET @V_CURRENT_END = (SELECT DATEADD(MINUTE,@V_INTERVAL,@V_CURRENT_START))
      
      --Replace the table hitcounter with your table name and [Hit Time] with your column name
      SET @V_HITS = (SELECT COUNT(*) FROM hitcounter WHERE [Hit Time] > @V_CURRENT_START and [Hit Time] <= @V_CURRENT_END)

      PRINT CAST(@V_CURRENT_START AS VARCHAR) + ' - ' + CAST(@V_CURRENT_END AS VARCHAR) + ' Total Hits: ' + CAST(@V_HITS AS VARCHAR)

      SET @V_CURRENT_START = @V_CURRENT_END      
  END


END


-- Brian
0
 
LVL 6

Assisted Solution

by:jchopde
jchopde earned 30 total points
ID: 10946118
Assumptions: You want to start and end with a range of time, your table is called UserHits with a column called HitTime, you want a resultset returned for processing into Excel or some other client program
------
set nocount on
declare @startPer smalldatetime, @endPer smalldatetime, @per_length smallint
declare @tot_pers smallint, @tot_mins smallint, @loop_count smallint
declare @tmp_endPer smalldatetime

create table #stats (PeriodStart smalldatetime not null, PeriodEnd smalldatetime not null, UserCount int)

--your start and end period for stats reporting
select @startPer = '2004-03-28 13:50:00'
select @endPer = '2004-03-28 17:00:00'
--your period length in minutes
select @per_length = 20

select @loop_count = 1

--find out how many 20 min periods
select @tot_mins = datediff(mi, @startPer, @endPer)
select @tot_pers = (@tot_mins / @per_length) +
      case
            when @tot_mins % @per_length > 0 then 1
            else 0
      end

while @loop_count <= @tot_pers
  begin
      select @tmp_endPer = dateadd(mi, @per_length, @startPer)
      if @tmp_endPer > @endPer
        select @tmp_endPer = @endPer

      insert into #stats
      select      @startPer,
            @tmp_endPer,
            count(*)
      from      UserHits
      where      HitTime > @startPer and HitTime <= @tmp_endPer

      select @loop_count = @loop_count + 1
      select @startPer = @tmp_endPer
  end

select * from #stats
0
 
LVL 5

Expert Comment

by:bwdowhan
ID: 10946119
I should have explained the process a little better.... The procedure listed in the prior comment had a hardcoded date that was used to calculate how many records you had in your table that had a date that fell within the INTERVAL Increments (set to 20 as per request). This process will calculate all of the hits for each interval for an entire day. Midnight to Midnight. I modified the code above and turned it into a stored procedure that accepts 1 argument, the date you want to count hits for. With some minor modification you should be able to do whatever customizations you need:


CREATE PROC HITCOUNT @V_START_DATE VARCHAR(10)
 as
BEGIN
DECLARE @V_INTERVAL INT,
      @V_CURRENT_START DATETIME,
      @V_CURRENT_END DATETIME,
      @V_HITS INT

SET      @V_INTERVAL = 20  --Minutes
SET      @V_CURRENT_START = CAST(@V_START_DATE AS DATETIME)

WHILE (@V_CURRENT_START < DATEADD(DAY,1,CAST(@V_START_DATE AS DATETIME)))

  BEGIN

      SET @V_CURRENT_END = (SELECT DATEADD(MINUTE,@V_INTERVAL,@V_CURRENT_START))
      
      --Replace the table hitcounter with your table name and [Hit Time] with your column name
      SET @V_HITS = (SELECT COUNT(*) FROM hitcounter WHERE [Hit Time] > @V_CURRENT_START and [Hit Time] <= @V_CURRENT_END)

      PRINT CAST(@V_CURRENT_START AS VARCHAR) + ' - ' + CAST(@V_CURRENT_END AS VARCHAR) + ' Total Hits: ' + CAST(@V_HITS AS VARCHAR)

      SET @V_CURRENT_START = @V_CURRENT_END      
  END


END

You execute the procedure by running it once on the server to create the script and then running the following code with the appropriate date:

exec hitcount '2004-03-28'


~Brian
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 1

Author Comment

by:developer007
ID: 10946869
Hi brain and chopde ,

 Thanks for ur inputs.. Actually, The things work for me and they are not exactly suits my need.

 since I should consider in the performance issues to show the graphs also to the user. So, I have changed the requirement to the below consideration. I don't need periodic statistics now .. I just need
  hourly statistics I just need to calculate how many users logged in per hour, per day, per week, per month and per year thats enough for me..

 Please help me in this regard. sorry for troubling a bit..

 With Reg,
Lamdor

 
0
 
LVL 11

Assisted Solution

by:rdrunner
rdrunner earned 30 total points
ID: 10947120
Thats easier :)

Select count(*) , DATEPART(hh,[Hit Time]) from yourTable where[Hit Time] >= Getdate -1 group by  DATEPART(hh,[Hit Time])

The only thing with this function is that it gets kinda messy on the last hour... You could also add another grouping to it to group by Day to fix that problem...


Hope that helps...
0
 
LVL 1

Accepted Solution

by:
iyerbhuvanac earned 50 total points
ID: 10947344
This should help

--For yEAR wise
/*
select
Datename(year,Time),
count(*) from
testtab1 group by
Datename(year,Time)
*/

--For Month wise
/*select Datename(Month,Time),
Datename(year,Time),
count(*) from
testtab1 group by
Datename(Month,Time),Datename(year,Time)
*/

--For Day wise
/*select Datename(Day,Time),Datename(Month,Time),
Datename(year,Time),
count(*) from
testtab1 group by
Datename(Month,Time),Datename(year,Time),Datename(Day,Time)
*/


--For week wise
/*
select Datename(week,Time),Datename(Month,Time),
Datename(year,Time),count(*) from
testtab1 group by
Datename(year,Time),Datename(Month,Time),Datename(week,Time)
*/


--For hour wise
/*
select Datename(hour,Time),
Datename(week,Time),Datename(Month,Time),
Datename(year,Time),
count(*) from testtab1
group by Datename(year,Time),Datename(Month,Time),Datename(week,Time),
Datename(hour,Time)
*/
0
 
LVL 1

Expert Comment

by:iyerbhuvanac
ID: 10947869
Note that in the above queries,

testtab1--> table name
Time--> Hit Time field in testtab1
0
 
LVL 1

Author Comment

by:developer007
ID: 10954780
Oh..thanks a lot for all experts.

 
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question