Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Join of 2 subqueries Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 7:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:36 AM
Points: 37, Visits: 166
I believe the output would give me the dupe records that I'm wondering about. Am I mistaken to think so?
Post #1474629
Posted Wednesday, July 17, 2013 8:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:36 AM
Points: 37, Visits: 166
Just edited the query at the beginning of the thread to include the On criteria, but still no progress...any thoughts, anyone?
Post #1474650
Posted Wednesday, July 17, 2013 8:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:08 AM
Points: 13,062, Visits: 11,892
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1474658
Posted Wednesday, July 17, 2013 9:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:36 AM
Points: 37, Visits: 166
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.


  Post Attachments 
Screenshot.png (1 view, 55.56 KB)
Post #1474674
Posted Wednesday, July 17, 2013 9:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 7,114, Visits: 13,471
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1474678
Posted Thursday, July 18, 2013 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:36 AM
Points: 37, Visits: 166
Hi All,

Thanks for your assistance. It turns out I was taking the wrong approach with this query....so until next time... :)
Post #1475038
Posted Thursday, July 18, 2013 7:35 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
Intersect is your friend.


Best answer yet ChrisM, provided he is on a version that supports it.
Post #1475040
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse