需要基于一个值复制行。

huangapple
go评论60阅读模式
英文:

Need to Replicate Rows based on a value

问题

使用现有的SQL语句，如果[Action Taken]的值等于"See Previous Actions"，我需要复制行。

"See Previous Actions" 意味着参考此案例中案件参与者接收的所有操作。基本上，每个[ViolationCategory]中的 "See Previous Actions" 应填充为此案例和参与者的其他类别相同的操作。

在我的示例中有两个参与者，如您所看到的 [CaseParticipantID]，其中一个填写了 [Action Taken]，而另一个没有。

我假设...数据可以使用案件编号和案件参与者ID与自身联接，但我不知道如何执行。

来自SQL语句的数据（视图仅限于易于阅读的相关列]：

案件编号 案件参与者ID 违规类别 执行操作
123 456 利益冲突 报酬
123 456 利益冲突 谴责
123 456 利益冲突 其他
123 789 利益冲突 口头谴责
123 456 偏袒 查看以前的操作

期望结果

案件编号 案件参与者ID 违规类别 执行操作
123 456 利益冲突 报酬
123 456 利益冲突 谴责
123 456 利益冲突 其他
123 789 利益冲突 口头谴责
123 456 偏袒 报酬
123 456 偏袒 谴责
123 456 偏袒 其他

我当前的SQL语句，筛选为只有一个案件的情况如下：

SELECT
     Cases.CaseNumber
    ,Cases.DateOpened
    ,Cases.DateClosed
    ,Participant.CaseParticipantId
    ,Participant.RoleInCase
    ,Participant.RelToOrganization
    ,Participant.Practice
    ,Participant.City
    ,Issues.Issue
    ,Issues.IssueSubCategory
    ,Issues.CaseIssueId
    ,Issues.Outcome
    ,ParticipantAlias.ParticipantName
    ,CaseAction.ActionTaken
FROM [Warehouse].[Table].[Case] as Cases
LEFT JOIN [Warehouse].[Table].[CaseParticipant] as Participant
    ON Cases.CaseNumber = Participant.CaseNumber

LEFT JOIN [Warehouse].[Table].[CaseIssue] as Issues
    ON Participant.CaseParticipantId = Issues.CaseParticipantId

LEFT JOIN [Warehouse].[Table].[CaseAction] as CaseAction
    ON Issues.CaseIssueId = CaseAction.CaseIssueId

LEFT JOIN [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
    ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId

WHERE [Cases].[CaseNumber] = '123'
英文:

Using an existing SQL statement, I need to duplicate rows if value in [Action Taken] equals "See Previous Actions".

“See Previous Actions” means refer back to all actions Case Participant received for this case.
Basically, each [ViolationCategory] with "See Previous Actions" should be filled in with the same actions as other Categories for this case and participant.

I have two participants in my example as you see by the [CaseParticipantID] one that has [Action Taken] filled in and the other one does not.

I assume...data can be unioned back to itself using Case Number and Case Participant ID, but I do not know how to do that.

Data from SQL Statement (View limited to only pertinent columns for easier reading]

Case Number CaseParticipantID ViolationCategory Action Taken
123 456 Conflict of Interest Compensation
123 456 Conflict of Interest Reprimand
123 456 Conflict of Interest Other
123 789 Conflict of Interest Verbal Reprimand
123 456 Favoritism See Previous Action

Desired Outcome

Case Number CaseParticipantID ViolationCategory Action Taken
123 456 Conflict of Interest Compensation
123 456 Conflict of Interest Reprimand
123 456 Conflict of Interest Other
123 789 Conflict of Interest Verbal Reprimand
123 456 Favoritism Compensation
123 456 Favoritism Reprimand
123 456 Favoritism Other

My current SQL Statement, filtered down to just one case is:

SELECT
     Cases.CaseNumber
    ,Cases.DateOpened
    ,Cases.DateClosed
    ,Participant.CaseParticipantId
    ,Participant.RoleInCase
    ,Participant.RelToOrganization
    ,Participant.Practice
    ,Participant.City
    ,Issues.Issue
    ,Issues.IssueSubCategory
    ,Issues.CaseIssueId
    ,Issues.Outcome
    ,ParticipantAlias.ParticipantName
    ,CaseAction.ActionTaken
FROM [Warehouse].[Table].[Case] as Cases
LEFT JOIN [Warehouse].[Table].[CaseParticipant] as Participant
    ON Cases.CaseNumber = Participant.CaseNumber

LEFT JOIN [Warehouse].[Table].[CaseIssue] as Issues
    ON Participant.CaseParticipantId = Issues.CaseParticipantId

LEFT JOIN [Warehouse].[Table].[CaseAction] as CaseAction
    ON Issues.CaseIssueId = CaseAction.CaseIssueId

LEFT JOIN [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
    ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId

WHERE [Cases].[CaseNumber] = '123'

</details>


# 答案1
**得分**: 0

根据您的查询，我无法确定应该是视图还是存储过程，但我建议使用后者。以下是使用存储过程实现预期输出的示例。输出如下：

```sql
USE [Warehouse]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_GetCaseResult]
	-- 添加存储过程的参数
	@caseNumber as int
AS
BEGIN
	SET NOCOUNT ON;

	DROP TABLE IF EXISTS #Temp_table
	-- 1. 将原始查询结果存储到临时表
	SELECT
	Cases.CaseNumber
	,Cases.DateOpened
	,Cases.DateClosed
	,Participant.CaseParticipantId
	,Participant.CaseParticipantKey
	,Participant.ParticipantAliasId
	,Participant.RoleInCase
	,Participant.RelToOrganization
	,Participant.Practice
	,Participant.City
	,Issues.Issue
	,Issues.IssueSubCategory
	,Issues.CaseIssueId
	,Issues.Outcome
	,ParticipantAlias.ParticipantName
	,CaseAction.ActionTaken
	INTO #Temp_table
	FROM [Warehouse].[Table].[Case] as Cases
	LEFT JOIN
	[Warehouse].[Table].[CaseParticipant] as Participant
	ON Cases.CaseNumber = Participant.CaseNumber
	LEFT JOIN
	[Warehouse].[Table].[CaseIssue] as Issues
	ON Participant.CaseParticipantId = Issues.CaseParticipantId
	LEFT JOIN
	[Warehouse].[Table].[CaseAction] as CaseAction
	ON Issues.CaseIssueId = CaseAction.CaseIssueId
	LEFT JOIN
	[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
	ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
	WHERE [Cases].[CaseNumber] = @caseNumber

	-- 2. 从临时表中查询，排除 "查看以前的操作"
	SELECT * FROM #Temp_table t1 WHERE t1.ActionTaken <> '查看以前的操作'

	-- "联接"两个结果
	UNION ALL

	-- 3. 从临时表中查询，联接两个带和不带 "查看以前的操作" 的表，并获取所需的输出
	SELECT
		 t1.CaseNumber
		,t1.DateOpened
		,t1.DateClosed
		,t1.CaseParticipantId
		,t1.CaseParticipantKey
		,t1.ParticipantAliasId
		,t1.RoleInCase
		,t1.RelToOrganization
		,t1.Practice
		,t1.City
		,t1.Issue
		,t1.IssueSubCategory
		,t1.CaseIssueId
		,t1.Outcome
		,t1.ParticipantName
		,t2.ActionTaken
	FROM #Temp_table t2
	INNER JOIN #Temp_table t1 ON t2.CaseNumber = t1.CaseNumber AND t2.CaseParticipantId = t1.CaseParticipantId
	WHERE t1.ActionTaken = '查看以前的操作' AND t2.ActionTaken <> '查看以前的操作';

END
GO

或者，如果您真的希望不使用存储过程，以下是一个简单查询的示例：

DECLARE @caseNumber int = '123'

SELECT DISTINCT 
     t1.CaseNumber
    ,t1.DateOpened
    ,t1.DateClosed
    ,t1.CaseParticipantId
    ,t1.CaseParticipantKey
    ,t1.ParticipantAliasId
    ,t1.RoleInCase
    ,t1.RelToOrganization
    ,t1.Practice
    ,t1.City
    ,t1.Issue
    ,t1.IssueSubCategory
    ,t1.CaseIssueId
    ,t1.Outcome
    ,t1.ParticipantName
    ,t2.ActionTaken
FROM (
    SELECT
    Cases.CaseNumber
    ,Cases.DateOpened
    ,Cases.DateClosed
    ,Participant.CaseParticipantId
    ,Participant.CaseParticipantKey
    ,Participant.ParticipantAliasId
    ,Participant.RoleInCase
    ,Participant.RelToOrganization
    ,Participant.Practice
    ,Participant.City
    ,Issues.Issue
    ,Issues.IssueSubCategory
    ,Issues.CaseIssueId
    ,Issues.Outcome
    ,ParticipantAlias.ParticipantName
    ,CaseAction.ActionTaken
    FROM [Warehouse].[Table].[Case] as Cases
    LEFT JOIN
    [Warehouse].[Table].[CaseParticipant] as Participant
    ON Cases.CaseNumber = Participant.CaseNumber
    LEFT JOIN
    [Warehouse].[Table].[CaseIssue] as Issues
    ON Participant.CaseParticipantId = Issues.CaseParticipantId
    LEFT JOIN
    [Warehouse].[Table].[CaseAction] as CaseAction
    ON Issues.CaseIssueId = CaseAction.CaseIssueId
    LEFT JOIN
    [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
    ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
    WHERE [Cases].[CaseNumber] = @caseNumber --123
) t1
FULL OUTER JOIN (
    SELECT
    Cases.CaseNumber
    ,Cases.DateOpened
    ,Cases.DateClosed
    ,Participant.CaseParticipantId
    ,Participant.CaseParticipantKey
    ,Participant.ParticipantAliasId
    ,Participant.RoleInCase
    ,Participant.RelToOrganization
    ,Participant.Practice
    ,Participant.City
    ,Issues.Issue
    ,Issues.IssueSubCategory
    ,Issues.CaseIssueId
    ,Issues.Outcome
    ,ParticipantAlias.ParticipantName
    ,CaseAction.ActionTaken
    FROM [Warehouse].[Table].[Case] as Cases
    LEFT JOIN
    [Warehouse].[Table].[CaseParticipant] as Participant
    ON Cases.CaseNumber = Participant.CaseNumber
    LEFT JOIN
    [Warehouse].[Table].[CaseIssue] as Issues
    ON Participant.CaseParticipantId = Issues.CaseParticipantId
    LEFT JOIN
    [Warehouse].[Table].[CaseAction] as CaseAction
    ON Issues.CaseIssueId = CaseAction.CaseIssueId
    LEFT JOIN
    [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
    ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
    WHERE [Cases].[CaseNumber] = @caseNumber --123 
) t2 
    ON t2.CaseNumber = t1.CaseNumber AND t2.CaseParticipantId = t1.CaseParticipantId
    WHERE t2.ActionTaken <> '查看以前的操作';
英文:

Based on your query, I cannot tell whether it should be a view or procedure, but I'd suggest making the latter.
Here is an example of how to achieve the expected output with a stored procedure.
The output:
Stored Procedure Sample Output

USE [Warehouse]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_GetCaseResult]
-- Add the parameters for the stored procedure here
@caseNumber as int
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE IF EXISTS #Temp_table
-- 1. Original query into temp table
SELECT
Cases.CaseNumber
,Cases.DateOpened
,Cases.DateClosed
,Participant.CaseParticipantId
,Participant.CaseParticipantKey
,Participant.ParticipantAliasId
,Participant.RoleInCase
,Participant.RelToOrganization
,Participant.Practice
,Participant.City
,Issues.Issue
,Issues.IssueSubCategory
,Issues.CaseIssueId
,Issues.Outcome
,ParticipantAlias.ParticipantName
,CaseAction.ActionTaken
INTO #Temp_table
FROM [Warehouse].[Table].[Case] as Cases
LEFT JOIN
[Warehouse].[Table].[CaseParticipant] as Participant
ON Cases.CaseNumber = Participant.CaseNumber
LEFT JOIN
[Warehouse].[Table].[CaseIssue] as Issues
ON Participant.CaseParticipantId = Issues.CaseParticipantId
LEFT JOIN
[Warehouse].[Table].[CaseAction] as CaseAction
ON Issues.CaseIssueId = CaseAction.CaseIssueId
LEFT JOIN
[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
WHERE [Cases].[CaseNumber] = @caseNumber
-- 2. Query from temptable without &quot;See previous actions&quot;
SELECT * FROM #Temp_table t1 WHERE t1.ActionTaken &lt;&gt; &#39;See Previous Actions&#39;
--  &quot;Joining&quot; the two results
UNION ALL
-- 3. Query from temptable with joining the two table with and without &quot;See previous actions&quot; and getting needed output.
SELECT
t1.CaseNumber
,t1.DateOpened
,t1.DateClosed
,t1.CaseParticipantId
,t1.CaseParticipantKey
,t1.ParticipantAliasId
,t1.RoleInCase
,t1.RelToOrganization
,t1.Practice
,t1.City
,t1.Issue
,t1.IssueSubCategory
,t1.CaseIssueId
,t1.Outcome
,t1.ParticipantName
,t2.ActionTaken
FROM #Temp_table t2
INNER JOIN #Temp_table t1 ON t2.CaseNumber = t1.CaseNumber AND t2.CaseParticipantId = t1.CaseParticipantId
WHERE t1.ActionTaken = &#39;See Previous Actions&#39; AND t2.ActionTaken &lt;&gt; &#39;See Previous Actions&#39;
END
GO

OR If you really want it without stored-procedure: Here is an example for it:

Example of simple query

DECLARE @caseNumber int = &#39;123&#39;
SELECT DISTINCT 
t1.CaseNumber
,t1.DateOpened
,t1.DateClosed
,t1.CaseParticipantId
,t1.CaseParticipantKey
,t1.ParticipantAliasId
,t1.RoleInCase
,t1.RelToOrganization
,t1.Practice
,t1.City
,t1.Issue
,t1.IssueSubCategory
,t1.CaseIssueId
,t1.Outcome
,t1.ParticipantName
,t2.ActionTaken
FROM (
SELECT
Cases.CaseNumber
,Cases.DateOpened
,Cases.DateClosed
,Participant.CaseParticipantId
,Participant.CaseParticipantKey
,Participant.ParticipantAliasId
,Participant.RoleInCase
,Participant.RelToOrganization
,Participant.Practice
,Participant.City
,Issues.Issue
,Issues.IssueSubCategory
,Issues.CaseIssueId
,Issues.Outcome
,ParticipantAlias.ParticipantName
,CaseAction.ActionTaken
FROM [Warehouse].[Table].[Case] as Cases
LEFT JOIN
[Warehouse].[Table].[CaseParticipant] as Participant
ON Cases.CaseNumber = Participant.CaseNumber
LEFT JOIN
[Warehouse].[Table].[CaseIssue] as Issues
ON Participant.CaseParticipantId = Issues.CaseParticipantId
LEFT JOIN
[Warehouse].[Table].[CaseAction] as CaseAction
ON Issues.CaseIssueId = CaseAction.CaseIssueId
LEFT JOIN
[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
WHERE [Cases].[CaseNumber] = @caseNumber --123
) t1
FULL OUTER JOIN (
SELECT
Cases.CaseNumber
,Cases.DateOpened
,Cases.DateClosed
,Participant.CaseParticipantId
,Participant.CaseParticipantKey
,Participant.ParticipantAliasId
,Participant.RoleInCase
,Participant.RelToOrganization
,Participant.Practice
,Participant.City
,Issues.Issue
,Issues.IssueSubCategory
,Issues.CaseIssueId
,Issues.Outcome
,ParticipantAlias.ParticipantName
,CaseAction.ActionTaken
FROM [Warehouse].[Table].[Case] as Cases
LEFT JOIN
[Warehouse].[Table].[CaseParticipant] as Participant
ON Cases.CaseNumber = Participant.CaseNumber
LEFT JOIN
[Warehouse].[Table].[CaseIssue] as Issues
ON Participant.CaseParticipantId = Issues.CaseParticipantId
LEFT JOIN
[Warehouse].[Table].[CaseAction] as CaseAction
ON Issues.CaseIssueId = CaseAction.CaseIssueId
LEFT JOIN
[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
WHERE [Cases].[CaseNumber] = @caseNumber --123 
) t2 
ON t2.CaseNumber = t1.CaseNumber AND t2.CaseParticipantId = t1.CaseParticipantId
WHERE t2.ActionTaken &lt;&gt; &#39;See Previous Actions&#39;

答案2

得分: 0

以下是 UNION 示例的翻译部分：

然而，从性能考虑，如果您的表包含数十万条记录，那么存储过程是“最快”的，因为它只查询主要数据一次！（之后临时表正在使用）。

在另外两个示例中，您查询表的次数越多（其中包含大量数据），性能就会越差。如果表中没有太多数据，性能差异不大。
所以您可以选择使用哪种方法来获取所期望的输出。

UNION 示例结果

DECLARE @caseNumber int = '123'
SELECT
	Cases.CaseNumber
	,Cases.DateOpened
	,Cases.DateClosed
	,Participant.CaseParticipantId
	,Participant.CaseParticipantKey
	,Participant.ParticipantAliasId
	,Participant.RoleInCase
	,Participant.RelToOrganization
	,Participant.Practice
	,Participant.City
	,Issues.Issue
	,Issues.IssueSubCategory
	,Issues.CaseIssueId
	,Issues.Outcome
	,ParticipantAlias.ParticipantName
	,CaseAction.ActionTaken
FROM [Warehouse].[Table].[Case] as Cases
LEFT JOIN
	[Warehouse].[Table].[CaseParticipant] as Participant
	ON Cases.CaseNumber = Participant.CaseNumber
LEFT JOIN
	[Warehouse].[Table].[CaseIssue] as Issues
	ON Participant.CaseParticipantId = Issues.CaseParticipantId
LEFT JOIN
	[Warehouse].[Table].[CaseAction] as CaseAction
	ON Issues.CaseIssueId = CaseAction.CaseIssueId
LEFT JOIN
	[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
	ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
WHERE [Cases].[CaseNumber] = @caseNumber AND [CaseAction].ActionTaken &lt;&gt; 'See Previous Actions';

UNION ALL

SELECT DISTINCT 
	 t1.CaseNumber
	,t1.DateOpened
	,t1.DateClosed
	,t1.CaseParticipantId
	,t1.CaseParticipantKey
	,t1.ParticipantAliasId
	,t1.RoleInCase
	,t1.RelToOrganization
	,t1.Practice
	,t1.City
	,t1.Issue
	,t1.IssueSubCategory
	,t1.CaseIssueId
	,t1.Outcome
	,t1.ParticipantName
	,t2.ActionTaken
FROM (
	SELECT
	Cases.CaseNumber
	,Cases.DateOpened
	,Cases.DateClosed
	,Participant.CaseParticipantId
	,Participant.CaseParticipantKey
	,Participant.ParticipantAliasId
	,Participant.RoleInCase
	,Participant.RelToOrganization
	,Participant.Practice
	,Participant.City
	,Issues.Issue
	,Issues.IssueSubCategory
	,Issues.CaseIssueId
	,Issues.Outcome
	,ParticipantAlias.ParticipantName
	,CaseAction.ActionTaken
	FROM [Warehouse].[Table].[Case] as Cases
	LEFT JOIN
	[Warehouse].[Table].[CaseParticipant] as Participant
	ON Cases.CaseNumber = Participant.CaseNumber
	LEFT JOIN
	[Warehouse].[Table].[CaseIssue] as Issues
	ON Participant.CaseParticipantId = Issues.CaseParticipantId
	LEFT JOIN
	[Warehouse].[Table].[CaseAction] as CaseAction
	ON Issues.CaseIssueId = CaseAction.CaseIssueId
	LEFT JOIN
	[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
	ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
	WHERE [Cases].[CaseNumber] = @caseNumber AND [CaseAction].ActionTaken = 'See Previous Actions'
	) t1
	INNER JOIN (
		SELECT
			Cases.CaseNumber
			,Cases.DateOpened
			,Cases.DateClosed
			,Participant.CaseParticipantId
			,Participant.CaseParticipantKey
			,Participant.ParticipantAliasId
			,Participant.RoleInCase
			,Participant.RelToOrganization
			,Participant.Practice
			,Participant.City
			,Issues.Issue
			,Issues.IssueSubCategory
			,Issues.CaseIssueId
			,Issues.Outcome
			,ParticipantAlias.ParticipantName
			,CaseAction.ActionTaken
		FROM [Warehouse].[Table].[Case] as Cases
		LEFT JOIN
			[Warehouse].[Table].[CaseParticipant] as Participant
			ON Cases.CaseNumber = Participant.CaseNumber
		LEFT JOIN
			[Warehouse].[Table].[CaseIssue] as Issues
			ON Participant.CaseParticipantId = Issues.CaseParticipantId
		LEFT JOIN
			[Warehouse].[Table].[CaseAction] as CaseAction
			ON Issues.CaseIssueId = CaseAction.CaseIssueId
		LEFT JOIN
			[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
			ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
		WHERE [Cases].[CaseNumber] = @caseNumber AND [CaseAction].ActionTaken &lt;&gt; 'See Previous Actions'
	) t2 
	ON t2.CaseNumber = t1.CaseNumber AND t2.CaseParticipantId = t1.CaseParticipantId
英文:

Here is the UNION sample also:

However considering performance, if your tables contain hundreds of thousands of records, then the stored procedure is the "fastest" as it only queries the main data once ! (afterwards the temporary tables are in use).

In the other 2 examples, the more you query the tables (which contains many data) the less the performance will be. There won't be much difference if the tables don't contain many data inside.
So it's up to you what way method you'd choose getting the expected output.

UNION example result

DECLARE @caseNumber int = &#39;123&#39;
SELECT
Cases.CaseNumber
,Cases.DateOpened
,Cases.DateClosed
,Participant.CaseParticipantId
,Participant.CaseParticipantKey
,Participant.ParticipantAliasId
,Participant.RoleInCase
,Participant.RelToOrganization
,Participant.Practice
,Participant.City
,Issues.Issue
,Issues.IssueSubCategory
,Issues.CaseIssueId
,Issues.Outcome
,ParticipantAlias.ParticipantName
,CaseAction.ActionTaken
FROM [Warehouse].[Table].[Case] as Cases
LEFT JOIN
[Warehouse].[Table].[CaseParticipant] as Participant
ON Cases.CaseNumber = Participant.CaseNumber
LEFT JOIN
[Warehouse].[Table].[CaseIssue] as Issues
ON Participant.CaseParticipantId = Issues.CaseParticipantId
LEFT JOIN
[Warehouse].[Table].[CaseAction] as CaseAction
ON Issues.CaseIssueId = CaseAction.CaseIssueId
LEFT JOIN
[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
WHERE [Cases].[CaseNumber] = @caseNumber AND [CaseAction].ActionTaken &lt;&gt; &#39;See Previous Actions&#39;
UNION ALL
SELECT DISTINCT 
t1.CaseNumber
,t1.DateOpened
,t1.DateClosed
,t1.CaseParticipantId
,t1.CaseParticipantKey
,t1.ParticipantAliasId
,t1.RoleInCase
,t1.RelToOrganization
,t1.Practice
,t1.City
,t1.Issue
,t1.IssueSubCategory
,t1.CaseIssueId
,t1.Outcome
,t1.ParticipantName
,t2.ActionTaken
FROM (
SELECT
Cases.CaseNumber
,Cases.DateOpened
,Cases.DateClosed
,Participant.CaseParticipantId
,Participant.CaseParticipantKey
,Participant.ParticipantAliasId
,Participant.RoleInCase
,Participant.RelToOrganization
,Participant.Practice
,Participant.City
,Issues.Issue
,Issues.IssueSubCategory
,Issues.CaseIssueId
,Issues.Outcome
,ParticipantAlias.ParticipantName
,CaseAction.ActionTaken
FROM [Warehouse].[Table].[Case] as Cases
LEFT JOIN
[Warehouse].[Table].[CaseParticipant] as Participant
ON Cases.CaseNumber = Participant.CaseNumber
LEFT JOIN
[Warehouse].[Table].[CaseIssue] as Issues
ON Participant.CaseParticipantId = Issues.CaseParticipantId
LEFT JOIN
[Warehouse].[Table].[CaseAction] as CaseAction
ON Issues.CaseIssueId = CaseAction.CaseIssueId
LEFT JOIN
[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
WHERE [Cases].[CaseNumber] = @caseNumber AND [CaseAction].ActionTaken = &#39;See Previous Actions&#39;
) t1
INNER JOIN (
SELECT
Cases.CaseNumber
,Cases.DateOpened
,Cases.DateClosed
,Participant.CaseParticipantId
,Participant.CaseParticipantKey
,Participant.ParticipantAliasId
,Participant.RoleInCase
,Participant.RelToOrganization
,Participant.Practice
,Participant.City
,Issues.Issue
,Issues.IssueSubCategory
,Issues.CaseIssueId
,Issues.Outcome
,ParticipantAlias.ParticipantName
,CaseAction.ActionTaken
FROM [Warehouse].[Table].[Case] as Cases
LEFT JOIN
[Warehouse].[Table].[CaseParticipant] as Participant
ON Cases.CaseNumber = Participant.CaseNumber
LEFT JOIN
[Warehouse].[Table].[CaseIssue] as Issues
ON Participant.CaseParticipantId = Issues.CaseParticipantId
LEFT JOIN
[Warehouse].[Table].[CaseAction] as CaseAction
ON Issues.CaseIssueId = CaseAction.CaseIssueId
LEFT JOIN
[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
WHERE [Cases].[CaseNumber] = @caseNumber AND [CaseAction].ActionTaken &lt;&gt; &#39;See Previous Actions&#39;
) t2 
ON t2.CaseNumber = t1.CaseNumber AND t2.CaseParticipantId = t1.CaseParticipantId

huangapple
  • 本文由 发表于 2023年2月19日 03:57:33
  • 转载请务必保留本文链接：https://go.coder-hub.com/75496033.html
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定