15 minute count query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

15 minute count query

Author

Topic

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-02-05 : 09:42:58

I am a novice when it comes to SQL. I am basically able to build reports using the query builder but have been asked to do something more complex.

Essentially I have a table with two columns, DateAdded and Name. The "Name" column contains a list of system names and "DateAdded" contains the date/time the system was updated.

Name DateAdded
CME 05/10/2010 11:44
Routing Rules 05/10/2010 11:54
WFM 05/10/2010 11:54
Avaya 05/10/2010 12:04
CME 05/10/2010 12:04
Avaya 05/10/2010 12:04
CME 05/10/2010 12:04
Routing Rules 05/10/2010 12:04
WFM 05/10/2010 12:04
Avaya 05/10/2010 12:54

I need to produce a stored procedure that returns the number of changes per 15 minute interval per system.

CME Routing Rules WFM Avaya
11:30 1
11:45 1 1
12:00 2 1 1 2
12:15
12:30
12:45 1

I have been given the following code to help. It seems to return the number of changes, but does not split it down by system. The person who gave me the code is not available to help me further - can anyone assist.

ALTER procedure [dbo].[ChangesByIntervalGK]
@in_date datetime
as
begin
declare @timesteps table (interval varchar(5), howmany int)

declare @num int
select @num = 0
while @num < 96
begin
insert into @timesteps values (convert(varchar(5), dateadd(minute, @num*15, 0),8), 0)

select @num = @num + 1
end

select innerquery.interval, max(innerquery.howmany) howmany from
(

select * from @timesteps

union

select
convert(varchar(5), dateadd(minute, datediff(minute,0,Addeddate) / 15 * 15, 0), 8) interval,
count(*) howmany

from ConfigurationItemLatest

where Addeddate between @in_date and dateadd(day, 1, @in_date)
group by convert(varchar(5), dateadd(minute, datediff(minute,0,Addeddate) / 15 * 15, 0), 8)

) innerquery

group by innerquery.interval

end

I think I have to add "Name" to group by and select statement, but no idea exactly where it should go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 10:35:12

i think you need to apply a logic like this

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-05 : 10:55:12

Same/similar thoughts as described in Visakh's blog, but since I started it anyway before I saw Visakh's post, thought I will post this anyway.

CREATE TABLE #tmp ([name] VARCHAR(32), Addeddate DATETIME);
GO

insert into #tmp values ('CME','05/10/2010 11:44')
insert into #tmp values ('Routing Rules','05/10/2010 11:54')
insert into #tmp values ('WFM','05/10/2010 11:54')
insert into #tmp values ('Avaya','05/10/2010 12:04')
insert into #tmp values ('CME','05/10/2010 12:04')
insert into #tmp values ('Avaya','05/10/2010 12:04')
insert into #tmp values ('CME','05/10/2010 12:04')
insert into #tmp values ('Routing Rules','05/10/2010 12:04')
insert into #tmp values ('WFM','05/10/2010 12:04')
insert into #tmp values ('Avaya','05/10/2010 12:54');

GO
--------------------------------------------------

DECLARE @startTime DATETIME, @endTime DATETIME;
SET @startTime = '05/10/2010 11:00';
SET @endTime = '05/10/2010 14:00';

WITH cte1(tm) AS -- creating a calendar with 15 min intervals
(
   SELECT @startTime
   UNION ALL 
   SELECT DATEADD(minute,15,tm) 
   FROM cte1
   WHERE tm < @endTime
),
cte2 AS ----------------------- counting
(
   SELECT
      CAST(tm AS TIME) tm,
      [name],
      ISNULL(COUNT([name]),0) N
   FROM
      cte1 c
      LEFT JOIN #tmp t
         ON c.tm = DATEADD(minute,DATEDIFF(minute,0,Addeddate)/15*15,0)
   GROUP BY
      CAST(tm AS TIME),
      [name]
)
SELECT --pivoting
   *
FROM
   cte2
PIVOT(MAX(N) FOR [name] IN ([CME],[Routing Rules],[WFM],[Avaya])) P;

GO
DROP TABLE #tmp;

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-02-05 : 17:00:25

Thanks for the responses. I dont know enough to be able to follow that code. Also the values in the "Name" column is not fixed.

Is there anywnay to qucikly modift the original code to include the "Name" column and have values returned in the interval column of the report.

Lets say there are 2 values in the name column, CME and WFM. I therefore need the report to show column headers of interval, CME and WFM with values in CME and WFM columns of number of changes per 15 minutes.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 19:24:19

quote:
Originally posted by sprotson

Thanks for the responses. I dont know enough to be able to follow that code. Also the values in the "Name" column is not fixed.

Is there anywnay to qucikly modift the original code to include the "Name" column and have values returned in the interval column of the report.

Lets say there are 2 values in the name column, CME and WFM. I therefore need the report to show column headers of interval, CME and WFM with values in CME and WFM columns of number of changes per 15 minutes.

for varying values in Name column you can modify PIVOT part as follows

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources