Help me join data from 2 common tables into 1 row. I'm outta ideas!

Common table: PrimaryClaims

Facility

ClaimNumber

opsd

8

088270301

06/26/2007

8

088270701

06/26/2007

8

088270801

06/26/2007

8

088477201

06/26/2007

8

088477701

06/26/2007

Common table: SecondaryClaims

Facility

ClaimNumber

ossd

8

069294101

06/26/2007

8

069633701

06/26/2007

8

073847301

06/26/2007

8

075659901

06/26/2007

8

077253001

06/26/2007

8

081933301

06/26/2007

8

082344301

06/26/2007

To make it easier to visualize my example, the above are the data for the common tables. Here’s what I got so far:

Code Snippet

WITH PrimaryClaims AS

(

SELECT DISTINCT Facility, ClaimNumber, OriginalPrimarySubmissionDate AS opsd

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalPrimarySubmissionDate = '6/26/07'

),

SecondaryClaims AS

(

SELECT DISTINCT Facility, ClaimNumber, OriginalSecondarySubmissionDate AS ossd

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalSecondarySubmissionDate = '6/26/07'

)

SELECT pc.Facility, COUNT(pc.opsd) AS PCount, sc.Facility, COUNT(sc.ossd) AS SCount

FROM PrimaryClaims pc FULL OUTER JOIN SecondaryClaims sc ON pc.ClaimNumber = sc.ClaimNumber

GROUP BY pc.Facility, sc.Facility

My current results:

Facility

PCount

Facility

SCount

8

5

NULL

0

NULL

0

8

7

I’ve played with INNER JOIN, all OUTER JOINs, and CROSS JOIN to no avail. I’ve added different conditions into the joins and still won’t work. I’ve done many other attempts as well. Too many to list and still won’t work. I’m thinking I’m missing something so simple.

What I’m trying to do is to get all the data on one row. The query basically counts the number of primary and secondary claims. I’d like to get them in a row so I can make a report out of it. Any help is greatly appreciated!

Why do you need to JOIN the tables together? Can you just write a CTE or inline sub queries to get the count of each table?

Code Snippet

SELECT (SELECT COUNT(*) FROM PrimaryClains WHERE ....) AS PCount

,(SELECT COUNT(*) FROM SecondaryClaimms WHERE ...) AS SCount

|||

Using your cte values here some code, and then a modification to your code to achieve the same from the source table (which obviously I haven't tested)

Code Snippet

drop table #PrimaryCLaims

drop table #SecondaryClaims

create table #PrimaryClaims ( Facility int, ClaimNumber int, opsd datetime)

insert into #PrimaryClaims values (8, 088270301, '06/26/2007')

insert into #PrimaryClaims values (8, 088270701, '06/26/2007')

insert into #PrimaryClaims values (8, 088270801, '06/26/2007')

insert into #PrimaryClaims values (8, 088477201, '06/26/2007')

insert into #PrimaryClaims values (8, 088477701, '06/26/2007')

create table #SecondaryClaims( Facility int, ClaimNumber int, ossd datetime)

insert into #SecondaryClaims values (8, 069294101, '06/26/2007')

insert into #SecondaryClaims values (8, 069633701, '06/26/2007')

insert into #SecondaryClaims values (8, 073847301, '06/26/2007')

insert into #SecondaryClaims values (8, 075659901, '06/26/2007')

insert into #SecondaryClaims values (8, 077253001, '06/26/2007')

insert into #SecondaryClaims values (8, 081933301, '06/26/2007')

insert into #SecondaryClaims values (8, 082344301, '06/26/2007')

select p.Facility, p.pcount, s.scount

from

( select Facility, count(*) pcount from #PrimaryClaims group by Facility ) as p

inner join

( select Facility, count(*) scount from #SecondaryClaims group by Facility) as s

on p.Facility = s.Facility

WITH PrimaryClaims AS

(

Select Facility, count(*) as pcount from

(

SELECT DISTINCT Facility, ClaimNumber, OriginalPrimarySubmissionDate AS opsd

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalPrimarySubmissionDate = '6/26/07'

) as p1

Group by Facility

),

SecondaryClaims AS

(

Select Facility, count(*) as scount from

(

SELECT DISTINCT Facility, ClaimNumber, OriginalSecondarySubmissionDate AS ossd

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalSecondarySubmissionDate = '6/26/07'

) as s1

Group by Facility

)

SELECT pc.Facility, pc.pcount as PCount, sc.scount AS SCount

FROM PrimaryClaims pc

INNER JOIN SecondaryClaims sc

ON pc.ClaimNumber = sc.ClaimNumber

|||

Unless I'm totally mis-reading your question, it seems like both -it can be done without a JOIN and it can be done a lot simpler.

Does this come close?

Code Snippet

DECLARE @.Claims table
( Facility int,
ClaimNumber int,
OriginalPrimarySubmissionDate datetime,
OriginalSecondarySubmissionDate datetime,
ClaimSubmitted int
)

INSERT INTO @.Claims VALUES ( 8, 088270301, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088270701, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088270801, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088477201, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088477701, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 069294101, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 069633701, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 073847301, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 075659901, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 077253001, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 081933301, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 082344301, NULL, '06/26/2007', 1 )
-- Data to test criteria
INSERT INTO @.Claims VALUES ( 8, 082344301, NULL, '06/27/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 088477701, '06/27/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 082344302, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 088477703, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088477701, '06/26/2007', NULL, 0 )
INSERT INTO @.Claims VALUES ( 8, 082344301, NULL, '06/26/2007', 0 )
INSERT INTO @.Claims VALUES ( 7, 088477701, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 9, 082344301, NULL, '06/26/2007', 1 )

SELECT
Facility,
PCount = count( OriginalPrimarySubmissionDate ),
SCount = count( OriginalSecondarySubmissionDate )
FROM @.Claims
WHERE ( ClaimSubmitted = 1
AND ClaimNumber LIKE '%01'
AND Facility = 8
AND ( OriginalPrimarySubmissionDate = '2007/06/26'
OR OriginalSecondarySubmissionDate = '2007/06/26'
)
)
GROUP BY Facility

Facility PCount SCount
-- -- --
8 5 7

|||hi you can also try this one, but this is with the assumption that all primary claim may or may not have a secondary claim, that's why a left join was used

create table #primaryclaims ( Facility int, ClaimNumber int, opsd datetime)
insert into #primaryclaims values (8, 088270301, '06/26/2007')
insert into #primaryclaims values (8, 088270701, '06/26/2007')
insert into #primaryclaims values (8, 088270801, '06/26/2007')
insert into #primaryclaims values (8, 088477201, '06/26/2007')
insert into #primaryclaims values (8, 088477701, '06/26/2007')

create table #secondaryclaims( Facility int, ClaimNumber int, ossd datetime)
insert into #secondaryclaims values (8, 069294101, '06/26/2007')
insert into #secondaryclaims values (8, 069633701, '06/26/2007')
insert into #secondaryclaims values (8, 073847301, '06/26/2007')
insert into #secondaryclaims values (8, 075659901, '06/26/2007')
insert into #secondaryclaims values (8, 077253001, '06/26/2007')
insert into #secondaryclaims values (8, 081933301, '06/26/2007')
insert into #secondaryclaims values (8, 082344301, '06/26/2007')

select p.facility
, p.pcount
, s.facility
, s.scount
from ( select p.facility
, count(*) as pcount
from #primaryclaims p
group by
p.facility
) p left outer join
( select s.facility
, count(*) as scount
from #secondaryclaims s
group by
s.facility
) s on p.facility = s.facility

drop table #primaryclaims
drop table #secondaryclaims|||

THANKS FOR ALL YOUR HELP GUYS! Some famaliar faces in the replies! Thanks so much...

Although I didn't go with any of your possible solutions, your examples combined with further research got me what i was looking for. I understand it's very difficult to explain the whole picture when asking for help because the tables we're working with can span infinite space and time. I always try to give the most information with the least amount of data to get my question across so I don't waste anyone's time too much.

I ended up just using a simple COUNT(DISTINCT...) transact in the CTE. My count was based on claims with the primary and secondary submission dates, which were static amongst all the records in the result set. With that in mind, I just counted the claim numbers. For anyone who's reading this and wants to know how i did it, here's the new query:

Code Snippet

WITH PrimaryClaims AS

(

SELECT FacilityName, COUNT(DISTINCT ClaimNumber) AS PCount

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalPrimarySubmissionDate = '6/26/07'

GROUP BY FacilityName

),

SecondaryClaims AS

(

SELECT FacilityName, COUNT(DISTINCT ClaimNumber) AS SCount

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalSecondarySubmissionDate = '6/26/07'

GROUP BY FacilityName

)

SELECT pc.FacilityName, pc.PCount, sc.SCount

FROM PrimaryClaims pc

LEFT OUTER JOIN SecondaryClaims sc ON pc.FacilityName = sc.FacilityName

This gives me what i wanted in one row. Thanks again for all the help and inspiration!