Join of 2 subqueries

Question

Join of 2 subqueries

daniness

SSCrazy

Points: 2890
More actions
July 17, 2013 at 7:05 am

#395154

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

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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 Shaw}

For 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

HanShi SSC-Dedicated Points: 33508 More actions · Answer 2

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’! **

daniness SSCrazy Points: 2890 More actions · Answer 3

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.

daniness SSCrazy Points: 2890 More actions · Answer 4

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... 🙁

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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 Shaw}

For 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

daniness SSCrazy Points: 2890 More actions · Answer 6

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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 Shaw}

For 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

daniness SSCrazy Points: 2890 More actions · Answer 8

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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 Shaw}

For 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

daniness SSCrazy Points: 2890 More actions · Answer 10

I believe the output would give me the dupe records that I'm wondering about. Am I mistaken to think so?

daniness SSCrazy Points: 2890 More actions · Answer 11

Just edited the query at the beginning of the thread to include the On criteria, but still no progress...any thoughts, anyone?

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

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/

daniness SSCrazy Points: 2890 More actions · Answer 13

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

If all you are looking for is matching keys between the two queries, then this should work:

SELECT

ClaimId,

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 Shaw}

For 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