While Loop Fuction-CodePudding

I have this query

 select         
 Suc,       
 SKU,       
 TipoMov,       
 Prov,      
 sum(Uni) as Uni, sum(Imp_Vta) as Imp_Vta 
 into #Mov 
 from dual where (Mes between 22009 and 22105) 
 and        Suc = 11 
 and        TipoMov in (4,5,8)
 and        Id_Fec_Diaria between '2020-09-28' and '2021-05-17' 
 group by 
 Suc,       SKU,        TipoMov,        Prov

and I'm trying to do a while loop between two dates so far I have this but there is a mistake I can't fix nor understand.

declare
@FechaMin DATE='2020-09-28',
@FechaMax DATE='2021-05-17';

while (@FechaMin >= @FechaMax)
begin 
        select 
        suc,
        SKU,
        TipoMov,
        Prov,
        sum(Uni) as Uni,
        sum(Imp_Vta) as Imp_Vta
into #Mov
from dual
where (Mes between 22009 and 22105) and
        Suc = 11 and
        TipoMov in (4,5,8) and
        Id_Fec_Diaria >= @FechaMin and Id_Fec_Diaria< @FechaMax
group by 
        suc,
        SKU,
        TipoMov,
        prov) END

Thanks for the help.

CodePudding user response：

If I am not mistaken about your purpose, you have to use cursor to achieve your goals.

You can replace your while block with this code:

DECLARE 
    @Suc VARCHAR(MAX), 
    @SKU   VARCHAR(MAX),
    @TipoMov VARCHAR(MAX),
    @Prov VARCHAR(MAX)

DECLARE Mycursor CURSOR
FOR SELECT 
         Suc , 
         SKU ,
         TipoMov,
         Prov 
    FROM 
        dual where (Mes between 22009 and 22105) 
 and        Suc = 11 
 and        TipoMov in (4,5,8)
 and        Id_Fec_Diaria between '2020-09-28' and '2021-05-17'

OPEN Mycursor;

FETCH NEXT FROM Mycursor INTO 
    @Suc , 
    @SKU   ,
    @TipoMov ,
    @Prov ;

WHILE @@FETCH_STATUS = 0
    BEGIN
        -----Your Specific Code
        FETCH NEXT FROM Mycursor INTO 
             @Suc , 
             @SKU   ,
             @TipoMov ,
             @Prov ;
    END;

CLOSE Mycursor;

DEALLOCATE Mycursor;