-
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
-
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.
-
May try add 'top 100 percent' in select statement of derived table.
-
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.
-
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
-
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
-
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.
-
-
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
-
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 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.
-
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
-
Forum Rules
|
|