| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
07-28-10, 15:42
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 39
|
|
|
Help with SQL complex join Query
|
|
I am trying to pull data from same table using joins. The query is running fine, but there is some anomaly in data. CPT Code field is not entered for some rows and I need to pull those rows corresponding to join.
Can anyone help me, in what should I add to get those rows.
Code:
SELECT
SUM(BD.Extended), SUM(ABS( i.extended)) as Insurance, DateDiff(qq, '01/01/2007' ,i.[Service Date 1]) AS Quarter,
SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 0 AND 3 THEN ABS(i.extended) END) AS b0_3,
SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 4 AND 6 THEN ABS(i.extended) END) AS b4_6,
SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 7 AND 9 THEN ABS(i.extended) END) AS b7_9,
SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 10 AND 12 THEN ABS(i.extended) END) AS b10_12,
SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 13 AND 15 THEN ABS(i.extended) END) AS b13_15,
SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 16 AND 18 THEN ABS(i.extended) END) AS b16_18,
SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 19 AND 21 THEN ABS(i.extended) END) AS b19_21,
SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 22 AND 24 THEN ABS(i.extended) END) AS b22_24,
SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) >24 THEN ABS(i.extended) ELSE 0 END) AS b25_Above
FROM
[Billing Detail] as BD
FULL OUTER JOIN
[Billing Detail] AS O ON O.Billing=BD.Billing
FULL OUTER JOIN [Billing Detail] AS I ON (I.billing = O.billing and O.[transaction code] = I.[cpt code] )
WHERE
O.item=I.item
AND (i.[transaction Code] = 'PIP' or
i.[transaction Code] = 'APPEA0001' OR
i.[transaction Code] = 'ATTYPYMT' OR
i.[transaction Code] = 'INPRO0000' OR
i.[transaction Code] = 'INTPMTS' OR
i.[transaction Code] = 'LPMT0000' OR
i.[transaction Code] = 'MRPYMT' OR
i.[transaction Code] = 'NONRESP' OR
i.[transaction Code] = 'PARTI0000' OR
i.[transaction Code] = 'PARTI0001' OR
i.[transaction Code] = 'PPCA' OR
i.[transaction Code] = 'PPCC' OR
i.[transaction Code] = 'PPCH' OR
i.[transaction Code] = 'PPIP' OR
i.[transaction Code] = 'PSIP' OR
i.[transaction Code] = 'PTIP' OR
i.[transaction Code] = 'SETTL0000' OR
i.[transaction Code] = 'SETTOPAT' OR
i.[transaction Code] = 'SIP ' OR
i.[transaction Code] = 'STMT' OR
i.[transaction Code] = 'SUPCA' OR
i.[transaction Code] = 'SUPCC' OR
i.[transaction Code] = 'SUPCH' OR
i.[transaction Code] = 'IP' OR
i.[transaction Code] = 'MP' OR
i.[transaction Code] = 'PIP' OR
i.[transaction Code] = 'TIP' OR
i.[transaction Code] = 'VOUCHER' OR
i.[transaction Code] = 'VOUCHPHON' ) and BD.[unique id] = O.[unique id]
AND BD.extended > 0
AND I.[Service Date 1] BETWEEN @StartDate AND @EndDate
GROUP BY DateDiff(qq, '01/01/2007' ,i.[Service Date 1])
|
|
07-28-10, 16:18
|
|
Registered User
|
|
Join Date: Jul 2003
Location: San Antonio, TX
Posts: 3,611
|
|
Have you tried to just remove your "AND BD.extended > 0" filter and see if you're getting your CPT's?
__________________
"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."
|
|
07-28-10, 16:25
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,609
|
|
|
|
FULL OUTER joins are fun! I'd try:
Code:
SELECT
SUM(BD.Extended), SUM(ABS( i.extended)) as Insurance
, DateDiff(qq, '01/01/2007' ,i.[Service Date 1]) AS Quarter
, SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 0 AND 3 THEN ABS(i.extended) END) AS b0_3
, SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 4 AND 6 THEN ABS(i.extended) END) AS b4_6
, SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 7 AND 9 THEN ABS(i.extended) END) AS b7_9
, SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 10 AND 12 THEN ABS(i.extended) END) AS b10_12
, SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 13 AND 15 THEN ABS(i.extended) END) AS b13_15
, SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 16 AND 18 THEN ABS(i.extended) END) AS b16_18
, SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 19 AND 21 THEN ABS(i.extended) END) AS b19_21
, SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) BETWEEN 22 AND 24 THEN ABS(i.extended) END) AS b22_24
, SUM (CASE WHEN DateDiff(mm, BD.[Service Date 1], i.[Service Date 1]) >24 THEN ABS(i.extended) ELSE 0 END) AS b25_Above
FROM [Billing Detail] as BD
FULL OUTER JOIN [Billing Detail] AS O
ON (O.[Billing] = BD.[Billing]
AND O.[unique id] = BD.[unique id])
FULL OUTER JOIN [Billing Detail] AS I
ON (I.[billing] = O.[billing]
AND I.[item] = O.[item]
AND I.[transaction code] = O.[cpt code]
AND I.[Service Date 1] BETWEEN @StartDate AND @EndDate
AND I.[transaction Code] IN ('PIP', 'APPEA0001', 'ATTYPYMT'
, 'INPRO0000', 'INTPMTS', 'LPMT0000'
, 'MRPYMT', 'NONRESP', 'PARTI0000'
, 'PARTI0001', 'PPCA', 'PPCC'
, 'PPCH', 'PPIP', 'PSIP'
, 'PTIP', 'SETTL0000', 'SETTOPAT'
, 'SIP ', 'STMT', 'SUPCA'
, 'SUPCC', 'SUPCH', 'IP'
, 'MP', 'PIP', 'TIP'
, 'VOUCHER', 'VOUCHPHON'))
WHERE BD.extended > 0
GROUP BY DateDiff(qq, '01/01/2007' , i.[Service Date 1])
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
|
|
07-28-10, 16:54
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 39
|
|
well thanks for your replies but I have tried full outer joins as well as removing extended>0 but it's not helping.
Now we have to manually include those data for null cpt codes
is there any other solution
|
|
07-28-10, 18:07
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,609
|
|
So when you run the code that I posted, you don't get as much data as you'd envisioned ??? I can see getting more than you bargained for, or possibly getting more rows summarized than you'd anticipated, but not getting enough rows had never crossed my mind!
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
|
|
07-29-10, 12:00
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 39
|
|
FULL OUTER JOIN IS NOT HELLPING EITHER, IT IS GIVING THE SAME DATA AS WITH LEFTOUTER JOIN
Do you have any other suggestion?
|
|
07-29-10, 12:51
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
how about giving us tablee ddl and some sample data and show what you expect to see?
Dave
|
|
07-29-10, 14:16
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 39
|
|
|
Ddl
CREATE TABLE [dbo].[Billing Detail](
[Unique ID] [int] NOT NULL,
[Chart] [varchar](15) NOT NULL,
[Billing] [int] NOT NULL,
[Created Date] [datetime] NULL,
[Service Date 1] [datetime] NULL,
[Service Date 2] [datetime] NULL,
[Provider Code] [varchar](9) NOT NULL,
[Location Code] [varchar](9) NOT NULL,
[Transaction Code] [varchar](9) NOT NULL,
[Charge] [money] NOT NULL,
[Units] [real] NOT NULL,
[Extended] [money] NOT NULL,
[Detail Note] [varchar](40) NOT NULL,
[Item] [int] NOT NULL,
[Sub Item] [int] NOT NULL,
[CPT Code] [varchar](7) NOT NULL,
[Created Date - Auto] [datetime] NOT NULL CONSTRAINT [Default Billing Detail Created Date - Auto] DEFAULT (getdate()),
[Diagnosis 6] [varchar](9) NOT NULL,
[Diagnosis 7] [varchar](9) NOT NULL,
[Diagnosis 8] [varchar](9) NOT NULL,
[NDCCode] [varchar](48) NOT NULL,
[NDCUnitPrice] [money] NOT NULL,
[NDCUnitCount] [real] NOT NULL,
[NDCUnitMeasurement] [varchar](2) NOT NULL,
[NDCPrescriptionNumber] [varchar](30) NOT NULL,
CONSTRAINT [Billing Detail.Primary Key - Dtl Index Unique ID] PRIMARY KEY CLUSTERED
|
|
07-29-10, 14:23
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 39
|
|
select [chart],[billing],[service date 1], [provider code],[transaction code], [cpt code],[item],[charge],extended,units from [billing detail]as i where
(i.[transaction Code] = 'PIP' or
i.[transaction Code] = 'APPEA0001' OR
i.[transaction Code] = 'ATTYPYMT' OR
i.[transaction Code] = 'INPRO0000' OR
i.[transaction Code] = 'INTPMTS' OR
i.[transaction Code] = 'LPMT0000' OR
i.[transaction Code] = 'MRPYMT' OR
i.[transaction Code] = 'NONRESP' OR
i.[transaction Code] = 'PARTI0000' OR
i.[transaction Code] = 'PARTI0001' OR
i.[transaction Code] = 'PPCA' OR
i.[transaction Code] = 'PPCC' OR
i.[transaction Code] = 'PPCH' OR
i.[transaction Code] = 'PPIP' OR
i.[transaction Code] = 'PSIO' OR
i.[transaction Code] = 'PTIP' OR
i.[transaction Code] = 'SETTL0000' OR
i.[transaction Code] = 'SETTOPAT' OR
i.[transaction Code] = 'SIP ' OR
i.[transaction Code] = 'STMT' OR
i.[transaction Code] = 'SUPCA' OR
i.[transaction Code] = 'SUPCC' OR
i.[transaction Code] = 'SUPCH' OR
i.[transaction Code] = 'IP' OR
i.[transaction Code] = 'MP' OR
i.[transaction Code] = 'PIP' OR
i.[transaction Code] = 'TIP' OR
i.[transaction Code] = 'VOUCHER' OR
i.[transaction Code] = 'VOUCHPHON' )
and
[service date 1] between '1/1/2009' and '7/31/2010'
and [cpt code]=''
and extended<0
and chart!='overpay'
00001015 16062 2009-05-26 00:00:00.000 AH PIP 5 52.67 -52.67 1
00000296 32952 2010-04-19 00:00:00.000 IPN00 PIP 2 3.43 -3.43 1
There are several rows where [Cpt code] is empty and I want to include those fields also with the join query but these rows are not being displayed coz of join conditions. What should I do?
|
|
07-29-10, 14:39
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
Could it be due to the full outer join where you join on the CPT_CODE?
Quote:
|
and O.[transaction code] = I.[cpt code] )
|
You may want to make that a conditional join, which could lead to a partial cartesian product. You could correct it with a distinct or maybe joining to the table again on that condition?
Code:
and( O.[transaction code] = I.[cpt code]
or i.cpt code = '') )
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Help with SQL complex join Query
