Complex SQL Query - Expert needed.

Hi,

I need some help with a pretty complex query.  I’m using SQL 2008 R2
My starting point is this SELECT query  ( I’ll call the results returned by this DS1)

SELECT component_id, control_id, item_number, description FROM product_structures INNER JOIN items ON product_structures.component_id = items.item_id WHERE control_id = 112

Select allOpen in new window

Now the tricky part.  For every row of DS1 I need to somehow complete this logic:
1.      Use the component_id to derive another set of results (DS2).
2.      Analyse DS2 and return a single value back to DS1

I figure that one way to accomplish this would be for the first select statement to call a function.  That function would do all the processing and return the value back.
I tried working on the function but I’m struggling with analysis stage.

So far I have this:

SELECT a.title, a.status as doc_status, i.status as part_status, component_id, a.revision,
COUNT(item_serial_nos.item_id) AS serial_count,
CASE
WHEN a.status = 'A' AND i.status = 'R' AND COUNT(item_serial_nos.item_id) > 0 THEN 'G1'
WHEN a.status = 'A' AND i.status = 'R' AND COUNT(item_serial_nos.item_id) = 0 THEN 'G2'
WHEN a.status = 'N' AND i.status = 'N' THEN 'R'
ELSE 'A' END AS Cat
FROM product_structures INNER JOIN documents AS a ON product_structures.control_id = a.document_id
INNER JOIN items AS i ON product_structures.parent_id = i.item_id
LEFT OUTER JOIN item_serial_nos ON i.item_id = item_serial_nos.item_id
WHERE component_id = @component_id AND control_id != '112'
GROUP BY a.title, a.status, i.status, component_id, a.revision, i.item_number
ORDER BY i.item_number

Select allOpen in new window

Within the function I need to process the results using this logic:

If there is at least one row in DS2 where Cat = G1 then return 0 and exit
Else if there are no rows in DS2 where Cat = G1 but at least one row where Cat = G2 return 10 exit
Else if there are no G1 or G2 count the number of rows where cat = R, then divide the total number of rows where Cat = A by 2, add these values together and then divide by the total number of rows in DS2, then multiple this by 100 to return a percentage value

The end results would a number returned to the original DS1.

Any pointers or suggestions appreciated as this is well out of my depth and requires a SQL expert.
Thanks.