要点：我组织的客户在他们的初次访问和以后的第6次访问中进行了一次调查。我们需要知道个人是否随着时间的推移而有所改善。我们决定执行此操作的方法是将第一个与最新的进行比较。因此，如果他们去过18场会议，那么将比较第一和第三次调查（因为他们将在18场会议中完成3次调查）。
我已经能够通过一个存储过程中的两个复杂的，多层嵌套的select语句获得“第一”得分和“最近”得分。“第一个”是在唯一ID（DOCID）上链接的TOP（1），然后按日期排序。“最近的”是链接到唯一ID（DOCID）的TOP（1），然后按日期降序排列。这可以使我准确地获得每个语句中需要的内容，但是并不能正确地输出我需要的内容，这显然是语句中的顺序。
最终结果将是创建带有授权报告目的的Crystal Report。
Declare
@StartDate Date,
@EndDate Date,
@First_DOCID Int,
@First_Clientkey Int,
@First_Date_Screening Date,
@First_Composite_Score Float,
@First_Depression_Score Float,
@First_Emotional_Score Float,
@First_Relationship_Score Float,
@Recent_DOCID Int,
@Recent_Clientkey Int,
@Recent_Date_Screening Date,
@Recent_Composite_Score Float,
@Recent_Depression_Score Float,
@Recent_Emotional_Score Float,
@Recent_Relationship_Score Float,
@Difference_Composit_Score Float,
@Difference_Depression_Score Float,
@Difference_Emotional_Score Float,
@Difference_Relationship_Score Float
SET @StartDate = '1/1/2016'
SET @EndDate = '6/1/2016'
BEGIN
SELECT @First_DOCID = CB24_1.OP__DOCID, @First_Date_Screening = CB24_1.Date_Screening, @First_Clientkey = CB24_1.ClientKey, @First_Composite_Score = CB24_1.Composite_score, @First_Depression_Score = CB24_1.Depression_Results, @First_Emotional_Score = CB24_1.Emotional_Results, @First_Relationship_Score = CB24_1.Relationships_Results
FROM FD__CNSLG_BASIS24 AS CB24_1
WHERE (CB24_1.OP__DOCID =
(Select TOP(1) CB24_2.OP__DOCID
...
ORDER BY CB24_2.Date_Screening))
ORDER BY ClientKey DESC
END
BEGIN
SELECT @Recent_DOCID = CB24_1.OP__DOCID, @Recent_Date_Screening = CB24_1.Date_Screening, @Recent_Clientkey = CB24_1.ClientKey, @Recent_Composite_Score = CB24_1.Composite_score, @Recent_Depression_Score = CB24_1.Depression_Results, @Recent_Emotional_Score = CB24_1.Emotional_Results, @Recent_Relationship_Score = CB24_1.Relationships_Results
FROM FD__CNSLG_BASIS24 AS CB24_1
WHERE (CB24_1.OP__DOCID =
(Select TOP(1) CB24_2.OP__DOCID
...
ORDER BY CB24_2.Date_Screening DESC))
ORDER BY ClientKey
END
SET @Difference_Composit_Score = (@Recent_Composite_Score - @First_Composite_Score)
SET @Difference_Depression_Score = (@Recent_Depression_Score - @First_Depression_Score)
SET @Difference_Emotional_Score = (@Recent_Emotional_Score - @First_Emotional_Score)
SET @Difference_Relationship_Score = (@Recent_Relationship_Score - @First_Relationship_Score)
SELECT
@First_DOCID AS First_Docid,
@First_Clientkey AS First_Clientkey,
@First_Date_Screening AS First_Date_Screening,
@First_Composite_Score AS First_Composite_Score,
@First_Depression_Score AS First_Depression_Score,
@First_Emotional_Score AS First_Emotional_Score,
@First_Relationship_Score AS First_Relationship_Score,
@Recent_DOCID AS Recent_DOCID,
@Recent_Clientkey AS Recent_Clientkey,
@Recent_Date_Screening AS Recent_Date_Screening,
@Recent_Composite_Score AS Recent_Composite_Score,
@Recent_Depression_Score AS Recent_Depression_Score,
@Recent_Emotional_Score AS Recent_Emotional_Score,
@Recent_Relationship_Score AS Recent_Relationship_Score,
@Difference_Composit_Score AS Difference_Composit_Score,
@Difference_Depression_Score AS Difference_Depression_Score,
@Difference_Emotional_Score AS Difference_Emotional_Score,
@Difference_Relationship_Score AS Difference_Relationship_Score
在SQL中，您不需要不必要的声明变量。
这是一个人为设计但可复制的示例，该示例利用了常见的表表达式和窗口函数，可以使您朝正确的方向发展。我从模板创建了具有必要输入参数的存储过程（在现实生活中，您希望避免使用该参数）。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.Client_Improvement_Results
(@StartDate DATETIME, @EndDate DATETIME)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
-- You would never do this in real-life but for a simple reproducible example...
DECLARE @Survey TABLE
(
Clientkey INT,
Date_Screening DATE,
Composite_Score FLOAT
)
INSERT INTO @Survey
VALUES
(1, '2014-04-01', 42.1),
(1, '2014-04-10', 46.1),
(1, '2014-04-20', 48.1),
(2, '2014-05-10', 40.1),
(2, '2014-05-20', 30.1),
(2, '2014-05-30', 10.1)
;
--Use Common Table Expression & Window Functions to ID first/recent visit by client
WITH CTE AS (
SELECT
S.Clientkey
,S.Composite_Score
,S.Date_Screening
,First_Date_Screening = MIN(S.Date_Screening) OVER(PARTITION BY S.Clientkey)
,Recent_Date_Screening = MAX(S.Date_Screening) OVER(PARTITION BY S.Clientkey)
FROM @Survey AS S
)
--Self join of CTE with proper filters
--applied allows you to return differences in one row
SELECT
f.Clientkey
,f.First_Date_Screening
,f.Recent_Date_Screening
,Difference_Score = r.Composite_Score - f.Composite_Score
FROM
CTE AS f --first
INNER JOIN CTE AS r --recent
ON f.Clientkey = r.Clientkey
WHERE
f.Date_Screening = f.First_Date_Screening
AND r.Date_Screening = r.Recent_Date_Screening
END
GO
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句