December 16, 2009 at 8:20 am
Is it possible to create a subquery that uses the parent query WHERE clause? I'm trying to do a count and distinct count in a single query....
December 16, 2009 at 8:25 am
Confused. Why not show us what you are trying to accomplish.
December 16, 2009 at 8:42 am
If you show some code, or what you're trying to do, we could help.
December 16, 2009 at 8:48 am
SELECT
a.provcode
,(SELECT COUNT(DISTINCT acctno)
FROM charge_t
WHERE
dateofservice = '11/16/2009'
AND priinstype = 'R'
AND curinstype = 'R'
) AS 'Patient Count'
,(SELECT COUNT(proccode)
FROM charge_t
WHERE
dateofservice = '11/16/2009'
AND priinstype = 'R'
AND curinstype = 'R'
) AS 'Procedure Count'
,b.provname + SPACE(1) + b.altid7 AS 'Provider Name'
FROM
charge_t a
,provcode_t b
WHERE
a.dateofservice = '11/16/2009'
AND a.priinstype = 'R'
AND a.curinstype = 'R'
AND a.provcode = b.provcode
December 16, 2009 at 8:49 am
I agree with Steve & Lynn, I'm confused.
Based only on what you've said, I'd say the answer is no, you can't use the WHERE clause of the outer query within a sub-query, but more explanation of what it is you want might lead to a more satisfying answer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2009 at 8:53 am
What about something like this?
SELECT
a.provcode
,COUNT(DISTINCT acctno) AS 'Patient Count'
,COUNT(proccode) AS 'Procedure Count'
,b.provname + SPACE(1) + b.altid7 AS 'Provider Name'
FROM
charge_t a
INNER JOIN provcode_t b
ON a.provcode = b.provcode
WHERE
a.dateofservice = '11/16/2009'
AND a.priinstype = 'R'
AND a.curinstype = 'R'
GROUP BY a.provcode,b.provname,b.altid7
Although, the GROUP BY might not work well there. In which case, I'd suggest making the SELECT statement into a derived table and then grouping by it after you've concatenated the Provider name.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2009 at 9:02 am
You do provide an excellent solution however the WHERE clause in the parent query will contain conditions on the proccode column which have yet to be implemented. When those conditions are defined, then the acctno distinct count will change when I actually need the entire acctno distinct count without the proccode conditions.
My goal is to get a distinct count of the acctno for the date range and then of those acctno's, count how many have a specific proccode.....
December 16, 2009 at 9:06 am
Then use a derived table and join against it. That would, more or less, be the equivalent of maintaining one WHERE clause and then using it for sub-selects.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2009 at 9:08 am
Take Grant's suggestion and create a CTE to hold the data and use that in your joins.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply