• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 813
  • Last Modified:

Query for Periodic wise grouping.. MSSQL

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
developer007
Asked:
developer007
  • 2
  • 2
  • 2
  • +2
4 Solutions
 
bwdowhanCommented:
--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
 
jchopdeCommented:
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
 
bwdowhanCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
developer007Author Commented:
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
 
rdrunnerCommented:
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
 
iyerbhuvanacCommented:
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
 
iyerbhuvanacCommented:
Note that in the above queries,

testtab1--> table name
Time--> Hit Time field in testtab1
0
 
developer007Author Commented:
Oh..thanks a lot for all experts.

 
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now