T-sql query question - URGENT
Hello,
Here is the query that I have:
What my goal for the output is the following:
The output should be broken down by investid.
1) For the #dividends table, if the type is DIV then display the date, else show it as NULL - this works in the query below.
2) If the type in the #dividends table is 'DIV' then show the total by investor id.
If the type is not 'DIV' then look in the #interest table and if the year is 1999 then show the total again by investid.
It seems that the totals are not being calculated properly.
Thank you in advance. Below is the code.
create table #Investor(
investid varchar(20),
firstname varchar(20))
create table #dividends(
investid varchar(20),
type varchar(20),
dateval datetime,
amount int
)
create table #interest(
investid varchar(20),
dateval datetime,
amount int
)
insert into #Investor values('1e','Tom James')
insert into #Investor values('2e','Jim Barnes')
insert into #Investor values('3e','James Smith')
insert into #dividends values('1e','DIV','1/1/12',700)
insert into #dividends values('1e','DIV','1/1/12',400)
insert into #dividends values('2e','BUY','1/1/13',300)
insert into #dividends values('3e','DIV','1/1/14',400)
insert into #dividends values('3e','DIV','1/1/14',450)
insert into #interest values('1e','1/1/11',200)
insert into #interest values('1e','1/1/11',300)
insert into #interest values('2e','1/12/99',444)
insert into #interest values('3e','1/10/11',700)
select d.investid, datevalue = case
when d.type = 'DIV'
then d.dateval
else NULL
end,
amount = case
when d.type = 'DIV'
then sum(d.amount)
when year(ind.dateval) = 1999
then sum(ind.amount)
else null
end
from #investor i left join #dividends d
on i.investid = d.investid
left join #interest ind
on i.investid = ind.investid
group by d.dateval,d.type,ind.dateval,d.investid
drop table #investor
drop table #dividends
drop table #interest