For Loop in T-SQL?

Hi

It would be something like this (your code inside the loop may need adjusting)

 declare @PAID int
 declare @FREE int
 declare @CLASS int

Set @CLASS = 10
While @CLASS < 350
      Begin
          select @FREE = count(*)
          from submitqueue
                     where cost = 0
          and processstatus not in (4,5,6)
                                and class = @CLASS

     
          select @PAID = count(*)
          from submitqueue
          where cost > 0
          and processstatus not in (4,5,6)
                                and class = @CLASS

     
         
               /* INSERT FREE AD STATS */
               insert into ADCOUNTSBYCLASS (
                    DATE,
                    CLASS,
                    COUNT,
                    TYPE
               )
               values (
                    getdate(),
                    @CLASS,
                    @FREE,
                    'FREE'
               )
               /* INSERT PAID AD STATS */
               insert into ADCOUNTSBYCLASS (
                    DATE,
                    CLASS,
                    COUNT,
                    TYPE
               )
               values (
                    getdate(),
                    @CLASS,
                    @PAID,
                    'PAID')

             Set @CLASS = @CLASS+10

      End

You will need to add a line BEFORE the while statement that says:

Select @Class = 10

If you don't need a row for every class, only those that have ads, you could do this with a couple of single SQL statements and no loop like this.

INSERT INTO ADCOUNTSBYCLASS
SELECT
 getdate(),
 CLASS,
 COUNT(*),
 MAX(TYPE)
FROM  submitqueue
where cost = 0
and processstatus not in (4,5,6)
group by CLASS

insert into ADCOUNTSBYCLASS
select getdate(),
           CLASS,
           COUNT(*),
           'PAID'
from submitqueue
where cost > 0
and processstatus not in (4,5,6)
group by CLASS

there is no FOR NEXT, but there is a WHILE loop :

set @x = 1
while @x < 10
begin
     ......
     set @x = @x + 1
end

HOWEVER, you are much better off not doing a loop if you do not need to do so. Instead, you should always try to do set-based SQL statements where possible. In this case, you will be best off creating either a table, or a function which contains / generates the 35 CLASS values you want, and then a sinlge insert statement will do all the counts in one go :

try something like this :


create function dbo.tblClassType()
returns @tab table (class integer, type char(4))
as
begin
      declare @class integer
      set @class = 10
      while @class <= 350
      begin
            insert into @tab values (@class, 'FREE')
            insert into @tab values (@class, 'PAID')
            set @class = @class + 10
      end
      return
end

--- this function will provde the list of CLASS and TYPEs you are interested in, then a single join with your data, you can do it all in one insert.


        insert into ADCOUNTSBYCLASS (DATE,CLASS, COUNT, TYPE )
        select getdate(), cl.class, count(res.class),
            cl.type
        from  dbo.tblClassType() cl left outer join (
            select class,
            case when processstatus in (4,5,6) then 'PAID' else 'FREE' end as type
            from submitqueue
            where cost = 0
        ) res
                 on cl.class = res.class and cl.type = res.type
                group by  cl.class, cl.type

How about forgetting a loop and doing it using SQL?

INSERT INTO ADCOUNTSBYCLASS (DATE,CLASS,COUNT,TYPE), SELECT getDate(), COUNT(*) ,class, 'FREE' FROM  submitqueue WHERE cost = 0 GROUP BY CLASS
INSERT INTO ADCOUNTSBYCLASS (DATE,CLASS,COUNT,TYPE), SELECT getDate(), COUNT(*) ,class, 'PAID' FROM  submitqueue WHERE cost != 0 GROUP BY CLASS

or something similar

sorry, I misinterpreted your original, the type is set by the cost, not the process status, so the SQL should be

insert into ADCOUNTSBYCLASS (DATE,CLASS, COUNT, TYPE )
       select getdate(), cl.class, count(res.class),
          cl.type
       from  dbo.tblClassType() cl left outer join (
          select class,
          case when cost > -  then 'PAID' else 'FREE' end as type
          from submitqueue
          where processstatus <= 4 or processstatus >= 6
       ) res
                 on cl.class = res.class and cl.type = res.type
                group by  cl.class, cl.type


And, of course, if you do not need the 0 counts, you regress back to the simplest query, the one suggested by LowFatSpread

that's a 0 not a - , sorry for all the posts.

insert into ADCOUNTSBYCLASS (DATE,CLASS, COUNT, TYPE )
       select getdate(), cl.class, count(res.class),
          cl.type
       from  dbo.tblClassType() cl left outer join (
          select class,
          case when cost > 0  then 'PAID' else 'FREE' end as type
          from submitqueue
          where processstatus <= 4 or processstatus >= 6
       ) res
                 on cl.class = res.class and cl.type = res.type
                group by  cl.class, cl.type