How to LOOP a select statement and save results to a query and/or table

mattjankowski
mattjankowski used Ask the Experts™
on
I have written a query that selects the data based on log time. It is as follows:

SELECT DISTINCT method, COUNT(method),
FROM applog
WHERE log_time IN ( SELECT     log_time FROM         applog WHERE     DATEPART(YEAR,log_time) = '2009' AND DATEPART(MONTH,log_time) = '07' AND DATEPART(DAY,log_time)='06' AND DATEPART(HH,log_time)='12')
GROUP BY method

The result of a single query is

METHOD, COUNT(METHOD) where hour = 12

What I want is:
METHOD, COUNT(METHOD) where hour = 12, COUNT(METHOD) where hour =13, etc..

Where the leftmost method column has all the method names in the log and if a particular method is not called in the time frame of the select a '0' is inserted for that method in the count.

How do I script this?

I know that I will need to loop on: DATEPART(HH,log_time)='12'), but I don't know how to do the rest.

Thanks,
Matt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris MConsulting - Technology Services

Commented:
I did not exactly understand your query but for starters, if you're trying to pickup all hours of that day then do the following:
SELECT DISTINCT method, COUNT(method),
FROM applog
WHERE log_time between '20090706' and '20090707' GROUP BY method;

Commented:
this should solve your problem
SELECT DATEPART(HOUR, log_time), method, COUNT(*)
FROM  applog 
WHERE log_time between '20090706' and '20090707'
GROUP BY DATEPART(HOUR, log_time), method
ORDER BY DATEPART(HOUR, log_time), method

Open in new window

Senior Database Architect
Commented:
I think you should consider doing this with a Pivot on your DATEPART(HH,log_time) per day.
Check out Mark Wills' article on Dynamic Pivots, he does a great job of explaining them.
http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Dynamic-Pivot-Procedure-for-SQL-Server.html?sfQueryTermInfo=1+30+pivot

Author

Commented:
Hi All,

My apologies for the long delay in responding to your posts. I thought the pivot table approach was promising but my sql  was really not up to the complexity. A kind colleague has taught me a few more things - such as how to create a temporary table and how to do a loop in sql. I think I can reformulate the question.

I can generate the data I want in a loop. How do I change the loop so it can output to a table with:
number of columns = # loop iterations + 1
I have attempted to alter the temporary table each run through the loop, but that results in an error because local temporary tables cannot be altered. Is there another approach that may resolve this issue and provide me with output in the following columns:

label, col1,col2,col3....

Where the number of columns corresponds to the number of iterations through the loop.

Thanks,
Matt

declare @startdt datetime, @enddt datetime, @byhour int
select @byhour = 0, @startdt = "07/28/2009 00:00:00"
 
create table #label (
 label varchar(40)   NULL
)
 
insert into #label
SELECT DISTINCT label
FROM mytable
WHERE log_time > = @startdt  and log_time <= dateadd(dd,1, @startdt)
GROUP BY label
 
select @enddt = dateadd(hh,1, @startdt)
 
create table #byhour (
 label varchar(40)   NULL,
 byhour  int   null,
 thishour int null
)
/*This is the code that I want to put into the loop*/
 
insert into #byhour
SELECT DISTINCT label, COUNT(method), 0
    FROM mytable 
    WHERE log_time > = @startdt  and log_time <= @enddt
    GROUP BY label
/*Results in an error because #method is a temporary table. Ideally I would take the COUNT(label) column and insert it into the newly created column*/
alter table #label add col1 int null

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial