我想说的是，我是一个全新的存储过程，并且基本上是自学如何使用它们。任何建议或建议将不胜感激。如果可以的话，我会给你寄巧克力的。
要点:我的组织的客户在他们的第一次访问和每六次后续访问中进行调查。我们需要知道随着时间的推移，这个人是否表现出了进步。我们决定这样做的方法是比较第一个和最近的。因此，如果他们已经参加了18次会议，那么将比较第一次和第三次调查(因为他们在18次会议中完成了3次调查)。
我已经能够在一个存储过程中使用两个复杂的、多层嵌套的选择语句获得"第一"分数和"最近"分数。"第一个"是基于唯一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
这是我在听取了大家的建议后想出的解决方案。我想回去用我在某个时候学到的另一个新东西替换TOP(1):
select pc.*
from (select pc.*, row_number() over (partition by Clientkey, ProgramAdmitKey order by Date_Screening) as seqnum
from FD__CNSLG_BASIS24 PC) pc
where seqnum = 1
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
SET NOCOUNT ON;
Declare
@StartDate Date,
@EndDate Date
SET @StartDate = '1/1/2016'
SET @EndDate = '6/1/2016'
WITH CNSL_Clients AS (
SELECT PC_CNT.Clientkey, PC_Cnt.ProgramAdmitKey, PC_Cnt.OP__DOCID
FROM FD__Primary_Client as PC_Cnt
INNER JOIN VW__Cnsl_Session_Count_IndvFamOnly as cnt
ON PC_Cnt.Clientkey = CNT.Clientkey AND PC_Cnt.ProgramAdmitKey = CNT.ProgramAdmitKey
WHERE ((pc_CNT.StartDate between @StartDate AND @EndDate) OR (pc_CNT.StartDate <= @StartDate AND pc_CNT.ENDDate >= @StartDate) OR (pc_CNT.StartDate <= @StartDate AND pc_CNT.ENDDate is null))
AND CNT.SessionCount>=6
),
FIRST_BASIS AS (
SELECT CB24_1.OP__DOCID, CB24_1.Date_Screening, CB24_1.ClientKey, CB24_1.ProgramAdmitKey, CB24_1.Composite_score, CB24_1.Depression_Results,CB24_1.Emotional_Results, CB24_1.Relationships_Results
FROM FD__CNSLG_BASIS24 AS CB24_1
WHERE (CB24_1.OP__DOCID =
(Select TOP(1) CB24_2.OP__DOCID
FROM FD__CNSLG_BASIS24 AS CB24_2
Inner JOIN CNSL_Clients
ON CB24_2.ClientKey = CNSL_Clients.ClientKey AND CB24_2.ProgramAdmitKey = CNSL_Clients.ProgramAdmitKey
WHERE (CB24_1.ClientKey = CB24_2.ClientKey) AND (CB24_1.ProgramAdmitKey = CB24_2.ProgramAdmitKey)
ORDER BY CB24_2.Date_Screening))
),
RECENT_BASIS AS (
SELECT CB24_1.OP__DOCID, CB24_1.Date_Screening, CB24_1.ClientKey, CB24_1.ProgramAdmitKey, CB24_1.Composite_score, CB24_1.Depression_Results,CB24_1.Emotional_Results, CB24_1.Relationships_Results
FROM FD__CNSLG_BASIS24 AS CB24_1
WHERE (CB24_1.OP__DOCID =
(Select TOP(1) CB24_2.OP__DOCID
FROM FD__CNSLG_BASIS24 AS CB24_2
Inner JOIN CNSL_Clients
ON CB24_2.ClientKey = CNSL_Clients.ClientKey AND CB24_2.ProgramAdmitKey = CNSL_Clients.ProgramAdmitKey
WHERE (CB24_1.ClientKey = CB24_2.ClientKey) AND (CB24_1.ProgramAdmitKey = CB24_2.ProgramAdmitKey)
ORDER BY CB24_2.Date_Screening DESC))
)
SELECT F.OP__DOCID AS First_DOCID,R.OP__DOCID as Recent_DOCID,F.ClientKey, F.ProgramAdmitKey, F.Composite_Score AS FComposite_Score, R.Composite_Score as RComposite_Score, Composite_Change = R.Composite_Score - F.Composite_Score, F.Depression_Results AS FDepression_Results, R.Depression_Results AS RDepression_Resluts, Depression_Change = R.Depression_Results - F.Depression_Results, F.Emotional_Results AS FEmotional_Resluts, R.Emotional_Results AS REmotionall_Reslu, Emotional_Change = R.Emotional_Results - F.Emotional_Results, F.Relationships_Results AS FRelationships_Resluts, R.Relationships_Results AS RRelationships_Resluts, Relationship_Change = R.Relationships_Results - F.Relationships_Results
FROM First_basis AS F
FULL Outer JOIN RECENT_BASIS AS R
ON F.ClientKey = R.ClientKey AND F.ProgramAdmitKey = R.ProgramAdmitKey
ORDER BY F.ClientKey
END
GO