Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Missing Value Grouping

    Newbie SQL Question.

    I need to build a results grid that reports totals or zero for an attribute

    SELECT ClaimType, VendorNumber,
    COUNT (ClaimNumber) AS ClaimCount
    FROM ClaimHeader
    GROUP BY VendorNumber, ClaimType
    ORDER BY VendorNumber ASC;

    THe underlying data has two claim types (UB, and HCFA). Some vendor numbers have only UB claims or only HCFA claims. I want my totals to show a zero for those vendor numbers. Example

    VendorNumber ClaimType ClaimCount
    234 UB 21
    234 HCFA 12
    235 UB 0
    235 HCFA 15

    My current group by doesn't give me the 3rd row in my example. How do I change my query to add those rows that have no claims as a zero. Put another way, every vendorNumber has to report a HCFA total and a UB total, even if they're zero.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is flat-out "brute force" to get the job done using only what you've given us... There is almost certainly a better way.
    Code:
    SELECT v.VendorNumber, c.ClaimType, (SELECT Coalesce(Count(*), 0)
       FROM ClaimHeader AS ch
       WHERE  ch.VendorNumber = v.VendorNumber
          AND ch.ClaimType = c.ClaimType) AS ClaimCount
       FROM (SELECT DISTINCT VendorNumber
          FROM ClaimHeader) AS v
       CROSS JOIN (SELECT DISTINCT ClaimType
          FROM ClaimHeader) AS c
    -PatP

  3. #3
    Join Date
    Jun 2004
    Posts
    57

    Other Information

    Is something else I could provide to help with my question? This might b closer but i'm still missing the zeros for providers with not HCFA or UB claims.


    SELECT t.PeriodID, p.ProviderName, p.AltProviderID_1,
    t.FormTypeID,
    ClaimCount = CASE WHEN SUM(ClaimCount) IS NULL THEN 0 ELSE SUM(ClaimCount) END

    FROM dvTATClaim t
    INNER JOIN dimProvider p
    ON t.ProviderID = p.ProviderID

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sure...provide the DDL of the table

    CREATE TABLE myTable...

    Some Sample Data

    INSERT INTO myTable(
    SELECT 'data' UNION ALL
    SELECT 'data' UNION ALL
    ect

    And what the results should look like...

    You could probably use a stop watch to time how long it takes after you post that for your answer...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are there other tables that have:
    1. Every possible VendorNumber
    2. Every possible ClaimType

    Also, did the previous query I provided give you the results that you want?

    -PatP

Posting Permissions

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