asked on

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

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

Chris M

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;

tigin44

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

ASKER CERTIFIED SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mattjankowski

ASKER

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