[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-19
8
Medium Priority
?
193 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:tomanizer
  • 4
  • 3
8 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 900 total points
ID: 23000695
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
 
LVL 39

Assisted Solution

by:appari
appari earned 600 total points
ID: 23000698
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
 

Author Comment

by:tomanizer
ID: 23004023
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 23004431
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
 

Author Comment

by:tomanizer
ID: 23004500
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
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 900 total points
ID: 23004589
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
 

Author Comment

by:tomanizer
ID: 23009455
works great. thank you!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 23009904
You are most welcome.
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question