TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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
Reply
Answers (
1
)
The database ‘.mdf’ cannot be opened because it is version 661. This server supports version 612 and earlier
'LAST' is not a recognized function name.??