如何在一年中的某一天使用Microsoft SQL中的“一天”并将其“打包”？用于材料要求的制造场景

I have a need to create a gross requirements report that takes how much supply and demand of a item in inventory from a start date onwards and 'buckets' it into different weeks of the year so that material planners know when they will need a item and if they have enough stock in inventory at that time.

我需要创建一个总需求报告,该报告从开始日期开始计算库存中物品的供应和需求量,并将其“桶”分配到一年中的不同周,以便物料计划员知道他们何时需要物料和如果他们当时有足够的库存库存。

As an example, today’s date (report date) is 8/27/08. The first step is to find the date for the Monday of the week the report date falls in. In this case, Monday would be 8/25/08. This becomes the first day of the first bucket. All transactions that fall before that are assigned to week #0 and will be summarized as the beginning balance for the report. The remaining buckets are calculated from that point. For the eighth bucket, there is no ending date so any transactions after that 8th bucket start date are considered week #8.

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

WEEK# START DATE END DATE
0.......None..........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
8.......10/13/08......None

周#开始日期结束日期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 8 ....... 10/13/08 ......没有

How do I get the week #, start date, end date for a given date?

如何获得给定日期的周#,开始日期,结束日期?

5 个解决方案

#1

You can get Monday for any given date in a week as:

您可以在一周内的任何特定日期获得星期一:

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

and you can write a stored procedure with the following body

并且您可以使用以下正文编写存储过程

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

When I pass

当我通过

@date = '20080827'

to this procedure, I get the following

对于这个程序,我得到以下内容

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

#2

I've always found it easiest and most efficient (for SQL Server) to construct a table with one row for every week into the future through your domain horizon; and join to that (with a "WHERE GETDATE() >= MONDATE AND NOT EXISTS (SELECT 1 FROM table WHERE MONDATE < GETDATE())".

我总是发现最容易和最有效的(对于SQL Server)通过域范围构建一个每周一行的表格;并加入到那个(使用“WHERE GETDATE()> = MONDATE AND NOT EXISTS(SELECT 1 FROM table WHERE MONDATE ())”。

Anything you try to do with UDF's will be much less efficient and I find more difficult to use.

你试图用UDF做的任何东西效率都会低得多,而且我发现它更难以使用。

#3

--SQL sets the first day of the week as sunday and for our purposes we want it to be Monday.
--This command does that.

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

#4

The problem I see with the one bucket at a time approach is that its hard to make it scale,

我一次看到一个桶的问题是它难以扩展,

If you join into a user defined function you will get better performance, you could use this a a starting point

如果您加入用户定义的函数,您将获得更好的性能,您可以将此作为一个起点

#5

Why not use a combination of DATEPART(year, date-column) and DATEPART(week, date-column) and group by these values. This works if the week in DATEPART is aligned on Mondays as ISO 8601 requires. In outline:

为什么不使用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...

#1