代码之家 › 专栏 › 技术社区 › Jaydel Gluckie

在Microsoft SQL中,我如何将一年中的一天“打包”成几周?用于满足材料要求的制造场景

dateadd datediff sql-server

Jaydel Gluckie · 技术社区 · 17 年前

我需要创建一份总需求报告,记录从开始日期起库存中某件物品的供需情况,并将其“分桶”到一年中的不同周,以便物料计划人员知道何时需要某件物品,以及当时是否有足够的库存。

例如,今天的日期(报告日期)是8/27/08。第一步是找到报告日期所在的星期一的日期。在这种情况下,星期一将是8/25/08。这是第一桶的第一天。在此之前的所有交易都分配到第0周,并将汇总为报告的期初余额。剩余的桶是从该点开始计算的。对于第八个bucket,没有结束日期,因此在第八个bucket开始日期之后的任何交易都被视为第8周。

周#开始日期结束日期
0………没有。.........8/24/08
1.......8/25/08.......8/31/08
2.......9/1/08.........9/7/08
3.......9/8/08.........9/14/08
4.......9/15/08.......9/21/08
5.......9/22/08.......9/28/08
6.......9/29/08.......10/5/08
7.......10/06/08.....10/12/08
2008年10月13日……无

如何获取给定日期的周号、开始日期和结束日期?

5 回复 | 直到 17 年前

dkretz 17 年前

我一直认为,对于SQL Server来说,通过域视界为未来每周构建一行表是最简单、最有效的;并连接到该表(使用“WHERE GETDATE()>=MONDATE且不存在(从WHERE MONDATE<GETDATE()表中选择1)”。

你试图用UDF做的任何事情都会效率低得多,我发现更难使用。

Recep 17 年前

您可以在一周内的任何给定日期获得周一,如下所示:

DATEADD(d, 1 - DATEPART(dw, @date), @date)

您可以编写一个具有以下主体的存储过程

-- find Monday at that week
DECLARE @currentDate SMALLDATETIME
SELECT @currentDate = DATEADD(d, 1 - DATEPART(dw, @date), @date)

-- create a table and insert the first record
DECLARE @weekTable TABLE (Id INT, StartDate SMALLDATETIME, EndDate SMALLDATETIME)
INSERT INTO @weekTable VALUES (0, NULL, @currentDate)

-- increment the date
SELECT @currentDate = DATEADD(d, 1, @currentDate)

-- iterate for 7 more weeks
DECLARE @id INT
SET @id = 1
WHILE @id < 8
BEGIN
    INSERT INTO @weekTable VALUES (@id, @currentDate, DATEADD(d, 6, @currentDate))
    SELECT @currentDate = DATEADD(ww, 1, @currentDate)
    SET @id = @id + 1
END

-- add the last record
INSERT INTO @weekTable VALUES (8, @currentDate, NULL)

-- select the values
SELECT Id 'Week #', StartDate 'Start Date', EndDate 'End Date'
FROM @weekTable

当我经过时

@date = '20080827'

对于此过程,我得到以下内容

Week #  Start Date     End Date
0   NULL                2008-08-24 00:00:00
1   2008-08-25 00:00:00 2008-08-31 00:00:00
2   2008-09-01 00:00:00 2008-09-07 00:00:00
3   2008-09-08 00:00:00 2008-09-14 00:00:00
4   2008-09-15 00:00:00 2008-09-21 00:00:00
5   2008-09-22 00:00:00 2008-09-28 00:00:00
6   2008-09-29 00:00:00 2008-10-05 00:00:00
7   2008-10-06 00:00:00 2008-10-12 00:00:00
8   2008-10-13 00:00:00 NULL

Jaydel Gluckie 17 年前

--SQL将一周的第一天设置为星期日,为了我们的目的,我们希望它是星期一。
--这个命令就是这么做的。

SET DATEFIRST 1

DECLARE 
    @ReportDate DATETIME, 

    @Weekday INTEGER, 
    @NumDaysToMonday INTEGER, 
    @MondayStartPoint DATETIME,
    @MondayStartPointWeek INTEGER,
    @DateToProcess DATETIME,
    @DateToProcessWeek INTEGER,
    @Bucket VARCHAR(50),
    @DaysDifference INTEGER,
    @BucketNumber INTEGER,
    @NumDaysToMondayOfDateToProcess INTEGER,
    @WeekdayOfDateToProcess INTEGER,
    @MondayOfDateToProcess DATETIME,
    @SundayOfDateToProcess DATETIME

SET @ReportDate = '2009-01-01'
print @ReportDate

SET @DateToProcess = '2009-01-26'
--print @DateToProcess

SET @Weekday = (select DATEPART ( dw , @ReportDate ))
--print @Weekday

--print DATENAME(dw, @ReportDate)

SET @NumDaysToMonday = 
    (SELECT
      CASE 
         WHEN @Weekday =  1 THEN 0
         WHEN @Weekday =  2 THEN 1
         WHEN @Weekday =  3 THEN 2
         WHEN @Weekday =  4 THEN 3
         WHEN @Weekday =  5 THEN 4
         WHEN @Weekday =  6 THEN 5
         WHEN @Weekday =  7 THEN 6
      END)

--print @NumDaysToMonday

SET @MondayStartPoint =  (SELECT DATEADD (d , -1*@NumDaysToMonday, @ReportDate))
--print @MondayStartPoint

SET @DaysDifference = DATEDIFF ( dd , @MondayStartPoint , @DateToProcess )
--PRINT @DaysDifference

SET @BucketNumber = @DaysDifference/7
--print @BucketNumber

----Calculate the start and end dates of this bucket------
PRINT 'Start Of New Calc'

print @DateToProcess

SET @WeekdayOfDateToProcess = (select DATEPART ( dw , @DateToProcess ))
print @WeekdayOfDateToProcess

SET @NumDaysToMondayOfDateToProcess= 
    (SELECT
      CASE 
         WHEN @WeekdayOfDateToProcess =  1 THEN 0
         WHEN @WeekdayOfDateToProcess =  2 THEN 1
         WHEN @WeekdayOfDateToProcess =  3 THEN 2
         WHEN @WeekdayOfDateToProcess =  4 THEN 3
         WHEN @WeekdayOfDateToProcess =  5 THEN 4
         WHEN @WeekdayOfDateToProcess =  6 THEN 5
         WHEN @WeekdayOfDateToProcess =  7 THEN 6
      END)

print @NumDaysToMondayOfDateToProcess
SET @MondayOfDateToProcess =  (SELECT DATEADD (d , -1*@NumDaysToMondayOfDateToProcess, @DateToProcess))
print @MondayOfDateToProcess   ---This is the start week

SET @SundayOfDateToProcess = (SELECT DATEADD (d , 6, @MondayOfDateToProcess))
PRINT @SundayOfDateToProcess

Community Mohan Dere 9 年前

我看到的一次一桶的方法的问题是,它很难扩大规模,

如果你加入一个用户定义的函数,你会得到更好的性能,你可以使用 this a a starting point

Jonathan Leffler 17 年前

为什么不使用DATEPART(年,日期列)和DATEPART的组合(周,日期列”)并按这些值分组。如果一周内 DATEPART 按照ISO 8601的要求,周一对齐。概述:

SELECT DATEPART(year, date_column) AS yyyy,
       DATEPART(week, date_column) AS ww,
       ...other material as required...
    FROM SomeTableOrOther
    WHERE ...appropriate filters...
    GROUP BY yyyy, ww -- ...and other columns as necessary...