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
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,
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
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.