How to do the while loop in SQL Server query

Question

I'm having trouble using the loop in the "where" part. Basically, I need my "END_DATE" to be every month's last day. I tried a dumb way by giving all those dates like this

WHERE 
   ATKR.BEGIN_DATE>='2017-1-1' and (ATKR.END_DATE = '2018-1-31' or ATKR.END_DATE = '2018-2-28' or ATKR.END_DATE = '2018-3-31'or 
ATKR.END_DATE = '2018-4-30' or ATKR.END_DATE = '2018-5-31' or ATKR.END_DATE = '2018-6-30' or 
ATKR.END_DATE = '2018-7-31' or ATKR.END_DATE = '2018-8-31' or ATKR.END_DATE = '2018-9-30' or 
ATKR.END_DATE = '2018-10-31' or ATKR.END_DATE = '2018-11-30'or ATKR.END_DATE = '2018-12-31'or 
ATKR.END_DATE = '2019-1-31' or ATKR.END_DATE = '2019-2-28' or ATKR.END_DATE = '2019-3-31'or 
ATKR.END_DATE = '2019-4-30' or ATKR.END_DATE = '2019-5-31' or ATKR.END_DATE = '2019-6-30' or 
ATKR.END_DATE = '2019-7-31' or ATKR.END_DATE = '2019-8-31' or ATKR.END_DATE = '2019-9-30' or 
ATKR.END_DATE = '2019-10-31' or ATKR.END_DATE = '2019-11-30'or ATKR.END_DATE = '2019-12-31'or 
ATKR.END_DATE = '2020-1-31' or ATKR.END_DATE = '2020-2-29' or ATKR.END_DATE = '2020-3-31'or 
ATKR.END_DATE = '2020-4-30' or ATKR.END_DATE = '2020-5-31')

However, I think I can do a loop like this to get all these dates

declare @interimDate as datetime
declare @i as Int
set @i=1;

WHILE @i <30
BEGIN

   SET @interimDate = DATEADD(month,((YEAR(getdate())-1900)*12) + MONTH(getdate())-@i,-1);
   PRINT @interimDate;

   set @i=@i+1;
END;

Can someone help me to combine them? Thank you.

"I'm having trouble using the loop in the "where" part" you don't. A WHILE is a logical flow operator, it doesn't go in a statement. What are you actually trying to achieve here? — Thom A
– Thom A ♦, Commented Jun 3, 2020 at 14:22
It's not clear what you want. The example with the WHERE will restrict the results of some query. Whereas the WHILE example will make a list of dates. Those are two very different things. What is the actual goal with this situation? — ADyson
– ADyson, Commented Jun 3, 2020 at 14:23

Wouter · Accepted Answer · 2020-06-03 14:22:49Z

1

You can do this to find if a date is the last day of the month :

where DAY(DATEADD(d,1, ATKR.END_DATE ) ) = 1

Another option is to construct a calendar table that you can just join.

answered Jun 3, 2020 at 14:22

Wouter

2,9763 gold badges11 silver badges22 bronze badges

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

Comments

mkRabbani · Accepted Answer · 2020-06-03 14:47:52Z

0

Lets have some fun with your where part :) Can you try this below logic server purpose or not-

WHERE 
ATKR.BEGIN_DATE>='2017-1-1' 
AND
(
    (MONTH(ATKR.END_DATE) IN (1,3,5,7,8,10,12) AND DAY(ATKR.END_DATE) = 31)
    OR
    (MONTH(ATKR.END_DATE) IN (4,6,9,11) AND DAY(ATKR.END_DATE) = 30)
    OR
    (
        (
            MONTH(ATKR.END_DATE) IN (2) 
            AND 
            DAY(ATKR.END_DATE) = 
                CASE 
                    WHEN YEAR(ATKR.END_DATE)%4 = 0 THEN 29
                    ELSE 28
                END
        )
    )
)

And probably another easy checking is as below-

WHERE 
ATKR.BEGIN_DATE>='2017-1-1' 
AND MONTH(ATKR.END_DATE) <> MONTH(DATEADD(d,1,ATKR.END_DATE))
-- The logic is, the month with always change if you add 1 day 
-- with your date if it is the last day of month :)

edited Jun 3, 2020 at 14:47

answered Jun 3, 2020 at 14:34

mkRabbani

16.9k2 gold badges17 silver badges26 bronze badges

1 Comment

mkRabbani Over a year ago

You are most welcome @Kennysmith. Nice to hear that it helped. But, probably I have added the better and simplest solution now in the second part of my answer :) Please don't forget to put an up vote as well.

Collectives™ on Stack Overflow

How to do the while loop in SQL Server query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related