How Do I Combine Multiple SQL Queries?

I'm having some trouble figuring out any way to combine two SQL queries into a single one that expresses some greater idea.

For example, let's say that I have query A, and query B. Query A returns the total number of hours worked. Query B returns the total number of hours that were available for workers to work. Each one of these queries returns a single column with a single row.

What I really want, though, is es开发者_Go百科sentially query A over query B. I want to know the percentage of capacity that was worked.

I know how to write query A and B independently, but my problem comes when I try to figure out how to use those prewritten queries to come up with a new SQL query that uses them together. I know that, on a higher level, like say in a report, I could just call both queries and then divide them, but I'd rather encompass it all into a single SQL query.

What I'm looking for is a general idea on how to combine these queries using SQL.

Thanks!

Unconstrained JOIN, Cartesian Product of 1 row by 1 row

SELECT worked/available AS PercentageCapacity
FROM ( SELECT worked FROM A ), 
     ( SELECT available FROM B )

You can declare variables to store the results of each query and return the difference:

DECLARE @first INT
DECLARE @second INT

SET @first = SELECT val FROM Table...
SET @second = SELECT val FROM Table...

SELECT @first - @second

The answer depends on where the data is coming from.

If it's coming from a single table, it could be something as easy as:

select totalHours, availableHours, (totalHours - availableHours) as difference
from hoursTable

But if the data is coming from separate tables, you need to add some identifying column so that the rows can be joined together to provide some useful view of the data.

You may want to post examples of your queries so we know better how to answer your question.

You can query the queries:

SELECT
  a.ID
  a.HoursWorked/b.HoursAvailable AS UsedWork
FROM
  ( SELECT ID, HoursWorked FROM Somewhere ) a
INNER JOIN
  ( SELECT ID, HoursAvailable FROM SomewhereElse ) b
ON
  a.ID =  b.ID