Join of 2 subqueries
-
Hi All,
I've been trying to do a join of 2 subqueries, but haven't been having much luck and keep getting errors. Can anyone please advise on what I'm doing wrong here? Thanks!
Declare @SweepId as int = 10160, @RunType as varchar = 'Initial'
Select *
from
(Select distinct ClaimId
, LineNum
, 0 as EncounterLineNum
, EncounterType
, InternalICN
, PreviousDpwICN
, 0 as ClaimFrequencyCd
, EncounterPeriod
, 2 as StatusCd
, BypassReason
, null as EncounterFileNm
,@SweepId as SweepId
from IntEncTracking.EncounterList
where bypassflag = 1) as q1
join
( Select ClaimId
, 0 as LineNum
, 0 as EncounterLineNum
, EncounterType
, InternalICN
, PreviousDpwICN
, max(ClaimFreqCd) as ClaimFreqCd
, max(EncounterPeriod) as EncounterPeriod
, case when exists (select 'x'
from IntEncTracking.EncounterList el1
where el1.claimid = claimid
and BypassFlag = 0) then 1
else 2
end stscd
, case when @RunType = 'Initial' then 100 else 300 end as [StatusReasonCd]
, null as EncounterFileNm
, @SweepId as SweepId
from IntEncTracking.EncounterList el
where BypassFlag = 0) as q2
on q1.ClaimId = q2.ClaimId and
q1.LineNum = q2.LineNum and
q1.EncounterLineNum = q2.EncounterLineNum and
q1.EncounterType = q2.EncounterType and
q1.InternalICN = q2.InternalICN
group by q1.ClaimId, q1.EncounterType, q1.InternalICN, q1.PreviousDpwICN
order by q2.ClaimId, q2.LineNum, q2.EncounterLineNum, q2.EncounterType, q2.InternalICN
-
You won't get the two queries to JOIN in the way that you are attempting to, unless each query works in isolation. The second query has aggregate functions but there's no GROUP BY, it's associated with the outer query. If all you want from the second query is those aggregate columns then there's almost certainly a better way of doing this. Can you give us some more detail?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
It's not really clear what you want, but I think you can put the GROUP BY statement inside the second sub-select (and remove the "q1" alias within this GROUP BY)
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
Hi Chris,
Sure, I'd be happy to provide more detail. So the results of both queries are being inserted into the same table as part of a longer stored procedure, which errored out, saying that there cannot be a duplicate key inserted into it. So, this is why I'm trying to find out what the duplicate records are. Any suggestions would be appreciated. Thanks.
-
Hi Hanshi,
Actually, originally the group by was part of the 1st subquery, but it wasn't working, so this is why I tried placing it at the end, but obviously it's still not working... 🙁
-
daniness (7/17/2013)
Hi Hanshi,Actually, originally the group by was part of the 1st subquery, but it wasn't working, so this is why I tried placing it at the end, but obviously it's still not working... 🙁
Don't you know what results you want from this query? Making a query work is trivial, but it's pointless if you can't tell if the result set is correct or not.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Chris,
What I'm trying to get from this query are records that are in common to both subqueries. This would tell me which records are responsible for the duplicate error I mentioned earlier.
-
daniness (7/17/2013)
Chris,What I'm trying to get from this query are records that are in common to both subqueries. This would tell me which records are responsible for the duplicate error I mentioned earlier.
Which columns are included in the key which is responsible for generating the dupes error?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
The columns that are in the order by: ClaimId, LineNum, EncounterLineNum, EncounterType, and InternalICN. These make up the primary key that results of both queries are being inserted into in the stored procedure. Please let me know if I can further clarify. Thanks.
-
daniness (7/17/2013)
The columns that are in the order by: ClaimId, LineNum, EncounterLineNum, EncounterType, and InternalICN. These make up the primary key that results of both queries are being inserted into in the stored procedure. Please let me know if I can further clarify. Thanks.Like I said earlier, tweaking this query to get it through the PK error is a trivial matter - but isn't it a bit pointless if you don't know whether or not the output is actually correct?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
I believe the output would give me the dupe records that I'm wondering about. Am I mistaken to think so?
-
Just edited the query at the beginning of the thread to include the On criteria, but still no progress...any thoughts, anyone?
-
daniness (7/17/2013)
Just edited the query at the beginning of the thread to include the On criteria, but still no progress...any thoughts, anyone?The problem here is that we can't see your screen, we don't know your project and we are not familiar with what you are trying to do. In short, you haven't really provided any level of detail for us to help.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (7/17/2013)
daniness (7/17/2013)
Just edited the query at the beginning of the thread to include the On criteria, but still no progress...any thoughts, anyone?The problem here is that we can't see your screen, we don't know your project and we are not familiar with what you are trying to do. In short, you haven't really provided any level of detail for us to help.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
Okay SSCrazyEights et al,
Let's see if I can provide the info you're asking for:
1. The EncounterList table is structured as follows:
...CREATE TABLE [IntEncTracking].[EncounterList](
[ClaimId] [varchar](31) NOT NULL,
[LineNum] [int] NOT NULL,
[ProviderId] [varchar](20) NOT NULL,
[memberid] [varchar](31) NOT NULL,
[FormType] [varchar](7) NULL,
[EncounterType] [varchar](5) NULL,
[EncounterPeriod] [varchar](6) NULL,
[FirstDos] [date] NULL,
[LastDos] [date] NULL,
[BilledAmt] [money] NULL,
[PayAmt] [money] NULL,
[PrePayAmt] [money] NULL,
[COBAmt] [money] NULL,
[CoPayAmt] [money] NULL,
[DeductibleAmt] [money] NULL,
[PhysSignatureInd] [char](1) NULL,
[MedicareAssignmentCd] [varchar](7) NULL,
[AssignmentOfBenefitsInd] [char](1) NULL,
[ReleaseOfInfoInd] [char](1) NULL,
[IpOpInd] [char](1) NULL,
[BypassFlag] [int] NOT NULL,
[BypassReason] [int] NOT NULL,
[ClaimFreqCd] [varchar](1) NULL,
[ContractTypeCode] [varchar](2) NULL,
[CobFlag] [bit] NULL,
[ReversedFlag] [bit] NOT NULL,
[ClaimBatchType] [char](1) NOT NULL,
[EncStatus] [int] NULL,
[PreviousInternalICN] [varchar](50) NULL,
[PreviousDpwICN] [varchar](50) NULL,
[InternalICN] [varchar](50) NULL,
[EncounterLineNum] [int] NULL,
[ExternalClaimNum] [varchar](31) NULL,
[PatientSignatureSourceCd] [varchar](7) NULL,
[EstDueAmt] [money] NULL,
[BillType] [varchar](7) NULL
) ON [PRIMARY]
...
and the table that the records are being inserted into, EncounterTrackingTemp:
CREATE TABLE [IntEncTracking].[EncounterTrackingTemp]([ClaimId] [varchar](31) NOT NULL,
[LineNum] [int] NOT NULL,
[EncounterLineNum] [int] NOT NULL,
[EncounterType] [varchar](5) NOT NULL,
[InternalICN] [varchar](50) NOT NULL,
[DpwICN] [varchar](50) NULL,
[ClaimFrequencyCd] [int] NOT NULL,
[EncounterPeriod] [varchar](6) NOT NULL,
[StatusDt] [date] NOT NULL,
[StatusCd] [int] NOT NULL,
[StatusReasonCd] [int] NULL,
[EncounterFileNm] [varchar](60) NULL,
[SweepId] [int] NULL,
[DtCreated] [datetime] NOT NULL,
CONSTRAINT [PK_EncounterTrackingTemp] PRIMARY KEY CLUSTERED
(
...
[ClaimId] ASC,
[LineNum] ASC,
[EncounterLineNum] ASC,
[EncounterType] ASC,
[InternalICN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] ...
2. Here is the part of the stored procedure where the records are being inserted into the EncounterTrackingTemp table:
INSERT INTO [IntEncTracking].[EncounterTrackingTemp]( [ClaimId]
, [LineNum]
, EncounterLineNum
, EncounterType
, InternalICN
, DpwICN
, [ClaimFrequencyCd]
, [EncounterPeriod]
, [StatusCd]
, [StatusReasonCd]
, [EncounterFileNm]
, [SweepId])
Select distinct ClaimId
, LineNum
, 0
, EncounterType
, InternalICN
, PreviousDpwICN
, 0
, EncounterPeriod
, 2
, BypassReason
, null
,@SweepId
from IntEncTracking.EncounterList
where bypassflag = 1
-- Insert Line 0 record into the Tracking Table
INSERT INTO [IntEncTracking].[EncounterTrackingTemp]
( [ClaimId]
, [LineNum]
, EncounterLineNum
, EncounterType
, InternalICN
, DpwICN
, [ClaimFrequencyCd]
, [EncounterPeriod]
, [StatusCd]
, [StatusReasonCd]
, [EncounterFileNm]
, [SweepId])
Select ClaimId
, 0 as LineNum
, 0 as EncounterLineNum
, EncounterType
, InternalICN
, PreviousDpwICN
, max(ClaimFreqCd) as ClaimFreqCd
, max(EncounterPeriod) as EncounterPeriod
, case when exists (select 'x'
from IntEncTracking.EncounterList el1
where el1.claimid = claimid
and BypassFlag = 0) then 1
else 2
end stscd
, case when @RunType = 'Initial' then 100 else 300 end as [StatusReasonCd]
, null as EncounterFileNm
, @SweepId
from IntEncTracking.EncounterList el
where BypassFlag = 0
group by ClaimId, encountertype, internalicn, PreviousDpwICN
3. Please see the following sample data
In case it doesn't show up here, I've also attached it. Please let me know if I can provide anything else. Thanks for your help. -
If all you are looking for is matching keys between the two queries, then this should work:
SELECTClaimId,
LineNum,
EncounterLineNum,
EncounterType,
InternalICN
FROM IntEncTracking.EncounterList el
WHERE e1.bypassflag = 1
INTERSECT
SELECT
ClaimId,
LineNum,
EncounterLineNum,
EncounterType,
InternalICN
FROM IntEncTracking.EncounterList el
WHERE e1.bypassflag = 0
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply