April 4, 2005 at 10:32 am
ok i have a table with 3 columns: RIC, SVCode and Compliant
RIC is a code
SVCode is another code which is associated with a RIC code via a lookup table
Compliant is a t/f
i need to generate a report showing each RIC and how many records which have that RIC are compliant=true
then under each RIC that is displayed i need to display Each SVcode associated to the RIC and next to each SV code i need to display the number or records with that RIC and SV code where compliant=true
so far i have been kind of stumped on how to write a query for this.
i have a feeling there would be multiple queries involved but i would like to do it as efficiently as possible because there will be many records to query and i dont want it to be too slow.
can someone please help get me started in the right direction?
April 4, 2005 at 11:06 am
I guess you will be needing 2 queries for this one. The first one will be the number of compliant records for each RIC and the second one will be the number of compliant records for each RIC/SV Code.
SELECT RIC, count(*)
FROM YourTable
WHERE Compliant = true
GROUP BY RIC
SELECT RIC, SVCode, count(*)
FROM YourTable
WHERE Compliant = true
GROUP BY RIC, SVCode
April 4, 2005 at 11:21 am
Apparently there are no unique constraints (such as a primary key) on the table? Unless that is true, it seems to me based on what you said that the number of records with a given RIC and SV codes that was compliant would always be 1. The following assumes no unique constraint.
SELECT A.RIC
, ( SELECT COUNT(*) FROM MyTable B WHERE B.RIC = A.RIC AND B.Compliant = 1 ) AS CompliantRICs
, GROUPING( CompliantRICs ) AS CompliantRICsIsGroup
, SVCode
, ( SELECT COUNT(*) FROM MyTable C WHERE C.RIC = A.RIC AND C.SVCode = A.SVCode AND C.Compliant = 1 ) AS CompliantSVCodes
FROM MyTable A
GROUP BY A.RIC
, ( SELECT COUNT(*) FROM MyTable B WHERE B.RIC = A.RIC AND B.Compliant = 1 )
, SVCode
WITH ROLLUP
On a per row basis CompliantRICs is specific to the SVCode that follows. However, on the rollup rows it will be a total of all compliant RICs for the RIC listed on that rollup row. To aid in determining that programatically I added the CompliantRICsIsGroup column, though you may not have a need for it. I believe this is what you are after, anyhow. If you wanted two different result sets, one for the RIC counts and another for the SVCode counds you could make two more specialized versions of the above.
When I previewed I noticed rfrancisco's response. That is basically the two query version. One thing to note, though, is that if you want zero counts (none of the RIC/SVCodes for a given RIC are complient) his version would not report them. But then it would perform better, so that isn't necessarily a bad thing depending upon your requirements.
April 4, 2005 at 1:29 pm
hey thanks for your replies - Aaron - sorry i did nto mention that there is a PK field called UniqueID.
April 5, 2005 at 9:18 am
To wirte 1 query I'll use rfrancisco's code
SELECT TOP 100 percent RIC AS RIC1,'' as SVCode, count(*)
FROM YourTable
WHERE Compliant = 1
GROUP BY RIC
UNION ALL
SELECT top 100 percent '' as RIC1, SVCode, count(*)
FROM YourTable
WHERE Compliant = 1
GROUP BY RIC, SVCode
ORDER BY RIC1 DESC, SVCode
Vasc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply