主页 / 问题 / 755162
Accepted
Daniyal Lukmanov
Asked:2020-12-07 21:37:09 +0000 UTC2020-12-07 21:37:09 +0000 UTC

如何在 SQL Server 的一个表中获取多年的每周报告

  • 772

有一张表格，其中列出了 5 年指定日期的费用类别和金额。您想要获取整个期间每个类别的每周总计总数。也就是说，在每周结束时，您需要显示该类别的总金额。所有这些都必须以一张表格的形式获得。这就是我卡住的地方。我的脚本将每对值输出为单独的一行表格。脚本如下

DECLARE @i INT = 2; -- Инкремент AccountID
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
DECLARE @LastDate DATETIME = (SELECT TOP 1 TransactDate FROM TempTransactions ORDER BY TransactDate DESC);
    WHILE @i <= 3
      BEGIN
        SET @StartDate = '20120101'
        SET @EndDate = '20120107'
        WHILE @EndDate <= @LastDate
          BEGIN
                SELECT S.AccountID, SUM(Baks) As Summa
                FROM (
                SELECT O.TransactDate, O.AccountID, (O.Amount/CONVERT(MONEY, REPLACE(Col2, ',', '.'))) AS Baks 
                FROM (
                SELECT T.AccountID, T.Amount, T.TransactDate, R.Col2 
                FROM TransactsAndRates.dbo.TempTransactions T
                INNER JOIN TransactsAndRates.dbo.Rates R ON T.TransactDate = R.Col1) AS O
                WHERE O.AccountID = @i) AS S
                WHERE S.TransactDate BETWEEN @StartDate AND @EndDate
                GROUP BY S.AccountId
            SET @StartDate = DATEADD(DAY, 7, @StartDate)
            SET @EndDate = DATEADD(DAY, 7, @EndDate)
          END
    SET @i += 1
    END

如何将这些结果垂直“粘合”到一张表中？也许我没有走正确的路。

由于指定的脚本，我得到了很多表格的表格

AccountID |Summa    |
---------------------
2         |237.1244 |

最初，数据取自表格

TransactDate |Amount     |AccountID |
-------------|-----------|----------|
01-01-2012   |11506.0000 |2         |
01-01-2012   |8899.0000  |2         |
02-01-2012   |39.0000    |2         |
03-01-2012   |238.0000   |2         |
04-01-2012   |10023.0000 |2         |
04-01-2012   |565.0000   |2         |
06-01-2012   |357.0000   |2         |
09-01-2012   |10761.0000 |2         |
09-01-2012   |446.0000   |2         |
09-01-2012   |813.0000   |2         |
09-01-2012   |531.0000   |2         |
11-01-2012   |3597.0000  |2         |
12-01-2012   |2068.0000  |2         |
13-01-2012   |10706.0000 |2         |
13-01-2012   |187.0000   |2         |
13-01-2012   |11227.0000 |2         |
14-01-2012   |3019.0000  |2         |
14-01-2012   |969.0000   |2         |
18-01-2012   |1989.0000  |2         |
19-01-2012   |204.0000   |2         |
19-01-2012   |7879.0000  |2         |
20-01-2012   |483.0000   |2         |

期望的结果

StartDate | EndDate  | AccountID |Summa    |
-------------------------------------------
01.07.2012|07.01.2012| 2         |237.1244 |
08.07.2012|15.01.2012| 2         |557.244  |
16.07.2012|23.01.2012| 2         |426.44   |
16.07.2012|23.01.2012| 3         |100.789  |

差不多，但当然更多。

sql

2 个回答

  1. Best Answer

    就像是：

    SELECT dateadd(day, (datediff(day, '2012-01-01',TransactDate)/7)*7,'2012-01-01') as StartDate,
       dateadd(day, (datediff(day, '2012-01-01',TransactDate)/7)*7+6,'2012-01-01') as EndDate
        S.AccountID, SUM(Baks) As Summa
   ...
 group by S.AccountID, datediff(day, '2012-01-01',TransactDate)/7

    可以稍微调整公式以获得所需一周的正确开始或结束日期。

    PS 如果在您看来您需要一个循环来解决 SQL 中的问题，那么您有 95% 的可能性是从错误的角度看待问题。对于 SQL 允许在没有循环的情况下接收任何部分中的数据，一个请求。

    • 3

  2. 使用递归 CTE 获取日期的变体：

    ;WITH cte AS (
SELECT @startdate AS datebegin, DATEADD(DAY,6,@startDate) dateend
UNION ALL
SELECT DATEADD(DAY,1,dateend), DATEADD(DAY, 7, dateend) FROM cte
WHERE dateend<=@enddate)
SELECT datebegin, dateend, SUM(t.amount) amount, t.accountId FROM cte
LEFT JOIN transactions t ON t.TransactionDate BETWEEN datebegin AND dateend
GROUP BY datebegin, dateend, accountId

    在 CTE 中，我们有 7 天的时间，然后加入并分组。

    • 0

相关问题