如何在SQL Server查询中执行while循环

肯尼·史密斯

我在“ where”部分中使用循环时遇到麻烦。基本上，我需要将“ END_DATE”作为每个月的最后一天。我给所有这样的日期尝试了一个愚蠢的方式

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')

但是，我想我可以做这样的循环来获取所有这些日期

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;

有人可以帮助我将它们合并吗？谢谢。

mkRabbani

让我们在您的where部分中找到一些乐趣：)您是否可以在逻辑服务器目的下尝试以下操作-

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
        )
    )
)

可能另外一个简单的检查如下：

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 :)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。