Hello, I am using MS SQL Server 2005 in conjuction with CRM 3.0. I am using Bussiness Intelligence Studio 2005 to create reports. My problem is this. There are three entities, Campaign, Opportunity and Quotes. I have written a sql query that gets data from all three. The quote is related to the opportunity and the opportunity is realated to the campaign. The problem I'm having is I want to get the total campaign costs from the Campaign and the sum of all open opprtunities related to that campaign, but because there is more than one opportunity linked to the campaign in the results set the campaign costs are shown in duplicate rows. Is it even posssible to eliminate duplicate rows when you join tables that have a one to many relationship? PWR

Will you post you query and output you are getting ?Sanjeewan

It's a bit tricky. It's late right now, so I am posting a link to my blog where I explain the problem and provide solution ideas: Aggregates with multiple tables For every expert, there is an equal and opposite expert. - Becker's Law My blog

Here is the full query: WITH campaigns AS (SELECT filteredcampaign.campaignid, name FROM dbo.FilteredCampaign), openOpps AS (SELECT o.campaignid, o.opportunityid, SUM(o.estimatedvalue) AS value_of_open_opps FROM campaigns AS c INNER JOIN dbo.FilteredOpportunity AS o ON c.campaignid = o.campaignid WHERE (o.statecode = 0) GROUP BY o.campaignid, o.opportunityid), campaignCosts AS (SELECT cc.new_campaignid, SUM(cc.new_invoicedamount) AS ActualCosts, SUM(cc.new_estimatedcost) AS Estimated_Cost FROM campaigns AS c INNER JOIN dbo.FilteredNew_CampaignCost AS cc ON c.campaignid = cc.new_campaignid GROUP BY cc.new_campaignid), costsNYA AS (SELECT cna.new_campaignid, SUM(cna.new_invoicedamount) AS costs_not_yet_accounted_for FROM campaigns AS c INNER JOIN dbo.FilteredNew_CampaignCost AS cna ON c.campaignid = cna.new_campaignid WHERE (cna.new_invoicedamount IS NULL) GROUP BY cna.new_campaignid), theInvoices AS (SELECT i.opportunityid, SUM(i.totalamount) AS value_of_invoices, i.createdon AS idate FROM openOpps AS o INNER JOIN dbo.FilteredInvoice AS i ON o.opportunityid = i.opportunityid WHERE (@Time_Frame = 'All') AND (DATEDIFF(day, i.createdon, GETUTCDATE()) BETWEEN 0 AND 10000) OR (@Time_Frame = 'Last_Week') AND (DATEDIFF(day, i.createdon, GETUTCDATE()) BETWEEN 0 AND 7) OR (@Time_Frame = 'Last_Month') AND (DATEDIFF(day, i.createdon, GETUTCDATE()) BETWEEN 0 AND 31) OR (@Time_Frame = '3Months') AND (DATEDIFF(day, i.createdon, GETUTCDATE()) BETWEEN 0 AND 93) OR (@Time_Frame = '6Months') AND (DATEDIFF(day, i.createdon, GETUTCDATE()) BETWEEN 0 AND 186) OR (@Time_Frame = 'Last Year') AND (DATEDIFF(day, i.createdon, GETUTCDATE()) BETWEEN 366 AND 782) OR (@Time_Frame = 'ThisYear') AND (DATEDIFF(day, i.createdon, GETUTCDATE()) BETWEEN 0 AND 365) GROUP BY i.opportunityid, i.createdon), openQuotes AS (SELECT q.opportunityid, SUM(q.totalamount) AS value_of_open_quotes, q.createdon AS qdate FROM openOpps AS o INNER JOIN dbo.FilteredQuote AS q ON o.opportunityid = q.opportunityid WHERE (q.statecode = 1) AND (@Time_Frame = 'All') AND (DATEDIFF(day, q.createdon, GETUTCDATE()) BETWEEN 0 AND 10000) OR (@Time_Frame = 'Last_Week') AND (DATEDIFF(day, q.createdon, GETUTCDATE()) BETWEEN 0 AND 7) OR (@Time_Frame = 'Last_Month') AND (DATEDIFF(day, q.createdon, GETUTCDATE()) BETWEEN 0 AND 31) OR (@Time_Frame = '3Months') AND (DATEDIFF(day, q.createdon, GETUTCDATE()) BETWEEN 0 AND 93) OR (@Time_Frame = '6Months') AND (DATEDIFF(day, q.createdon, GETUTCDATE()) BETWEEN 0 AND 186) OR (@Time_Frame = 'Last Year') AND (DATEDIFF(day, q.createdon, GETUTCDATE()) BETWEEN 366 AND 782) OR (@Time_Frame = 'ThisYear') AND (DATEDIFF(day, q.createdon, GETUTCDATE()) BETWEEN 0 AND 365) GROUP BY q.opportunityid, q.createdon), openCoslas AS (SELECT s.opportunityid, SUM(s.totalamount) AS value_of_open_coslas, s.createdon AS sodate FROM openOpps AS o INNER JOIN dbo.FilteredSalesOrder AS s ON o.opportunityid = s.opportunityid WHERE (s.statecode = 0) AND (@Time_Frame = 'All') AND (DATEDIFF(day, s.createdon, GETUTCDATE()) BETWEEN 0 AND 10000) OR (@Time_Frame = 'Last_Week') AND (DATEDIFF(day, s.createdon, GETUTCDATE()) BETWEEN 0 AND 7) OR (@Time_Frame = 'Last_Month') AND (DATEDIFF(day, s.createdon, GETUTCDATE()) BETWEEN 0 AND 31) OR (@Time_Frame = '3Months') AND (DATEDIFF(day, s.createdon, GETUTCDATE()) BETWEEN 0 AND 93) OR (@Time_Frame = '6Months') AND (DATEDIFF(day, s.createdon, GETUTCDATE()) BETWEEN 0 AND 186) OR (@Time_Frame = 'Last Year') AND (DATEDIFF(day, s.createdon, GETUTCDATE()) BETWEEN 366 AND 782) OR (@Time_Frame = 'ThisYear') AND (DATEDIFF(day, s.createdon, GETUTCDATE()) BETWEEN 0 AND 365) GROUP BY s.opportunityid, s.createdon) SELECT CRMAF_FilteredCampaign.createdon AS cdate, CRMAF_FilteredCampaign.campaignid, CRMAF_FilteredCampaign.name AS campaign_name, CRMAF_FilteredCampaign.owneridname AS owner, CRMAF_FilteredCampaign.expectedrevenue, CRMAF_FilteredCampaign.totalcampaignactivityactualcost AS total_campaign_activity_actual_cost, CRMAF_FilteredCampaign.budgetedcost AS Budgeted_Cost, o.opportunityid, o.value_of_open_opps, SUM(q.value_of_open_quotes) AS value_of_open_quotes, SUM(s.value_of_open_coslas) AS Open_Coslas, SUM(i.value_of_invoices) AS Invoice_Sum, cc.ActualCosts AS Actual_Costs, cc.Estimated_Cost, cna.costs_not_yet_accounted_for, q.qdate, s.sodate, i.idate FROM dbo.FilteredCampaign AS CRMAF_FilteredCampaign LEFT OUTER JOIN openOpps AS o ON CRMAF_FilteredCampaign.campaignid = o.campaignid LEFT OUTER JOIN openQuotes AS q ON o.opportunityid = q.opportunityid LEFT OUTER JOIN openCoslas AS s ON o.opportunityid = s.opportunityid LEFT OUTER JOIN theInvoices AS i ON o.opportunityid = i.opportunityid LEFT OUTER JOIN campaignCosts AS cc ON CRMAF_FilteredCampaign.campaignid = cc.new_campaignid LEFT OUTER JOIN costsNYA AS cna ON CRMAF_FilteredCampaign.campaignid = cna.new_campaignid WHERE (@Time_Frame = 'All') AND (DATEDIFF(day, CRMAF_FilteredCampaign.createdon, GETUTCDATE()) BETWEEN 0 AND 10000) OR (@Time_Frame = 'Last_Week') AND (DATEDIFF(day, CRMAF_FilteredCampaign.createdon, GETUTCDATE()) BETWEEN 0 AND 7) OR (@Time_Frame = 'Last_Month') AND (DATEDIFF(day, CRMAF_FilteredCampaign.createdon, GETUTCDATE()) BETWEEN 0 AND 31) OR (@Time_Frame = '3Months') AND (DATEDIFF(day, CRMAF_FilteredCampaign.createdon, GETUTCDATE()) BETWEEN 0 AND 93) OR (@Time_Frame = '6Months') AND (DATEDIFF(day, CRMAF_FilteredCampaign.createdon, GETUTCDATE()) BETWEEN 0 AND 186) OR (@Time_Frame = 'Last Year') AND (DATEDIFF(day, CRMAF_FilteredCampaign.createdon, GETUTCDATE()) BETWEEN 366 AND 782) OR (@Time_Frame = 'ThisYear') AND (DATEDIFF(day, CRMAF_FilteredCampaign.createdon, GETUTCDATE()) BETWEEN 0 AND 365) GROUP BY CRMAF_FilteredCampaign.createdon, CRMAF_FilteredCampaign.campaignid, CRMAF_FilteredCampaign.name, CRMAF_FilteredCampaign.owneridname, CRMAF_FilteredCampaign.expectedrevenue, CRMAF_FilteredCampaign.totalcampaignactivityactualcost, CRMAF_FilteredCampaign.budgetedcost, o.opportunityid, o.value_of_open_opps, q.value_of_open_quotes, s.value_of_open_coslas, i.value_ and result cadte campaignid campaign_name owner expectedrevenue total_campaign... budgetcosts opportunityid ... 20.5.2009 15:00:18 047c4d7d-0345-de11-9330-005056a61aa0 test tuvu Tuure Vuorinen 0,0000 5,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 20.5.2009 17:29:20 bf525c50-1845-de11-9330-005056a61aa0 TEST Road Show X Jukka Niiranen 0,0000 0,0000 0,0000 ebaa12be-64a7-de11-a94d-005056a63d4a 3800000,0000 NULL 3800000,0000 NULL NULL NULL 20.5.2009 17:29:20 bf525c50-1845-de11-9330-005056a61aa0 TEST Road Show X Jukka Niiranen 0,0000 0,0000 0,0000 ebaa12be-64a7-de11-a94d-005056a63d4a 3800000,0000 NULL 3800000,0000 NULL NULL NULL 21.5.2009 16:48:34 bb3ad1c1-db45-de11-b740-005056a61aa0 JPN 09 Technical news Tuure Vuorinen 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 27.5.2009 10:51:47 f338a9ef-604a-de11-aa03-005056a61aa0 tatetat Ozaki, Shu 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 30.9.2009 11:19:20 cbcac2a7-67ad-de11-a94d-005056a63d4a MYS Sample Event Jukka Niiranen 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 20.11.2009 13:32:31 4e9cabb6-8dd5-de11-b409-005056a63d4a ????????TESTTEST Ozaki, Shu 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 14.4.2010 16:08:58 a3535496-9447-df11-816a-005056a63d4a TEST Jukka CoreMotives Jukka Niiranen 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 15.4.2010 11:39:31 1367f31d-3848-df11-816a-005056a63d4a SB Malaysia Jukka Niiranen 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 16.4.2010 12:05:26 194f03e6-0449-df11-816a-005056a63d4a Azrila test Campaign Ibrahim, Ungku 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 16.4.2010 16:03:37 a1bb602d-2649-df11-816a-005056a63d4a MBAM Ibrahim, Ungku 0,0000 0,0000 0,0000 542d2793-2a49-df11-816a-005056a63d4a 110100,0000 NULL 110100,0000 NULL NULL NULL 10.10.2010 13:13:01 fb5d6da9-24d4-df11-a023-005056a63d4a Daniel Test Campaign Reporting Miles, Daniel 5000,0000 2300,0000 3000,0000 67857981-27d4-df11-a023-005056a63d4a 44000,0000 NULL 44000,0000 NULL 6170,0000 6450,0000 10.10.2010 13:13:01 fb5d6da9-24d4-df11-a023-005056a63d4a Daniel Test Campaign Reporting Miles, Daniel 5000,0000 2300,0000 3000,0000 35294b9c-07d5-df11-a023-005056a63d4a 0,0000 NULL NULL NULL 6170,0000 6450,0000 10.10.2010 13:13:01 fb5d6da9-24d4-df11-a023-005056a63d4a Daniel Test Campaign Reporting Miles, Daniel 5000,0000 2300,0000 3000,0000 aa6b5c91-d82a-e111-aaa3-005056a63d4a 165000,0000 165000,0000 NULL NULL 6170,0000 6450,0000 10.10.2010 13:13:01 fb5d6da9-24d4-df11-a023-005056a63d4a Daniel Test Campaign Reporting Miles, Daniel 5000,0000 2300,0000 3000,0000 aa6b5c91-d82a-e111-aaa3-005056a63d4a 165000,0000 198000,0000 NULL NULL 6170,0000 6450,0000 10.10.2010 13:13:01 fb5d6da9-24d4-df11-a023-005056a63d4a Daniel Test Campaign Reporting Miles, Daniel 5000,0000 2300,0000 3000,0000 c4aee15d-de2a-e111-aaa3-005056a63d4a 198000,0000 NULL NULL NULL 6170,0000 6450,0000 10.10.2010 14:06:14 0959be1a-2cd4-df11-a023-005056a63d4a Daniel Test Campaign Email Miles, Daniel 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 11.10.2010 16:39:30 7f435bae-0ad5-df11-a023-005056a63d4a Daniel KL Test Telemarketing campaign Miles, Daniel 10000,0000 0,0000 2000,0000 5e07e086-0fd5-df11-a023-005056a63d4a 0,0000 NULL NULL NULL NULL NULL 11.10.2010 16:39:30 7f435bae-0ad5-df11-a023-005056a63d4a Daniel KL Test Telemarketing campaign Miles, Daniel 10000,0000 0,0000 2000,0000 07717c83-91d6-df11-a023-005056a63d4a 0,0000 NULL NULL NULL NULL NULL 11.10.2010 16:39:34 80435bae-0ad5-df11-a023-005056a63d4a Azrila Test Campaign in October 2010 Ibrahim, Ungku 10000,0000 0,0000 2000,0000 7c07e086-0fd5-df11-a023-005056a63d4a 0,0000 NULL NULL NULL NULL NULL 11.10.2010 16:39:34 81435bae-0ad5-df11-a023-005056a63d4a KOK try campaign Ibrahim, Ungku 10000,0000 0,0000 2000,0000 6207e086-0fd5-df11-a023-005056a63d4a 0,0000 NULL NULL NULL NULL NULL 11.10.2010 16:39:34 81435bae-0ad5-df11-a023-005056a63d4a KOK try campaign Ibrahim, Ungku 10000,0000 0,0000 2000,0000 fef97c50-11d5-df11-a023-005056a63d4a 0,0000 NULL NULL NULL NULL NULL 11.10.2010 16:39:36 82435bae-0ad5-df11-a023-005056a63d4a Ajie test on KL training Pamadaraji, Ajie 10000,0000 0,0000 2000,0000 5707e086-0fd5-df11-a023-005056a63d4a 0,0000 NULL NULL NULL NULL NULL 11.10.2010 16:39:50 4f0c21ba-0ad5-df11-a023-005056a63d4a Fadzril KL Test 1 Ibrahim, Ungku 10000,0000 0,0000 2000,0000 e6beb299-0fd5-df11-a023-005056a63d4a 0,0000 NULL NULL NULL NULL NULL 13.10.2010 12:21:18 cc38f6f0-78d6-df11-a023-005056a63d4a Jeeranawit P Phattraboonyanan, Jeeranawit 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 14.10.2010 14:33:12 a7e19688-54d7-df11-a023-005056a63d4a Daniel Test? 14.10.2010 Miles, Daniel 0,0000 0,0000 0,0000 aaf41112-59d7-df11-a023-005056a63d4a 0,0000 NULL NULL NULL NULL NULL 14.10.2010 14:34:04 4170dda7-54d7-df11-a023-005056a63d4a Fadzril Test Ibrahim, Ungku 0,0000 0,0000 0,0000 9ab712c1-aafe-e011-b364-005056a63d4a 0,0000 NULL NULL NULL NULL NULL 21.10.2010 17:04:15 780264ca-e9dc-df11-a023-005056a63d4a Japan News Test Ishibashi, Hiromi 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 21.10.2010 20:47:23 6e9512f7-08dd-df11-bdfc-005056a63d4a China Test Campaign 1 Monica, Cheng 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 15.8.2011 17:42:38 dd2bfd85-1ac7-e011-a2c1-005056a63d4a Core Motives Web Intelligence Test Campaign Wan, Joanne 0,0000 0,0000 0,0000 c42c5cfc-d4fe-e011-b364-005056a63d4a 149990,0000 NULL 206990,0000 NULL NULL NULL 15.8.2011 17:42:38 dd2bfd85-1ac7-e011-a2c1-005056a63d4a Core Motives Web Intelligence Test Campaign Wan, Joanne 0,0000 0,0000 0,0000 18e2a537-0e01-e111-b364-005056a63d4a 0,0000 NULL NULL NULL NULL NULL 15.8.2011 17:42:38 dd2bfd85-1ac7-e011-a2c1-005056a63d4a Core Motives Web Intelligence Test Campaign Wan, Joanne 0,0000 0,0000 0,0000 d1465c18-1401-e111-b364-005056a63d4a 0,0000 NULL NULL NULL NULL NULL 15.8.2011 17:42:38 dd2bfd85-1ac7-e011-a2c1-005056a63d4a Core Motives Web Intelligence Test Campaign Wan, Joanne 0,0000 0,0000 0,0000 1f027a27-1401-e111-b364-005056a63d4a 0,0000 NULL NULL NULL NULL NULL 9.9.2011 15:30:25 c8d92e31-adda-e011-9ec3-005056a63d4a Daniel Test Miles, Daniel 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL 120,0000 125,0000 27.10.2011 15:11:12 f7613976-6200-e111-b364-005056a63d4a SEA11 Test Miles, Daniel 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 27.10.2011 15:26:58 4d7c8eab-6400-e111-b364-005056a63d4a SEA11 Maintenance Renewal Wan, Joanne 0,0000 0,0000 0,0000 NULL NULL NULL NULL NULL NULL NULL 13.12.2011 19:12:50 fb2f7d02-7325-e111-aaa3-005056a63d4a Test Budget Campaign Miles, Daniel 100000,0000 1200,0000 50000,0000 NULL NULL NULL NULL NULL 2554,0000 4300,0000 13.12.2011 20:26:29 a54bb34c-7d25-e111-aaa3-005056a63d4a Demo Campaign Miles, Daniel 5000,0000 0,0000 1200,0000 NULL NULL NULL NULL NULL 1400,0000 1200,0000 PWR

It's a bit tricky. It's late right now, so I am posting a link to my blog where I explain the problem and provide solution ideas: Aggregates with multiple tables For every expert, there is an equal and opposite expert. - Becker's Law My blog