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:
OppIDbookedshippedYear
101002010
1100NULL2010
2300NULL2007
202002009
303002010
3500NULL2009
5300NULL2008
503002010
but i need this:
OppID bookedshipped Year
11001002010
23002007
22002009
35002009
33002010
53002008
53002010
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 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply