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?
 
Kevin CrossConnect With a Mentor Chief 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
 
appariConnect With a Mentor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 CrossConnect With a Mentor Chief 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.