如何使用SQL获得每周的周刊，包括周ID，开始和结束日期？

Example: Jan 2017

示例:2017年1月

Week 1- 01/01/2017 - 01/14/2017

Week 2 - 01/15/2017 - 01/28/2017

and so on...

等等...

Database : SQL Server

数据库:SQL Server

1 个解决方案

#1

Try this:

WITH dates AS (
    SELECT DATEADD(DAY,number,CAST('2017-01-01' AS DATE)) day
    FROM master..spt_values WHERE TYPE = 'p'
    AND number < 366
    )

SELECT 
    DATEPART(WEEK, day) as Week, 
    day as FirstWeekDay,
    dateAdd(day, 6, day) as LastWeekDay
FROM dates
WHERE DATENAME(dw, day) IN ('Monday')
Order by day

or regularly 15 days like your example:

或者像你的例子一样定期15天:

WITH dates AS (
    SELECT DATEADD(DAY,number,CAST('2017-01-01' AS DATE)) day
    FROM master..spt_values WHERE TYPE = 'p'
    AND number < 366
    )

SELECT 
    row_number() over (order by day),
    day as FirstWeekDay,
    dateAdd(day, 13, day) as LastWeekDay
FROM dates
WHERE DATENAME(dw, day) IN ('Sunday')
 and DATEPART(WEEK, day) % 2 = 1
order by day

UPDATE

You can use this query if your Calendar table has all the days:

如果您的日历表包含所有日期,则可以使用此查询:

SELECT 
    row_number() over (order by day_date) WeekId,
    day_date as FirstWeekDay,
    dateAdd(day, 6, day_date) as LastWeekDay  --change 6 -> 13 for 2 weeks
FROM Calendar
WHERE DATENAME(dw, day_date) IN ('Monday')    --change day name if you need
-- and DATEPART(WEEK, day_date) % 2 = 1       --remove comment for 2 weeks
order by day_date

#1