One to Many to many View

[JustineB]

I have a View that is the union of 3 views. The view stands to let users see the qty of leads, Archived Leads and Opportunities broken down by marketing Project.
I have various calculations in the 3 views that make up my Union View and all is working well.

I now have the requirement to add a link into another table (Forecast). This table links directly to the Opportunity Table and each Opportuntity can have several forecasts.

However I try and add this into my existing view, I end up with duplicate Opportunities (if there are 3 forecast Values there are 3 Opportunities).

I have a total mental block on this - please can someone explain in thoery how I need to get around this issue. I am sure that it is possible. I have tried various group by's etc.

The View below is my starting point. the forecast table that I am trying to add into it just contains 3 fields:
Opportunity_id
Forecast_date
Forecast_Value

Any help would be gratefully appreciated.

CREATE View Vw_Sales_Pipeline_1
as
SELECT     	dbo.Marketing_Project.Marketing_Project_Name,
                dbo.Vw_MP_Arch_Leads.[Create Date], 
		dbo.Vw_MP_Arch_Leads.[Company name], 
		dbo.Vw_MP_Arch_Leads.[Full Name], 
                dbo.Vw_MP_Arch_Leads.[Lead Days in Cycle], 
		dbo.Vw_MP_Arch_Leads.[Site ID], 
		dbo.Vw_MP_Arch_Leads.[Acc Manager], 
                dbo.Vw_MP_Arch_Leads.[Territory],
		dbo.Vw_MP_Arch_Leads.[Archived Reason], 
		dbo.Vw_MP_Arch_Leads.[City],
		dbo.Vw_MP_Arch_Leads.[Country],
		dbo.Vw_MP_Arch_Leads.[Clas7],
		dbo.Vw_MP_Arch_Leads.[Region Name],
		'' as 'Clas4',
		'' as 'Solution Type',
		'' as 'Last Called',
		'Archived Lead' as [Pipeline Stage],
		'' as 'Opp Name',
		'' as 'Est Dec Date',
		'' as 'Est Rev Date',
		'' as 'Act Dec Date',
		'' as 'Act Rev Date', 
		'' as 'Opp Status',
		'' as 'Progress',
		'' as 'Result 1',
		'' as 'Est Total',
		'' as 'Opp Curr',
		'' as 'Opp Lead Days',
		'' as 'Opp Total Days',
		'' as 'Prob Value',
		'' as 'Prob Inc Dec',
		'' as 'Opp Clas8',
		'' as 'Opp Won Against comp',
		'' as 'Opp Lost against Comp',
		'' as 'Opp Total Desc',
		'' as 'Opp Prob to Win',
		'' as 'GBP Est Total',
		'' as 'GBP Prob Total'


FROM         dbo.Marketing_Project RIGHT OUTER JOIN
                      dbo.Vw_MP_Arch_Leads ON dbo.Marketing_Project.Marketing_Project_Id = dbo.Vw_MP_Arch_Leads.Lead_Source_Id
UNION ALL

SELECT     	dbo.Marketing_Project.Marketing_Project_Name, 
		dbo.Vw_MP_Leads.[Create Date], 
                	dbo.Vw_MP_Leads.[Company name], 
		dbo.Vw_MP_Leads.[Full Name], 
		dbo.Vw_MP_Leads.[Lead Days in Cycle], 
               	dbo.Vw_MP_Leads.[Site ID], 
		dbo.Vw_MP_Leads.[Acc Manager], 
		dbo.Vw_MP_Leads.[Territory],
		'' as 'Arch Reason',
		dbo.Vw_MP_Leads.[City],
		dbo.Vw_MP_Leads.[Country],		
		dbo.Vw_MP_Leads.[Clas7], 
		dbo.Vw_MP_Leads.[Region Name],
		dbo.Vw_MP_Leads.[Clas4],
		dbo.Vw_MP_Leads.[Solution Type],
		dbo.Vw_MP_Leads.[Date Last Called],
		'Current Lead' as [Pipeline Stage],
		'' as 'Opp Name',
		'' as 'Est Dec Date',
		'' as 'Est Rev Date',
		'' as 'Act Dec Date',
		'' as 'Act Rev Date', 
		'' as 'Opp Status',
		'' as 'Progress',
		'' as 'Result 1',
		'' as 'Est Total',
		'' as 'Opp Curr',
		'' as 'Opp Lead Days',
		'' as 'Opp Total Days',
		'' as 'Prob Value',
		'' as 'Prob Inc Dec',
		'' as 'Opp Clas8',
		'' as 'Opp Won Against comp',
		'' as 'Opp Lost against Comp',
		'' as 'Opp Total Desc',
		'' as 'Opp Prob to Win',
		'' as 'GBP Est Total',
		'' as 'GBP Prob Total'
		
		
FROM         dbo.Marketing_Project RIGHT OUTER JOIN
                      dbo.Vw_MP_Leads ON dbo.Marketing_Project.Marketing_Project_Id = dbo.Vw_MP_Leads.Lead_Source_Id

UNION ALL
SELECT     	dbo.Marketing_Project.Marketing_Project_Name, 
		dbo.Vw_MP_Opportunities.[Create Date], 
		dbo.Vw_MP_Opportunities.[Opp Company Name], 
                      	dbo.Vw_MP_Opportunities.[Opp Full Name], 
		dbo.Vw_MP_Opportunities.[Opp Days in Cycle], 
		dbo.Vw_MP_Opportunities.[Opp Site], 
                      	dbo.Vw_MP_Opportunities.[Opp Account Manager], 
		dbo.Vw_MP_Opportunities.[Opp Territory], 
		'' AS 'Arch Reason', 
		dbo.Vw_MP_Opportunities.[Opp City], 
                      	dbo.Vw_MP_Opportunities.[Opp country], 
		dbo.Vw_MP_Opportunities.[Opp Clas7], 
		dbo.Vw_MP_Opportunities.[Region Name], 
                      	dbo.Vw_MP_Opportunities.[Opp Clas4], 
		dbo.Vw_MP_Opportunities.[Opp Solution Type], 
		'' AS 'last Called', 
		'Opportunity' AS [Pipeline Stage], 
                      	dbo.Vw_MP_Opportunities.[Opp Name],
		dbo.Vw_MP_Opportunities.[Est Dec Date], 
		dbo.Vw_MP_Opportunities.[Est Rev Date], 
                      	dbo.Vw_MP_Opportunities.[Act Dec Date], 
		dbo.Vw_MP_Opportunities.[Act Rev Date], 
		dbo.Vw_MP_Opportunities.[Opp Status], 
                      	dbo.Vw_MP_Opportunities.Progress, 
		dbo.Vw_MP_Opportunities.[Result 1], 
		dbo.Vw_MP_Opportunities.[Est Total], 
		dbo.Vw_MP_Opportunities.[Opp Curr], 
                      	dbo.Vw_MP_Opportunities.[Opp Lead Days], 
		dbo.Vw_MP_Opportunities.[Opp Total Days], 
		dbo.Vw_MP_Opportunities.[Prob Value], 
                      	dbo.Vw_MP_Opportunities.[Prob Inc Dec], 
		dbo.Vw_MP_Opportunities.[Opp Clas8], 
		dbo.Vw_MP_Opportunities.[Opp Won Against comp], 
                      	dbo.Vw_MP_Opportunities.[Opp Lost Against Comp], 
		dbo.Vw_MP_Opportunities.[Opp Total Desc], 
		dbo.Vw_MP_Opportunities.[Opp Prob to Win], 
                      	ROUND(dbo.Vw_MP_Opportunities.[GBP Est Total], 2),
		ROUND(dbo.Vw_MP_Opportunities.[GBP Prob Total], 2)
		
 
FROM        	dbo.Marketing_Project RIGHT OUTER JOIN
                      	dbo.Vw_MP_Opportunities ON dbo.Marketing_Project.Marketing_Project_Id = dbo.Vw_MP_Opportunities.Lead_Source_Id

 

[SQLDenis]

what happens when you change
UNION ALL
to
UNION ??

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[JustineB]

I shall have a try and get back to you. I hadn't thought of that.

Thanks for the suggestion

Justine

 

[SQLSister]

if each opportuntiy can have several forcasts, of course you are getting multiple records. One way to avoid is to figure out which one of the forcast records you want and then join to the query that gets that rather than the table.

Questions about posting. See faq183-874

 

[JustineB]

I realise why I am getting multiple records, I just don't know how to get One Opportunity record and then the subset of Forecast records.

I want All Forecast records and All Opportunity records (even if there is no forecast record for the Opportunity)

I tried to join the View to another view which contains the Forecast records, but I had the same issue.

maybe what I am trying to do is not possible in this view. Perhaps I just need to create a report and then a subreport to show the forecast values. If the forecast table hung off the marketing project then this would not be an issue. The issue is that I have One marketing record linked to many Leads, Opportunities and then Many Opportunities linking to Many Forecasts. I am familiar with Business Objects and this scenario is referred to as a Fan trap. I just don't know how to handle it in SQL itself.

I tried adding the forecast values back into my view and changed the UNION ALL to UNION, but as I got the same issue - on thinking about this, I should have known that this would happen as each forecast record is a unique value so UNION is going to behave like UNION ALL in this case anyway.

Thanks for trying to help though

Justine