Complex SQL Query - Expert needed.


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

Open 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,
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'
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

Open 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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
You did a good job of describing what you are trying to accomplish; however, a complex query like this is not going to be solved by "air" code.  If you want a truly helpful response you need to provide table creation scripts and data inserts for those table so that the query can be written against "real" data.  There a lot of talented query writers in here but it is in your best interest to lower the barrier of entry for them to dig in.

Scott PletcherSenior DBACommented:
Rather than a function, you can use CROSS|OUTER APPLY within the query itself.

1) If two or more tables are used in a query, always add the table name/alias to every column.  Please verify that I analyzed and guessed the correct alias in the DS1 query below:

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

1) Assuming I had the results of DS1 and DS2, on which columns would I join them?
I'm guessing ps.component_id and i,item_number ... but am I missing something or adding too much in?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
The 2nd SELECT is your DS2, right?
With control_id <> 112? And you need to check it for each DS1.component_id?

Can you provide sample data for both queries (DS1 and DS2)?
And I'm afraid that I didn't get what you want to do with the function. And where to put it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.