提问人：Ajk89 提问时间：1/16/2023 更新时间：1/16/2023 访问量：23
SQL while 循环用于每两周一次的数据
SQL while loop for fortnightly data
问：
早上好
我有一些代码，运行良好，但我想知道是否可以优化它以某种方式在 while 循环上运行。基本上，我希望每两周对每个站点进行一次计数，从声明的开始日期和结束日期（即第一个两周）开始。它将持续到最近的星期天。代码如下。
DECLARE @Startdate DATE SET @Startdate = '2022-03-14'
DECLARE @enddate DATE
SET @enddate = (select DATEADD(DAY, DATEDIFF(DAY, 13, @Startdate )+13, +13))
Select SiteName
,COUNT ( CASE WHEN CallDate between @Startdate and @enddate THEN CaseID END) as 'Period 1'
,COUNT ( CASE WHEN CallDate between DATEADD(DD,14,@Startdate) and DATEADD(DD, 14 ,@enddate) THEN CaseID END) as 'Period 2'
,COUNT ( CASE WHEN CallDate between DATEADD(DD,28,@Startdate) and DATEADD(DD, 28 ,@enddate) THEN CaseID END) as 'Period 3'
,COUNT ( CASE WHEN CallDate between DATEADD(DD,28,@Startdate) and DATEADD(DD, 28 ,@enddate) THEN CaseID END) as 'Period 4'
FROM [PathwaysDos_LIVE].[dbo].[vwCases]
where SiteTypeID = 5
group by SiteName
提前致谢，
问候，安德鲁。
答：
0赞
Serg
1/16/2023
#1
您可以使用日历表。例如，动态创建它
DECLARE @Startdate DATE SET @Startdate = '2022-03-14';
with t0(n) as (
select n
from (
values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
) t(n)
), ns as (
select row_number() over(order by t1.n) - 1 n
from t0 t1, t0 t2, t0 t3
), calendar as (
select DATEADD(D, n*14, @Startdate) ds, DATEADD(D, n*14 + 13 ,@Startdate) de
from ns
where DATEADD(D, n*14, @Startdate) < getdate()
)
select SiteName, ds, de
,COUNT ( CASE WHEN CallDate between ds and de THEN CaseID END) as 'Period 1'
,COUNT ( CASE WHEN CallDate between DATEADD(DD, 14, ds) and DATEADD(DD, 14, de) THEN CaseID END) as 'Period 2'
,COUNT ( CASE WHEN CallDate between DATEADD(DD, 28, ds) and DATEADD(DD, 28, de) THEN CaseID END) as 'Period 3'
,COUNT ( CASE WHEN CallDate between DATEADD(DD, 28, ds) and DATEADD(DD, 28, de) THEN CaseID END) as 'Period 4'
from calendar
join [PathwaysDos_LIVE].[dbo].[vwCases]
on SiteTypeID = 5 and CallDate between ds and DATEADD(DD, 28, de)
group by SiteName, ds, de;
评论