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.

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

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.

May try add 'top 100 percent' in select statement of derived table.

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

Like 'select top 100 percent * from table ...', check select syntax in sql books online.

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

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

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

Check execution plan to see what did sql do for the query.

ok you lost me man

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

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.

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.

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