Results 1 to 15 of 15

Thread: SQL Query Help!!!!

  1. #1
    Join Date
    Sep 2005
    Posts
    9

    Question SQL Query Help!!!!

    Ok here is my situation, I'm trying to do a Distict(claim_number count) and Group by Process_Date, but I also want to bring back additional colums such as Net, Billed, Allowed, Copay_Amt, IPA_Amt_To_Pay. The problem is that everytime I add the additonal columns to the query the Count(Distinct Claim_Number) no longer works ok here is the actually query and see if anyone of you guys can help.

    Select Count(Distinct Claim_number) AS Claim_Number, Process_Date
    FROM dbo.dailyClaims
    WHERE Process_Date BETWEEN '06/01/2005' AND '09/30/2005' AND IPA_ID = '823'
    GROUP BY process_Date
    ORDER BY Process_Date

    Remeber I want to also bring back "Net, Billed, Allowed, Copay_Amt, IPA_Amt_To_Pay" so that I can do some math calculations on these fields.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    You can use this query as a derived query and join it with dailyclaims table again to get other columns


    select a.claim_number,...
    from dailyclaims as a
    join (
    Select Count(Distinct Claim_number) AS Claim_Number, Process_Date
    FROM dbo.dailyClaims
    WHERE Process_Date BETWEEN '06/01/2005' AND '09/30/2005' AND IPA_ID = '823'
    GROUP BY process_Date
    ORDER BY Process_Date) as b
    on a.claim_number=b.claim_number and a.process_date=b.process_date

  3. #3
    Join Date
    Sep 2005
    Posts
    9

    Unhappy Got an Error

    I got the error below while trying to run:

    select a.claim_number,Net, Billed, Allowed, Copay_Amt, IPA_Amt_To_Pay
    from dailyclaims as a
    join (
    Select Count(Distinct Claim_number) AS Claim_Number, Process_Date
    FROM dbo.dailyClaims
    WHERE Process_Date BETWEEN '06/01/2005' AND '09/30/2005' AND IPA_ID = '823'
    GROUP BY process_Date
    ORDER BY Process_Date) as b
    on a.claim_number=b.claim_number and a.process_date=b.process_date

    Error:Server: Msg 1033, Level 15, State 1, Line 8
    The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    May try add 'top 100 percent' in select statement of derived table.

  5. #5
    Join Date
    Sep 2005
    Posts
    9

    Unhappy TOP 100 percent??????

    Sorry, I don't think I know what that is, can you provide some syntax, I'm new at this SQL Server thing.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Like 'select top 100 percent * from table ...', check select syntax in sql books online.

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    Put ORDER BY outside the derived view

    select a.claim_number,Net, Billed, Allowed, Copay_Amt, IPA_Amt_To_Pay
    from dailyclaims as a
    join (
    Select Count(Distinct Claim_number) AS Claim_Number, Process_Date
    FROM dbo.dailyClaims
    WHERE Process_Date BETWEEN '06/01/2005' AND '09/30/2005' AND IPA_ID = '823'
    GROUP BY process_Date) as b
    on a.claim_number=b.claim_number and a.process_date=b.process_date
    ORDER BY b.Process_Date

  8. #8
    Join Date
    Sep 2005
    Posts
    9

    Smile Ok the error is gone but...

    the error is gone but I don't get any data back:

    select a.claim_number,a.Net, a.Billed, a.Allowed, a.Copay_Amt, a.IPA_Amt_To_Pay
    from dailyClaims as a
    join(
    Select Count(Distinct Claim_number) AS Claim_Number, Process_Date
    FROM dbo.dailyClaims
    WHERE Process_Date BETWEEN '06/01/2005' AND '09/30/2005' AND IPA_ID = '823'
    GROUP BY process_Date) as b
    on a.claim_number=b.claim_number and a.process_date=b.process_date
    ORDER BY b.Process_Date

  9. #9
    Join Date
    Sep 2005
    Posts
    9
    I got the TOP 100 percent thing, but I don't get back any data

    select TOP 100 PERCENT *
    from dbo.dailyClaims AS a
    JOIN
    (Select Count(Distinct Claim_number) AS Claim_Number, Process_Date
    FROM dbo.dailyClaims
    WHERE Process_Date BETWEEN '06/01/2005' AND '09/30/2005' AND IPA_ID = '823'
    GROUP BY Process_Date) AS b
    ON a.Claim_Number=b.Claim_Number AND a.Process_Date=b.Process_Date
    ORDER BY b.Process_Date

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    Check execution plan to see what did sql do for the query.

  11. #11
    Join Date
    Sep 2005
    Posts
    9
    ok you lost me man

  12. #12
    Join Date
    Sep 2005
    Posts
    9

    Unhappy Don't know too much SQL syntax other than the basics That's why I'm asking the expert

    so once again my problem now is:

    I don't get back any data, I get all the column names but no data

    select TOP 100 PERCENT *
    from dbo.dailyClaims AS a
    JOIN
    (Select Count(Distinct Claim_number) AS Claim_Number, Process_Date
    FROM dbo.dailyClaims
    WHERE Process_Date BETWEEN '06/01/2005' AND '09/30/2005' AND IPA_ID = '823'
    GROUP BY Process_Date) AS b
    ON a.Claim_Number=b.Claim_Number AND a.Process_Date=b.Process_Date
    ORDER BY b.Process_Date

    This is what I previously had as a query but it also only gave me the column names but no data returned.

    select a.claim_number,a.Net, a.Billed, a.Allowed, a.Copay_Amt, a.IPA_Amt_To_Pay
    from dailyClaims as a
    join(
    Select Count(Distinct Claim_number) AS Claim_Number, Process_Date
    FROM dbo.dailyClaims
    WHERE Process_Date BETWEEN '06/01/2005' AND '09/30/2005' AND IPA_ID = '823'
    GROUP BY process_Date) as b
    on a.claim_number=b.claim_number and a.process_date=b.process_date
    ORDER BY b.Process_Date

  13. #13
    Join Date
    Sep 2002
    Posts
    5,938
    From query execution plan, you can find step by step of how sql processed your query and number of rows involved in each step. Therefore, figure out where is the problem.

  14. #14
    Join Date
    Sep 2005
    Posts
    9

    Unhappy Ok I can see it now

    ok the issue is wher is says "On a.Claim_Number = B.Claim_Number" if I remove this I get records but not count distinctively. Is this impossible to do or what.

  15. #15
    Join Date
    Sep 2005
    Posts
    9
    ok here is the answer guys:

    select * from
    (Select Count(Distinct Claim_number) AS Claim_Number, Process_Date
    FROM dailyClaims
    WHERE Process_Date BETWEEN '06/01/2005' AND '09/30/2005' AND IPA_ID = '823'
    GROUP BY Process_Date) a,

    (select Claim_number,Net, Billed, Allowed, Copay_Amt, IPA_Amt_To_Pay,Process_Date 'b_Process_Date'
    from dailyClaims
    WHERE Process_Date BETWEEN '06/01/2005' AND '09/30/2005' AND IPA_ID = '823') b

    where Process_Date = b_Process_Date

    now this works correctly

Posting Permissions

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