Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2010
    Posts
    39

    Unanswered: 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])

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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."

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

  4. #4
    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

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

  6. #6
    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?

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how about giving us tablee ddl and some sample data and show what you expect to see?
    Dave

  8. #8
    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

  9. #9
    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?

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Could it be due to the full outer join where you join on the CPT_CODE?

    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 = '') )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •