Intermediate to Complex SQL help - nested queries + aggregates + over time

[sao123]

i need help doing a summary SQL query, with multiple years of aggregates...

So lets say I have 2 tables

Contracts Table    
[ID] [Contractee] [ContractInitated] [ContractExpired]
1     Scott           Jan 1 2010            null
2     Bill            Jan 1 2010            Jan 1 2011
3     Doug            Jan 1 2011            null
4     Mario           Jan 1 2011            Jan 1 2012
5     Wendy           Jan 1 2012            null
6     Dan             Jan 1 2012            Jan 1 2013
(Null in contractExpired, meants the contract is still active)

Jobs Table
[JobID] [ContractID] [Employees] [Hours]
1      1                 10          2                  
2      2                 9           3
3      3                 8           2
4      4                 7           4
5      5                 6           2
6      6                 5           4
7      1                 11          3
8      2                 12          2
9      3                 13          3
10    4                 14           2
11    5                 15           1
12    6                 10           5

I need help writing SQL to summarize all this data in query.
I know the first thing I need to do is select ALL the years...

Select distinct datepart(yyyy,ContractInitiated) As OneYr
From Contracts

and
I know I need to select 3 independent aggregates

Select Count(ContractID) as CT From Contracts
Select Sum(Employees) as Emp From Jobs
Select Sum(Hours) as Hrs From Jobs

But how do I nest these 2 items together so I get the Summary data for each year?

to product this:

[Year]  [Contracts]  [EmployeeCount] [HourTotal]
2010    2            42               10
2011    4            84               21
2012    6            99               28

 

[Jeraden]

join the 2 tables and just do counts?

select datepart(yyyy,ContractInitiated) year, count(contractid), sum(employees), sum(hours)
from contracts c, jobs j
where c.contract_id = j.contract_id
group by datepart(yyyy,ContractInitiated)

Paraphrasing, but you get the idea. I only know Oracle, so some of that syntax might be oracle specific, so whatever the equivalent is in your rdbms.

Though if a contract spans multiple years, you don't really know what year any of the data is from? So you are just basing it on the start year of the contract?

 

[HumblePie]

I had originally responded to this thread before the roll back.

But since it looks like you are wanting counts and totals, why not just do that to scalar variables with CTEs on smaller join sets? Unless there is something I am missing there isn't really a need to do everything inside a giant select.

also, remember when doing sums to use the groupby or distinct otherwise you'll get the sums on every row used in the sums.

 

[mrjminer]

Too lazy to test it, but something along these lines:

---
SELECT a.Year AS [Year], COUNT(DISTINCT b.ContractId) AS Contracts, SUM(b.Employees) AS EmployeeCount, SUM(b.Hours) AS HourTotal
FROM
(
SELECT ID, DATEPART(yyyy, ContractInitiated) AS [Year]
FROM Contracts
) AS a
INNER JOIN Jobs AS b
ON a.ID = b.ContractId
GROUP BY a.Year
---

Assuming you only want each contract to be counted once in the "Contracts" count. Might need to subquery out the count if that doesn't work; been a while.

 

[sao123]

I did not even realize there were responses, much let alone responses then a reset...

Thanks for this... I'm going to attempt to implement some of this, but I will be back with Questions im sure.