Solved

Query for Periodic wise grouping.. MSSQL

Posted on 2004-04-28
8
803 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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 5

Assisted Solution

by:bwdowhan
bwdowhan earned 40 total points
Comment Utility
--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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:developer007
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 11

Assisted Solution

by:rdrunner
rdrunner earned 30 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Note that in the above queries,

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

Author Comment

by:developer007
Comment Utility
Oh..thanks a lot for all experts.

 
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now