时间戳

首页 > 解决方案 > T-SQL Select dates Sunday through Saturday (2 weeks apart) from Dates table for the last n months

sql-server - T-SQL Select dates Sunday through Saturday (2 weeks apart) from Dates table for the last n months

问题描述

I'm trying to select specific date ranges from a dates table and assign a week number (2 weeks = 1 week). Say I have the following table:

Date    WeekOfYear  WeekDayName
12/26/2019  52  Thursday
12/27/2019  52  Friday
12/28/2019  52  Saturday
12/29/2019  53  Sunday
12/30/2019  53  Monday
12/31/2019  53  Tuesday
1/1/2020    1   Wednesday
1/2/2020    1   Thursday
1/3/2020    1   Friday
1/4/2020    1   Saturday
1/5/2020    2   Sunday
1/6/2020    2   Monday
1/7/2020    2   Tuesday
1/8/2020    2   Wednesday
1/9/2020    2   Thursday
1/10/2020   2   Friday
1/11/2020   2   Saturday
1/12/2020   3   Sunday
1/13/2020   3   Monday
1/14/2020   3   Tuesday
1/15/2020   3   Wednesday
1/16/2020   3   Thursday
1/17/2020   3   Friday
1/18/2020   3   Saturday
1/19/2020   4   Sunday
1/20/2020   4   Monday
1/21/2020   4   Tuesday
1/22/2020   4   Wednesday
1/23/2020   4   Thursday
1/24/2020   4   Friday
1/25/2020   4   Saturday
1/26/2020   5   Sunday
1/27/2020   5   Monday
1/28/2020   5   Tuesday
1/29/2020   5   Wednesday
1/30/2020   5   Thursday
1/31/2020   5   Friday
2/1/2020    5   Saturday
2/2/2020    6   Sunday
2/3/2020    6   Monday
2/4/2020    6   Tuesday
2/5/2020    6   Wednesday
2/6/2020    6   Thursday
2/7/2020    6   Friday
2/8/2020    6   Saturday
2/9/2020    7   Sunday
2/10/2020   7   Monday
2/11/2020   7   Tuesday
2/12/2020   7   Wednesday
2/13/2020   7   Thursday
2/14/2020   7   Friday
2/15/2020   7   Saturday
2/16/2020   8   Sunday
2/17/2020   8   Monday
2/18/2020   8   Tuesday
2/19/2020   8   Wednesday
2/20/2020   8   Thursday
2/21/2020   8   Friday
2/22/2020   8   Saturday
2/23/2020   9   Sunday
2/24/2020   9   Monday
2/25/2020   9   Tuesday
2/26/2020   9   Wednesday
2/27/2020   9   Thursday
2/28/2020   9   Friday
2/29/2020   9   Saturday
3/1/2020    10  Sunday
3/2/2020    10  Monday
3/3/2020    10  Tuesday
3/4/2020    10  Wednesday
3/5/2020    10  Thursday
3/6/2020    10  Friday
3/7/2020    10  Saturday
3/8/2020    11  Sunday
3/9/2020    11  Monday
3/10/2020   11  Tuesday
3/11/2020   11  Wednesday
3/12/2020   11  Thursday
3/13/2020   11  Friday
3/14/2020   11  Saturday
3/15/2020   12  Sunday
3/16/2020   12  Monday
3/17/2020   12  Tuesday
3/18/2020   12  Wednesday
3/19/2020   12  Thursday
3/20/2020   12  Friday
3/21/2020   12  Saturday
3/22/2020   13  Sunday
3/23/2020   13  Monday
3/24/2020   13  Tuesday
3/25/2020   13  Wednesday

My end goal would like this:

WeekNum SundayDate  SaturdayDate
1       12/29/2019  1/11/2020
2       1/12/2020   1/25/2020
3       1/26/2020   2/8/2020
4       2/9/2020    2/22/2020
5       2/23/2020   3/7/2020
6       3/8/2020    3/21/2020

If I were to do this across several years, the max WeekNum would be either 26 or 27 depending on the year.

I've attempted several different queries but can't quite get the calculation right:

with MinDate_cte (Cut, MinDate)
as (select Cut = (((WeekOfYear + 1) % 52) / 2) + 1, MinDate = Date
    from DimDate
    where (datediff(day, Date, dateadd(day, 7 - datepart(weekday, getdate()), getdate())) % 14) = 0
        and Date between dateadd(month, -7, dateadd(day, 7 - datepart(weekday, getdate()), getdate())) -- Sunday 7 months ago
        and dateadd(day, 7 - datepart(weekday, getdate()), getdate()) -- next upcomming Saturday
    )

select Cut = (((WeekOfYear + 1) % 52) / 2) + 1, MaxDate = dateadd(day, 1, Date), MinDate
from DimDate
join MinDate_cte on MinDate_cte.Cut = (((WeekOfYear + 1) % 52) / 2) + 1
where (datediff(day, Date, dateadd(day, 7 - datepart(weekday, getdate()), getdate())) % 14) = 0
and Date between dateadd(month, -7, dateadd(day, 7 - datepart(weekday, getdate()), getdate())) -- Sunday 7 months ago
                           and dateadd(day, 7 - datepart(weekday, getdate()), getdate()) -- next upcomming Saturday
and dateadd(day, 1, Date) > MinDate
order by Date

标签： sql-servertsqldate

解决方案

我会使用更简单的 SQL，然后在星期天工作。这是未经测试的，因为我只是在这里输入的，但它应该是正确的或接近的。

--this is your date range
declare @startdate date = '20191229'
    ,@enddate date = '20200331'

select WeekNum = row_number() over (order by [Sunday]),*
from (
     select date as [Sunday]
        , dateadd(day,14,date) as [Saturday]
        , rn = row_number() over (order by date)
     from [your table]
     where WeekDayName = 'Sunday' 
         and date between @startdate and @enddate) a
where rn % 2 = 1

推荐阅读