SQL Server
SQL Server - 每周结束(SQL Server - Week Ending)我有以下T-SQL代码可以工作，但我认为可能会减少但不确定如何。
SELECT datepart(YEAR, CONVERT(DATE, GETDATE())) AS 'Year', Cast(Datepart(month, DATEADD (D, -1 * DatePart (DW, getdate()) + 7, getdate())) AS VARCHAR) + '-' + Cast(Datepart(DAY, DATEADD (D, -1 * DatePart (DW, getdate()) + 7, getdate()))AS VARCHAR) [Week Ending]我只想以一年的格式结束一周，因此我可以按年份分组，然后按周结尾。 可以帮助一些。
I have the following T-SQL code that works but I think may be reduced but not sure how.
SELECT datepart(YEAR, CONVERT(DATE, GETDATE())) AS 'Year', Cast(Datepart(month, DATEADD (D, -1 * DatePart (DW, getdate()) + 7, getdate())) AS VARCHAR) + '-' + Cast(Datepart(DAY, DATEADD (D, -1 * DatePart (DW, getdate()) + 7, getdate()))AS VARCHAR) [Week Ending]I just want to get the week ending in a format with the year in another column so I can group it by year and then week ending. Can some help out.
最满意答案你不能这样做吗？：
DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) [WeekEnd]没有理由必须在单独的专栏中将年份分开。
编辑：我不确定你的意见是什么意思。 这会打印日期，并以日期格式而不是varchar - 您的评论暗示不是周数。
例子：
DECLARE @dateTime datetimeSET @dateTime = '2016-01-01'select DATEADD(DAY, 7 - DATEPART(WEEKDAY, @dateTime), CAST(@dateTime AS DATE)) [WeekEnd]2016年1月2日
DECLARE @dateTime datetimeSET @dateTime = '2016-01-04'select DATEADD(DAY, 7 - DATEPART(WEEKDAY, @dateTime), CAST(@dateTime AS DATE)) [WeekEnd]2016年1月9日
使用示例：
DECLARE @table TABLE (orderDate datetime, orderAmount float)INSERT INTO @table( orderDate, orderAmount)SELECT '2015-01-02', 500union all SELECT '2015-01-04', 500union all SELECT '2015-01-05', 500union all SELECT '2015-01-05', 500union all SELECT '2015-01-06', 500union ALL SELECT '2015-01-11', 400union all SELECT '2016-01-01', 500union all SELECT '2016-01-02', 500union all SELECT '2016-01-04', 500union all SELECT '2016-01-05', 500union all SELECT '2016-01-05', 500union all SELECT '2016-01-06', 500UNION ALL SELECT '2016-01-11', 400UNION ALL SELECT '2016-12-11', 1200每日订单金额：
SELECT orderDate, sum(orderAmount) AS orderSumForGrouping, count(1) AS numberOfOrdersWithinGroupingFROM @table oGROUP BY orderDate输出：
orderDate orderSumForGrouping numberOfOrdersWithinGrouping2015-01-02 00:00:00.000 500 12015-01-04 00:00:00.000 500 12015-01-05 00:00:00.000 1000 22015-01-06 00:00:00.000 500 12015-01-11 00:00:00.000 400 12016-01-01 00:00:00.000 500 12016-01-02 00:00:00.000 500 12016-01-04 00:00:00.000 500 12016-01-05 00:00:00.000 1000 22016-01-06 00:00:00.000 500 12016-01-11 00:00:00.000 400 12016-12-11 00:00:00.000 1200 1按年份分组的订单金额：
SELECT year(orderDate) AS orderYear, sum(orderAmount) AS orderSumForGrouping, count(1) AS numberOfOrdersWithinGroupingFROM @table oGROUP BY year(orderDate)输出：
orderYear orderSumForGrouping numberOfOrdersWithinGrouping2015 2900 62016 4600 8按周末日期分组的订单金额：
SELECT DATEADD(DAY, 7 - DATEPART(WEEKDAY, orderDate), CAST(orderDate AS DATE)) AS ordersPerWeek, sum(orderAmount) AS orderSumForGrouping, count(1) AS numberOfOrdersWithinGroupingFROM @table oGROUP BY DATEADD(DAY, 7 - DATEPART(WEEKDAY, orderDate), CAST(orderDate AS DATE))输出：
ordersPerWeek orderSumForGrouping numberOfOrdersWithinGrouping2015-01-03 500 12015-01-10 2000 42015-01-17 400 12016-01-02 1000 22016-01-09 2000 42016-01-16 400 12016-12-17 1200 1请注意， 2016-01-02和2016-01-16都是星期六 - 本周的最后一天。 （对于其他日期也适用，但这些日期刚刚发生，因此最容易检查）
Couldn't you do this?:
DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) [WeekEnd]No reason to necessarily to separate out the year in a separate column.
EDIT: I'm not sure what you mean by your comment. This prints out dates, and in date format rather than varchar - not week numbers are your comment suggests.
examples:
DECLARE @dateTime datetimeSET @dateTime = '2016-01-01'select DATEADD(DAY, 7 - DATEPART(WEEKDAY, @dateTime), CAST(@dateTime AS DATE)) [WeekEnd]2016-01-02
DECLARE @dateTime datetimeSET @dateTime = '2016-01-04'select DATEADD(DAY, 7 - DATEPART(WEEKDAY, @dateTime), CAST(@dateTime AS DATE)) [WeekEnd]2016-01-09
Sample of use:
DECLARE @table TABLE (orderDate datetime, orderAmount float)INSERT INTO @table( orderDate, orderAmount)SELECT '2015-01-02', 500union all SELECT '2015-01-04', 500union all SELECT '2015-01-05', 500union all SELECT '2015-01-05', 500union all SELECT '2015-01-06', 500union ALL SELECT '2015-01-11', 400union all SELECT '2016-01-01', 500union all SELECT '2016-01-02', 500union all SELECT '2016-01-04', 500union all SELECT '2016-01-05', 500union all SELECT '2016-01-05', 500union all SELECT '2016-01-06', 500UNION ALL SELECT '2016-01-11', 400UNION ALL SELECT '2016-12-11', 1200Order amount per date:
SELECT orderDate, sum(orderAmount) AS orderSumForGrouping, count(1) AS numberOfOrdersWithinGroupingFROM @table oGROUP BY orderDateOutput:
orderDate orderSumForGrouping numberOfOrdersWithinGrouping2015-01-02 00:00:00.000 500 12015-01-04 00:00:00.000 500 12015-01-05 00:00:00.000 1000 22015-01-06 00:00:00.000 500 12015-01-11 00:00:00.000 400 12016-01-01 00:00:00.000 500 12016-01-02 00:00:00.000 500 12016-01-04 00:00:00.000 500 12016-01-05 00:00:00.000 1000 22016-01-06 00:00:00.000 500 12016-01-11 00:00:00.000 400 12016-12-11 00:00:00.000 1200 1Order amounts grouped by year:
SELECT year(orderDate) AS orderYear, sum(orderAmount) AS orderSumForGrouping, count(1) AS numberOfOrdersWithinGroupingFROM @table oGROUP BY year(orderDate)Output:
orderYear orderSumForGrouping numberOfOrdersWithinGrouping2015 2900 62016 4600 8Order amounts grouped by week-end date:
SELECT DATEADD(DAY, 7 - DATEPART(WEEKDAY, orderDate), CAST(orderDate AS DATE)) AS ordersPerWeek, sum(orderAmount) AS orderSumForGrouping, count(1) AS numberOfOrdersWithinGroupingFROM @table oGROUP BY DATEADD(DAY, 7 - DATEPART(WEEKDAY, orderDate), CAST(orderDate AS DATE))Output:
ordersPerWeek orderSumForGrouping numberOfOrdersWithinGrouping2015-01-03 500 12015-01-10 2000 42015-01-17 400 12016-01-02 1000 22016-01-09 2000 42016-01-16 400 12016-12-17 1200 1observe that 2016-01-02, 2016-01-09, and 2016-01-16 are all Saturdays - the last day of the week. (holds true for the other dates as well, but these dates just occurred so are easiest to check)