SQL Loop doesn't loop

Question

I have to repeat a specific bit of code for each month, so I thought to build a loop.

DECLARE @Startdate DATE = '20200101';
DECLARE @Enddate   DATE = '20210101';

WHILE (@Startdate < @Enddate) 
BEGIN
    SELECT Rapportagedatum, Boekdatum, Rekeningnr, Bedrag, Uw_referentie         
    FROM FC_GBRMUT (nolock) 
    WHERE RekeningNr = 14050  
      AND MONTH(Rapportagedatum) = MONTH(@Startdate) 
      AND YEAR(Rapportagedatum) = YEAR(@Startdate)
      AND Boekdatum < @Startdate

    SET @Startdate = DATEADD(m, 1, @Startdate)
END

Now the problem is that it refuses to loop. If I remove the AND Boekdatum < @Startdate it shows that only January is pulled from the database.

How do I fix it so that it loops for the entire year?

The loop is working for me. If you run this WHILE (@Startdate < @Enddate) BEGIN SELECT @Startdate SET @Startdate = DATEADD(m,1,@Startdate) END you should get 12 dates. — ramtamtam
– ramtamtam, Commented Apr 3, 2020 at 6:48
Lets take a step back. Why are you using WHILE loop and what do you want to achieve ? — Squirrel
– Squirrel, Commented Apr 3, 2020 at 6:54
WHat I want to achieve is that I get an overview of the balance per Reportingmonth while the bookingdate contains the months before that. If the loop works for you it's my systems that have the problem, thank you. The nolock is essential otherwise it will prohibit others in Finance accessing our bookkeepingprogram. — Loesje Middendorp
– Loesje Middendorp, Commented Apr 3, 2020 at 10:04

marc_s · Accepted Answer · 2020-04-03 08:02:53Z

1

Why even bother with a loop??

Just write it in one, simple statement:

SELECT Rapportagedatum, Boekdatum, Rekeningnr, Bedrag, Uw_referentie         
FROM FC_GBRMUT
WHERE RekeningNr = 14050  
  AND Boekdatum BETWEEN @StartDate AND @Enddate

Done! No looping, no unnecessary RBAR (row-by-agonizing-row) processing

answered Apr 3, 2020 at 8:02

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Loesje Middendorp Over a year ago

Well.. because for each month I need the Balance at Reportingdate while the bookingdate lies before the Reportingdate.

Sen Alexandru · Accepted Answer · 2020-04-03 06:50:12Z

0

I see no reason on why this would not work unless you get an error. I've tested the query below

DECLARE @Startdate      DATE = '20200101';
DECLARE @Enddate        DATE = '20210101';

WHILE  (@Startdate < @Enddate) 
BEGIN
    SELECT @Startdate
    SET @Startdate = DATEADD(m,1,@Startdate)
END

answered Apr 3, 2020 at 6:50

Sen Alexandru

2,3195 gold badges25 silver badges39 bronze badges

Comments

Mathias Fogel · Accepted Answer · 2020-04-03 08:36:53Z

As all the others already mentioned the Loop is working for me. I am wondering why you are using a WHILE LOOP in the first place, but let's not question this for now.

I created the following minimal example. Let's assume your data looks similar, then your results would return nothing because of Boekdatum is not smaller than StartDate for each Loop, but equals StartDate. Therefore in the line AND Boekdatum <= @Startdate I replaced = with <= and I removed (nolock). Maybe this will resolve your "not working" LOOP.

DECLARE @FC_GBRMUT table (Rapportagedatum date, Boekdatum date , Rekeningnr int , Bedrag varchar(100), Uw_referentie varchar(100))

INSERT INTO @FC_GBRMUT
SELECT '20200101', '20200101', 14050, 'RandomText', 'RandomText'
UNION SELECT '20200201', '20200201', 14050, 'RandomText', 'RandomText'
UNION SELECT '20200301', '20200301', 14050, 'RandomText', 'RandomText'
UNION SELECT '20200401', '20200401', 14050, 'RandomText', 'RandomText'
UNION SELECT '20200501', '20200501', 14050, 'RandomText', 'RandomText'
UNION SELECT '20200601', '20200601', 14050, 'RandomText', 'RandomText'
UNION SELECT '20200701', '20200701', 14050, 'RandomText', 'RandomText'
UNION SELECT '20200801', '20200801', 14050, 'RandomText', 'RandomText'
UNION SELECT '20200901', '20200901', 14050, 'RandomText', 'RandomText'
UNION SELECT '20201001', '20201001', 14050, 'RandomText', 'RandomText'
UNION SELECT '20201101', '20201101', 14050, 'RandomText', 'RandomText'
UNION SELECT '20201201', '20201201', 14050, 'RandomText', 'RandomText'

DECLARE @Startdate DATE = '20200101';
DECLARE @Enddate DATE = '20210101';

WHILE (@Startdate < @Enddate) 
BEGIN
SELECT Rapportagedatum, Boekdatum, Rekeningnr, Bedrag, Uw_referentie 
FROM @FC_GBRMUT --(nolock) 
WHERE RekeningNr =14050 
AND Month(Rapportagedatum) = MONTH(@Startdate) AND Year(Rapportagedatum) = YEAR(@Startdate)
AND Boekdatum <= @Startdate
SET @Startdate = DATEADD(m,1,@Startdate)
END

Collectives™ on Stack Overflow

SQL Loop doesn't loop

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related