Nate  Peters

Nate Peters

  • NA
  • 2
  • 33.7k

T-sql query question - URGENT

Aug 22 2011 10:02 PM
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

Answers (1)