sql - SQL-每周报告 7 天（周日至周六）

Question

我创建了以下查询，从周六和周日开始每 7 天重复一次。此报告将每 7 天生成一次。但是，在我们的 SFTP 文件夹（入站）报告中收到文件的日子里，我面临的问题应该有一个缺失 Null= 0 的条目。主要目标是使这个过程成为一个自动化过程，每周日到每 7 天的星期日 示例：

SELECT SubmitterID,SubmitterName,convert(varchar(15), DateReceived, 101) DateReceived,sum(ClaimCount) as TotalCount
FROM FalloutClaimReport
WHERE DateReceived BETWEEN '2019-06-01' AND '2019-06-07'
--ORDER BY COUNT(submitterID) DESC;
GROUP BY submitterid, SubmitterName, convert(varchar(15), DateReceived, 101)

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME

SET @StartDate = '2019-06-01' --AND '2019-06-10'
SET @StartDate = '2019-06-07'
SET @EndDate = GETDATE()
SET @CurrentDate = @StartDate

标签： sqlsql-servertsqlssissrs

Answer 1

我有点不确定你在寻找什么，但我认为一般的方法是你试图获得一周的数据。

日期计算

让我们从一些查询开始（这些假设是美国安装，因为默认日期是星期一。

SELECT
    DATEADD(WEEK, -1, CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST(GETDATE() AS date)), -1) AS date)) AS TheLastSundayOfTheFullWeek
,   DATEADD(WEEK, -1, CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST(GETDATE() AS date)), +5) AS date)) AS TheLastSaturdayOfTheFullWeek
,   CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST(GETDATE() AS date)), -1) AS date) AS SundayOfTheCurrentWeek
,   CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST(GETDATE() AS date)), +5) AS date) AS SaturdayOfTheCurrentWeek;

这些查询生成以下日期

TheLastSundayOfTheFullWeek TheLastSaturdayOfTheFullWeek SundayOfTheCurrentWeek SaturdayOfTheCurrentWeek
2019-06-30                 2019-07-06                   2019-07-07             2019-07-13

最后一整周将从 6/30 到 7/06。本周将定义为 7/7 到 7/13。

根据您需要的星期定义，选择适当的列对。

处理未知数

在这种情况下，我构建了一个虚拟表，其中包含我的报告应具有的所有预期日期（或元素）。然后我使用它来驱动与实际数据表的连接。由于我们不知道我们会找到给定日期的任何行，因此我使用 LEFT JOIN 连接表

SELECT
    FCR.SubmitterID
,   FCR.SubmitterName
,   CONVERT(varchar(15), ED.DateReceived, 101) AS DateReceived
,   SUM(FCR.ClaimCount) AS TotalCount
FROM
    (
        -- This logic builds out a list of all the dates that must exist on the report
        -- I used the logic for TheLastSundayOfTheFullWeek
        SELECT
            DATEADD(DAY, D.DayOffset, DATEADD(WEEK, -1, CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST(GETDATE() AS date)), -1) AS date))) AS DateReceived
        FROM
        (
            -- Generate a series of 7 numbers from 0 t 6
            SELECT TOP 7
                -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
            FROM
                sys.all_columns AS AC 
        ) D(DayOffset)
    ) AS ED
    LEFT OUTER JOIN
        dbo.FalloutClaimReport AS FCR
        ON FCR.DateReceived = ED.DateReceived
GROUP BY
    CONVERT(varchar(15), ED.DateReceived, 101)
,   FCR.SubmitterID
,   FCR.SubmitterName;

这会生成一个结果集，例如

我们没有 30 号或 5 号的数据，但查询中仍有记录。如果您需要其中的默认值，请使用 ISNULL/COALESCE 调用包装该列。

DBFiddle 版本提供测试沙箱