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
droptable #PrimaryCLaims
droptable #SecondaryClaims
createtable #PrimaryClaims ( Facility int, ClaimNumber int, opsd datetime)
insertinto #PrimaryClaims values(8, 088270301,'06/26/2007')
insertinto #PrimaryClaims values(8, 088270701,'06/26/2007')
insertinto #PrimaryClaims values(8, 088270801,'06/26/2007')
insertinto #PrimaryClaims values(8, 088477201,'06/26/2007')
insertinto #PrimaryClaims values(8, 088477701,'06/26/2007')
createtable #SecondaryClaims( Facility int, ClaimNumber int, ossd datetime)
insertinto #SecondaryClaims values(8, 069294101,'06/26/2007')
insertinto #SecondaryClaims values(8, 069633701,'06/26/2007')
insertinto #SecondaryClaims values(8, 073847301,'06/26/2007')
insertinto #SecondaryClaims values(8, 075659901,'06/26/2007')
insertinto #SecondaryClaims values(8, 077253001,'06/26/2007')
insertinto #SecondaryClaims values(8, 081933301,'06/26/2007')
insertinto #SecondaryClaims values(8, 082344301,'06/26/2007')
select p.Facility, p.pcount, s.scount
from
(select Facility,count(*) pcount from #PrimaryClaims groupby Facility )as p
innerjoin
(select Facility,count(*) scount from #SecondaryClaims groupby Facility)as s
on p.Facility = s.Facility
WITH PrimaryClaims AS
(
Select Facility,count(*)as pcount from
(
SELECTDISTINCT Facility, ClaimNumber, OriginalPrimarySubmissionDate AS opsd
FROM Claims
WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE'%01'AND Facility = 8 AND OriginalPrimarySubmissionDate ='6/26/07'
)as p1
Groupby Facility
),
SecondaryClaims AS
(
Select Facility,count(*)as scount from
(
SELECTDISTINCT Facility, ClaimNumber, OriginalSecondarySubmissionDate AS ossd
FROM Claims
WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE'%01'AND Facility = 8 AND OriginalSecondarySubmissionDate ='6/26/07'
)as s1
Groupby Facility
)
SELECT pc.Facility, pc.pcount as PCount, sc.scount AS SCount
FROM PrimaryClaims pc
INNERJOIN 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
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'
GROUPBY 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'
GROUPBY FacilityName
)
SELECT pc.FacilityName, pc.PCount, sc.SCount
FROM PrimaryClaims pc
LEFTOUTERJOIN SecondaryClaims sc ON pc.FacilityName = sc.FacilityName
This gives me what i wanted in one row. Thanks again for all the help and inspiration!
No comments:
Post a Comment