英文:
Show the previous week value next to the current value on a transaction table
问题
我有一个 SQL Server 表，其中有以下列：
[Username]，[Team]，[ID]（*主键），[DateEntered]，[Task]，[T_ID]，[ValueAmount]
[T_ID] 可以进行分组，因为可能有 5 个不同的任务，它们都具有相同的 T_ID 示例（例如，a、b、c、d、e，其 T_ID 为 1）。
我想要做的是将 [ValueAmount] 和 LastWeeks_ValueAmount 并排显示，但是我无法弄清楚如何做到。
我尝试了以下 SQL 语句：
SELECT
UserName, Team, ID, [DateEntered], [Task], T_ID, ValueAmount,
LAG(ValueAmount) OVER (ORDER BY DateEntered) AS PreviousWeek,
DATEADD(dd, ((DATEDIFF(dd, '17530101', DateEntered) / 7) * 7) - 7, '17530101') StartPW,
DATEADD(dd, ((DATEDIFF(dd, '17530101', DateEntered) / 7) * 7), '17530101') EndPW,
ROW_NUMBER() OVER (PARTITION BY [T_ID] ORDER BY DateEntered DESC) AS rn
FROM
Table
这将返回前一行，但不是上一周的行，所以仍然无法正常工作。我认为可能可以使用带有 case 语句的 CTE，但我无法弄清楚，希望有人能帮助我。
我尝试使用下面的帖子，但仍然无法使其正常工作：
为了提供更多细节，同一周内可能有多行数据，例如，同一任务、团队等可能有 2 行数据，其中一个是 T_ID 1，另一个是 T_ID 26，预期结果将在上一周中获取它们的值，因为它们属于相同的团队、任务和用户名。
英文:
I have a SQL Server table that has columns
[Username], [Team], [ID] (* primary key), [DateEntered], [Task], [T_ID], [ValueAmount]
The [T_ID] can be grouped as there could be 5 different tasks which all have the same T_ID example (a,b,c,d,e with the T_ID of 1).
What I am trying to do is see the [ValueAmount], LastWeeks_ValueAmount side by side but can't figure it out.
I have tried the following SQL
SELECT
UserName, Team, ID, [DateEntered], [Task], T_ID, ValueAmount,
LAG(ValueAmount) OVER (ORDER BY DateEntered) AS PreviousWeek,
DATEADD(dd, ((DATEDIFF(dd, '17530101', DateEntered) / 7) * 7) - 7, '17530101') StartPW,
DATEADD(dd, ((DATEDIFF(dd, '17530101', DateEntered) / 7) * 7) , '17530101') EndPW,
ROW_NUMBER() OVER (PARTITION BY [T_ID] ORDER BY DateEntered DESC) AS rn
FROM
Table
This returns the previous row, but not for the previous week so it's still not working. I think maybe a CTE with a case statement might work, but I can't figure it out and I hope someone can help?
I have tried to use the post below cut still can't get it to work:
To add some more detail there can be a multiple rows within the same week for example the one week there could be 2 rows for the same Task, Team etc, one being T_ID 1 and one being T_ID 26 the expected result would pick up the value for both on the previous week as they are for the same Team, Task and UserName.
答案1
得分: 1
这是一个SQL查询语句，用于获取数据。以下是翻译的结果：
未经测试，但我认为它应该给你提供数据
使用CTE（公共表表达式）：
选择UserName，Team，ID，DateEntered，Task，T_ID，ValueAmount
以及DATEPART(WK, DateEntered)作为WeekNo
从cte中选择
选择ThisWeek.*，LastWeek.ValueAmount作为LastWeek_ValueAmount
从以下查询结果中选择：
选择UserName，Team，T_ID，WeekNo，SUM(ValueAmount)作为ValueAmount
从cte中选择
按UserName，Team，T_ID，WeekNo进行分组
左连接：
选择UserName，Team，T_ID，WeekNo，SUM(ValueAmount)
从cte中选择
按UserName，Team，T_ID，WeekNo进行分组
其中LastWeek.UserName = ThisWeek.UserName，LastWeek.Team = ThisWeek.Team
并且LastWeek.T_ID = ThisWeek.T_ID，LastWeek.WeekNo = ThisWeek.WeekNo - 1
英文:
It is not tested, but I think it should give you the data
WITH cte AS (
SELECT UserName, Team, ID, [DateEntered], [Task], T_ID, ValueAmount
, DATEPART(WK, [DateEntered]) AS WeekNo
)
SELECT ThisWeek.*, LastWeek.ValueAmount AS LastWeek_ValueAmount
FROM (
SELECT UserName, Team, T_ID, WeekNo, SUM(ValueAmount) AS ValueAmount
FROM cte
GROUP BY UserName, Team, T_ID, WeekNo
) AS ThisWeek
LEFT JOIN (
SELECT UserName, Team, T_ID, WeekNo, SUM(ValueAmount)
FROM cte
GROUP BY UserName, Team, T_ID, WeekNo
) AS LastWeek ON LastWeek.UserName = ThisWeek.UserName AND LastWeek.Team = ThisWeek.Team
AND LastWeek.T_ID = ThisWeek.T_ID AND LastWeek.WeekNo = ThisWeek.WeekNo - 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论