May 7, 2010 at 4:49 pm
Okay, here is some code for you:
create table dbo.Opportunity
(
OppID int,
abc varchar(20)
)
create table dbo.Book
(
OppID int,
booked int,
BYear int
)
create table dbo.Sales
(
OppID int,
shipped int,
SYear int
)
--inserting into opportunity table
insert into Opportunity values (1,'xxx')
insert into Opportunity values (2,'ghj')
insert into Opportunity values (3,'ghj')
insert into Opportunity values (4,'ghj')
insert into Opportunity values (5,'ghj')
--inserting into Book table
insert into Book values (1,100,2010)
insert into Book values (2,300,2010)
insert into Book values (3,500,2009)
insert into Book values (5,300,2008)
--inserting into sales table
insert into Sales values (2,200,2007)
insert into Sales values (3,300,2010)
insert into Sales values (5,300,2010)
select * from Book;
select * from Sales;
select * from Opportunity;
with OpportunityYears as (
select
s.SYear,
o.OppID
from
dbo.Sales s
cross join dbo.Opportunity o
union
select
b.BYear,
o.OppID
from
dbo.Book b
cross join dbo.Opportunity o
)
select
os.SYear,
sum(isnull(b.booked, 0)) booked,
sum(isnull(s.shipped,0)) shipped
from
OpportunityYears os
left outer join dbo.Book b
on (os.SYear = b.BYear
and os.OppID = b.OppID)
left outer join dbo.Sales s
on (os.SYear = s.SYear
and os.OppID = s.OppID)
group by
os.SYear;
I used isnull to eliminate the warning from aggregating the null values.
Also, your expected results is inaccurate. Check your addition.
May 10, 2010 at 6:42 am
The code is giving exact results i want .
Yes ,i checked the addition you are right my results are inaccurate.
Thanks Lynn.
May 10, 2010 at 6:58 am
Thank you for the feedback. Glad the code is working for you.
May 17, 2010 at 11:36 am
Hi,
I am sorry for this but i need a different output now.actually i want to create the view for the code you have given me but looks like the user needs more data from 2000 year onwards for all the opportunities. so i thought since it has large amounts of data in it i have decided to create a table ...so here is what i did:
create table dbo.Opportunity
(
OppID int,
abc varchar(20)
)
create table dbo.Book
(
OppID int,
booked int,
BYear int
)
create table dbo.Sales
(
OppID int,
shipped int,
SYear int
)
--inserting into opportunity table
insert into Opportunity values (1,'xxx')
insert into Opportunity values (2,'ghj')
insert into Opportunity values (3,'ghj')
insert into Opportunity values (4,'ghj')
insert into Opportunity values (5,'ghj')
insert into Opportunity values (6,'iuy')
--inserting into Book table
insert into Book values (1,100,2010)
insert into Book values (2,300,2007)
insert into Book values (3,500,2009)
insert into Book values (5,300,2008)
--inserting into sales table
insert into Sales values (1,100,2010)
insert into Sales values (2,200,2009)
insert into Sales values (3,300,2010)
insert into Sales values (5,300,2010)
select * from Book;
select * from Sales;
select * from Opportunity;
and i tried to create the table below and insert the data i want :
create table dbo.example
(
OppID int,
booked int,
shipped int,
Year int
)
and insert the data :
insert dbo.example
(
OppID ,
shipped ,
booked ,
Year
)
select
o.OppID ,
s.shipped,
'',
s.SYear
from
Opportunity o
inner join dbo.Sales s
on o.OppID=s.OppID
insert dbo.example
(
OppID ,
booked ,
Year
)
select
o.OppID ,
b.booked,
b.BYear
from
dbo.example e
right outer join Opportunity o
on e.OppID=o.OppID
inner join dbo.Book b
on o.OppID=b.OppID
I got this:
OppID booked shipped Year
1 0 100 2010
1 100 NULL 2010
2 300 NULL 2007
2 0 200 2009
3 0 300 2010
3 500 NULL 2009
5 300 NULL 2008
5 0 300 2010
but i need this:
OppID booked shipped Year
1 100 100 2010
2 300 2007
2 200 2009
3 500 2009
3 300 2010
5 300 2008
5 300 2010
For OppID 1 booking and shipping has happened in the same year i want that in a single row i think that's is the only difference between the expected one one and the one i got.
the results are confusing here so i have attached the excel.
Can any one please tell me how should i get data i showed you excel .it's urgent..
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply