SQL - Get all fees from starting from a date in the joined table

I am struggling with an SQL Query.

I have two tables.
Table 1:
No   Date                     Category   Attrib1   Attrib 2 ...
1       06/11/2008           Papa           tall        brown
2       10/11/2008           Mama          short     blond
1       08/11/2008            Papa           short    brown
2       18/11/2008           Mama          short     brown
...

Key is No + Date

Table 2
Date_Payable    Fees   Key
05/11/2008        200      1
08/11/2008        300      1
12/11/2008        200      1
05/11/2008        200      2
15/11/2008        200      2

Key is Key + Date_Payable

I need all columns from Table 1 and a column which gives me the sum of  all fees payble from the date in col (Date).

Example
No   Date                     Category   Attrib1   Attrib 2 ...   Fees
1       06/11/2008           Papa           tall        brown      500
2       10/11/2008           Mama          short     blond      200
1       08/11/2008            Papa           short    brown     200
2       18/11/2008           Mama          short     brown      0

Please see the code I tried below.
It wodkes for some rows, but not for all.
It appears o return too many rows.
I get more rows than I have in table 1.
I should get exactly the same number of rows as in table 1 --> just with a fee column added to it.
     
I tried something along the lines
 
Select table1.No, table1.Date,... sum(fees) 
from table 1 
left join table 2 
on 
table1.key = table2.key 
group by
table1.no,
table1.Date,
...
having table1.date < table2.Date_payable

Open in new window

tomanizerAsked:
Who is Participating?

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

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

Kevin CrossChief Technology OfficerCommented:
You can do this through a subquery:

select t1.*
, IsNull((select sum(fees) from table2 t2 where t2.date_payable > t1.date and t2.key = t1.no), 0) AS Fees
from table1 t1

Regards,
Kevin
0

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
appariCommented:
try this

Select table1.*,
( Select sum(fees) from table2
Where table1.key = table2.key
and table1.date < table2.Date_payable) Sum_Fee

from table 1
0
tomanizerAuthor Commented:
I need to re-use the selected value Sum_Fee in a few calculated columns.
Do i need to put in the subquery everytime?

e.g.
Select table1.*,
( Select sum(fees) from table2
Where table1.key = table2.key
and table1.date < table2.Date_payable) Sum_Fee,
( Select sum(fees) + 10  from table2
Where table1.key = table2.key
and table1.date < table2.Date_payable) Sum_Fee_10,
( Select sum(fees) + 20  from table2
Where table1.key = table2.key
and table1.date < table2.Date_payable) Sum_Fee_20,


from table 1
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Kevin CrossChief Technology OfficerCommented:
I would get it once and then use as a derived table for other formulas.

select *,
Sum_Fees + 10 AS Sum_Fees_10,
Sum_Fees + 20 AS Sum_Fees_20
from (
select t1.*
, IsNull((select sum(fees) from table2 t2 where t2.date_payable > t1.date and t2.key = t1.no), 0) AS Sum_Fees
from table1 t1
) derived
0
tomanizerAuthor Commented:
Hi Mwvisa 1

I probably do not understand.
Would the code above work?

Because Sum_Fees is an alias that was only created in this (sub)-query.
My understanding is that you cannot use aliases in the query itself.

A work around is to sort the results into another table and then use that table to do the calculations.
Not very elegant though.

What I want is something like this:

Select table1.*,
( Select sum(fees) from table2
Where table1.key = table2.key
and table1.date < table2.Date_payable) Sum_Fee,
Sum_Fee + 10 as Sum_Fee_10,
SumFee + 20 as Sum_Fee_20,

Which I understand is similar to your code or am I missing something?
This code will not work because Sum_Fee is an "Invalid column name" for SQl Server.
0
Kevin CrossChief Technology OfficerCommented:
You cannot use the alias inside the same query, which is you look at my example what I did was make the first query a derived table.  Now that it is a derived table -- it is same as if I saved that select statement as a view then select from the view -- you can select by the column names used which in this case includes the alias Sum_Fees.  Give it a try and see.

Since this is SQL Server 2005 by zones, this is also possible.

;WITH feesCTE AS (
select t1.*
, IsNull((select sum(fees) from table2 t2 where t2.date_payable > t1.date and t2.key = t1.no), 0) AS Sum_Fees
from table1 t1
)
SELECT *
, Sum_Fees + 10 AS Sum_Fees_10
, Sum_Fees + 20 AS Sum_Fees_20
FROM feesCTE
0
tomanizerAuthor Commented:
works great. thank you!
0
Kevin CrossChief Technology OfficerCommented:
You are most welcome.
0
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 2005

From novice to tech pro — start learning today.