sql - 使用多个不断变化的 SELECT 结果执行数学运算
问题描述
首先，我查看了其他标题相似的问题，它们对我没有帮助。
我的问题：我需要根据与特定日期关联的结果集执行百分比计算。日期不固定，有好几个。从检索日期开始，需要在 12 个月的周期内重复计算。每个日期都必须独立于其他日期来处理。当使用一个指定的日期时，我的查询按预期执行。不得不处理多个日期是我的问题开始的地方。
如果我的查询知道确切日期（仅显示查询的 3 个月部分），则我的查询如何工作的示例：
DECLARE @StartDate as date,
@Customer as varchar(7),
@TotalPlaced as money
SET @StartDate = '2018-01-04'
SET @Customer = '0000054'
SET @TotalPlaced = (SELECT SUM(original) FROM master with (nolock) WHERE
customer = @Customer AND received = @StartDate)
SELECT (SUM(gross)/@TotalPlaced)*100
FROM PAYMENTS with (nolock)
WHERE customer = @Customer
AND received = @StartDate
AND entered between @StartDate and (SELECT DATEADD(s,-1,DATEADD(mm,
DATEDIFF(m,0,@StartDate)+1,0)))
SELECT (SUM(gross)/@TotalPlaced)*100
FROM PAYMENTS with (nolock)
WHERE customer = @Customer
AND received = @StartDate
AND entered between (SELECT DATEADD(m, DATEDIFF(m, 0, @StartDate)+1, 0))
AND (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+2,0)))
SELECT (SUM(gross)/@TotalPlaced)*100
FROM PAYMENTS with (nolock)
WHERE customer = @Customer
AND received = @StartDate
AND entered between (SELECT DATEADD(m, DATEDIFF(m, 0, @StartDate)+2, 0))
AND (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+3,0)))
这将产生结果集：
1.00
0.17
0.15
现在，如果我稍微更改查询以查找指定日期之后的任何日期并按这些日期分组，则数学计算不正确，并且查询不会独立处理日期值。例子：
DECLARE @StartDate as date,
@TotalPlaced as money
SET @StartDate = '2018-01-04'
SET @TotalPlaced = (SELECT SUM(original) FROM master with (nolock) WHERE
customer = '0000054'AND received = @StartDate)
SELECT received, (SUM(gross)/@TotalPlaced)*100
FROM PAYMENTS with (nolock)
WHERE customer = '0000054'
AND received >= @StartDate
AND entered between @StartDate and (SELECT DATEADD(s,-1,DATEADD(mm,
DATEDIFF(m,0,@StartDate)+1,0)))
GROUP BY received
ORDER BY received
SELECT received, (SUM(gross)/@TotalPlaced)*100
FROM PAYMENTS with (nolock)
WHERE customer = '0000054'
AND received >= @StartDate
AND entered between (SELECT DATEADD(m, DATEDIFF(m, 0, @StartDate)+1, 0))
AND (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+2,0)))
GROUP BY received
ORDER BY received
SELECT received, (SUM(gross)/@TotalPlaced)*100
FROM PAYMENTS with (nolock)
WHERE customer = '0000054'
AND received >= @StartDate
AND entered between (SELECT DATEADD(m, DATEDIFF(m, 0, @StartDate)+2, 0))
AND (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+3,0)))
GROUP BY received
ORDER BY received
以这种方式完成，日期的值似乎混合在一起，并且它也没有像我需要的那样完全分开处理日期。查询现在只包括从与该日期关联的月份开始的计算，而不是每个日期的 12 个月结果集（“2018-03-02”将在第 3 个月开始显示结果“2018-04-03”将开始显示第 4 个月的结果，当它们应该显示从第 1 个月开始时）并且实际数学也是错误的，例如 2018-04-03 的结果应该是 0.77
received percentage
2018-01-04 0.17
2018-03-02 0.05
2018-04-03 18.08
从这里我尝试做一个 WHILE 循环，认为这将提供我需要的独立性。
DECLARE @StartDate as date,
@Customer as varchar(7),
@TotalPlaced as money
SET @StartDate = '2018-01-04'
SET @Customer = '0000054'
SET @TotalPlaced = (SELECT SUM(original) FROM master with (nolock) WHERE
customer = @Customer AND received = @StartDate)
CREATE TABLE #Totals
(ReceivedDate date,
Processed int null,
M1 varchar(15) null,
M2 varchar(15) null,
M3 varchar(15) null)
INSERT INTO #Totals
SELECT distinct(received), 1, null, null, null
FROM master with (nolock)
WHERE customer = @Customer
AND received >= @StartDate
--VARIABLES FOR LOOP
DECLARE @CHKDATE date = ''
DECLARE @RECORDCOUNT int = (SELECT COUNT(ReceivedDate) FROM #Totals)
--LOOP SYNTAX
WHILE (@RECORDCOUNT > 0)
BEGIN
SELECT top 1 @CHKDATE = (SELECT MIN(ReceivedDate) FROM #Totals WHERE
Processed = 1)
FROM #Totals
WHERE processed = 1
IF @@ROWCOUNT = 0 BREAK;
UPDATE #Totals SET M1 =
(SELECT (SUM(gross)/@TotalPlaced)*100
FROM PAYMENTS with (nolock)
WHERE customer = '0000054'
AND received = @CHKDATE
AND entered between @CHKDATE and (SELECT DATEADD(s,-1,DATEADD(mm,
DATEDIFF(m,0,@CHKDATE)+1,0))))
UPDATE #Totals SET M2 =
(SELECT (SUM(gross)/@TotalPlaced)*100
FROM PAYMENTS with (nolock)
WHERE customer = '0000054'
AND received = @CHKDATE
AND entered between (SELECT DATEADD(m, DATEDIFF(m, 0, @CHKDATE)+1,
0)) and (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@CHKDATE)+2,0))))
UPDATE #Totals SET M3 =
(SELECT (SUM(gross)/@TotalPlaced)*100
FROM PAYMENTS with (nolock)
WHERE customer = '0000054'
AND received = @CHKDATE
AND entered between (SELECT DATEADD(m, DATEDIFF(m, 0, @CHKDATE)+2,
0)) and (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@CHKDATE)+3,0))))
UPDATE #Totals SET processed = 0 WHERE ReceivedDate = @CHKDATE
SET @RECORDCOUNT = @RECORDCOUNT - 1
END
结果仍然是错误的。
ReceivedDate Processed M1 M2 M3
2018-01-04 0 0.49 0.95 0.54
2018-02-02 0 0.49 0.95 0.54
2018-03-02 0 0.49 0.95 0.54
2018-04-03 0 0.49 0.95 0.54
2018-05-02 0 0.49 0.95 0.54
2018-06-06 0 0.49 0.95 0.54
2018-07-05 0 0.49 0.95 0.54
我可以使用帮助来查看我在循环中哪里出了问题，或者任何关于如何达到预期结果的建议。下图是我的整体报告应该是什么样子。查询的所有其余部分都可以正常工作，这只是我挂断的最后一部分。
解决方案
经过几天将查询分解为较小的部分后，我发现了问题。循环变量不会在循环的每次迭代中被刷新。我还通过设置需要处理的记录的临时表来稍微更改迭代方法，然后在循环处理时删除每条记录。如果其他人发现他们需要类似 WHILE 循环的帮助，这里是修改后的语法：
---------------------Create temp tables for WHILE Loop
CREATE TABLE #LoopTemp
(ID INT IDENTITY(1, 1) primary key,
ReceivedDate date)
CREATE TABLE #LoopTotals
(ID INT IDENTITY(1, 1) primary key,
ReceivedDate date,
M1 varchar(15) null,
M2 varchar(15) null,
M3 varchar(15) null,
M4 varchar(15) null)
------------------------Populate #LoopTemp with receive dates
INSERT INTO #LoopTemp
SELECT distinct(received)
FROM master with (nolock)
WHERE customer = @Customer
AND received >= @StartDate
--------VARIABLES FOR LOOP
DECLARE @CHKDATE date = ''
DECLARE @RECORDCOUNT int = (SELECT COUNT(ReceivedDate) FROM #LoopTemp)
DECLARE @PKID int
DECLARE @TotalPlaced as money
-------LOOP SYNTAX
WHILE (@RECORDCOUNT > 0)
BEGIN
SELECT @PKID = (SELECT MIN(ID) FROM #LoopTemp)
SELECT @CHKDATE = (SELECT MIN(ReceivedDate) FROM #LoopTemp WHERE ID = @PKID)
SET @TotalPlaced = (SELECT SUM(original) FROM master with (nolock) WHERE customer
= @Customer AND received = @CHKDATE)
IF @@ROWCOUNT = 0 BREAK;
INSERT INTO #LoopTotals SELECT @CHKDATE
((SUM(gross)/@TotalPlaced)*100),null,null,null,null
FROM PAYMENTS with (nolock)
WHERE customer = @Customer
AND received = @CHKDATE
AND entered between @CHKDATE and (SELECT DATEADD(s,-1,DATEADD(mm,
DATEDIFF(m,0,@CHKDATE)+1,0)))
UPDATE #LoopTotals SET M2 =
(SELECT (SUM(gross)/@TotalPlaced)*100
FROM PAYMENTS with (nolock)
WHERE customer = @Customer
AND received = @CHKDATE
AND entered between (SELECT DATEADD(m, DATEDIFF(m, 0, @CHKDATE)+1, 0)) and
(SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@CHKDATE)+2,0))))
WHERE ReceivedDate = @CHKDATE
AND ID = @PKID
UPDATE #LoopTotals SET M3 =
(SELECT (SUM(gross)/@TotalPlaced)*100
FROM PAYMENTS with (nolock)
WHERE customer = @Customer
AND received = @CHKDATE
AND entered between (SELECT DATEADD(m, DATEDIFF(m, 0, @CHKDATE)+2, 0)) and
(SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@CHKDATE)+3,0))))
WHERE ReceivedDate = @CHKDATE
AND ID = @PKID
UPDATE #LoopTotals SET M4 =
(SELECT (SUM(gross)/@TotalPlaced)*100
FROM PAYMENTS with (nolock)
WHERE customer = @Customer
AND received = @CHKDATE
AND entered between (SELECT DATEADD(m, DATEDIFF(m, 0, @CHKDATE)+3, 0)) and
(SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@CHKDATE)+4,0))))
WHERE ReceivedDate = @CHKDATE
AND ID = @PKID
--Advance loop to next record by deleting the record just ran
DELETE FROM #LoopTemp WHERE ReceivedDate = @CHKDATE
SET @RECORDCOUNT = @RECORDCOUNT - 1
END
推荐阅读
- mongodb - 在 Go 中使用 MongoDb 默认驱动程序创建唯一索引
- javascript - 当暗模式处于活动状态时，如何防止页面在页面转换之间闪烁到其原始的亮模式样式
- react-native - 如何使用 SQL 数据在本机反应中绘制图形？
- pointers - 当类型在结构定义中明确指定时，无法推断类型参数 T 的类型
- windows - 如何从 WDM 流式音频源（Realtek HD 音频）中捕获
- regex - RegEx 用于包含 2 个小数点的百分比范围
- .net - Visual Studio Online 是否需要在您的计算机上安装 Node.js、.Net Core 或 .Net Framework 才能调试和运行？
- python - Pydoop 与 Mrjob 在 Hadoop 上进行图像处理
- c - K&R 练习 1-9：第二个 while 循环条件说明
- java - AWS S3 分块上传文件，小于 5MB