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'
2条答案
按热度按时间dxpyg8gm1#
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
OR If you really want it without stored-procedure: Here is an example for it:
Example of simple query
8ehkhllq2#
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