<div>
Thanks for your fast response! 
 
 
@brandongalderisi &nbsp;what would that look like @8080 diver, I am working on the same issue, I am trying to figure out how to add the the planjoin table with out getting duplicates. I dont have duplicates until I add that table however, there are relationships on four of the columns :( 

<pre><code class="code-100 nolinks" id="code-3273867">SELECT DISTINCT vreportdatacurrentprior.recordid, 
 vreportdatacurrentprior.product, 
 vreportdatacurrentprior.subproduct, 
 vreportdatacurrentprior.orderamount_lastyear, 
 vreportdatacurrentprior.invoiceamount_lastyear, 
 vreportdatacurrentprior.orderamount_currentyear, 
 vreportdatacurrentprior.invoiceamount_currentyear, 
 Datediff(dd, CAST(Floor(CAST(Dateadd(dd, -Datepart(dd, Getdate() 
 ) + 1, Getdate() 
 ) AS DOUBLE 
 PRECISION)) AS 
 DATETIME), CAST( 
 Floor(CAST(Dateadd(dd, -Datepart(dd, Dateadd(MONTH, 1, Getdate() 
 )), 
 Dateadd(MONTH, 1, Getdate 
 ())) AS DOUBLE PRECISION)) AS DATETIME)) - ( 
 Datediff(dd, Dateadd(dd, 6 - ( Datepart(dw, CAST( 
 Floor(CAST(Dateadd(dd, 
 -Datepart 
 (dd, Getdate 
 ( 
 )) + 1, 
 Getdate 
 ( 
 ) 
 ) 
 AS DOUBLE 
 PRECISION 
 )) AS DATETIME)) + @@DATEFIRST - 2 ) % 7, CAST( 
 Floor( 
 CAST( 
 Dateadd(dd, -Datepart(dd, 
 Getdate()) + 
 1, Getdate()) AS DOUBLE 
 PRECISION) 
 ) AS DATETIME)), Dateadd(dd, -( ( Datepart(dw, CAST( 
 Floor(CAST(Dateadd(dd, 
 -Datepart(dd, Dateadd( 
 MONTH 
 , 1, 
 Getdate 
 ( 
 )) 
 ), 
 Dateadd( 
 MONTH, 
 1, 
 Getdate 
 ())) AS 
 DOUBLE 
 PRECISION)) 
 AS DATETIME)) 
 + 
 @@DATEFIRST - 2 ) % 7 ), CAST(Floor(CAST(Dateadd(dd, 
 -Datepart(dd, 
 Dateadd(MONTH 
 , 1, Getdate( 
 ))), 
 Dateadd( 
 MONTH, 
 1, 
 Getdate( 
 ))) AS 
 DOUBLE 
 PRECISION)) AS 
 DATETIME))) - 1 ) / 7 * 2 - CASE (Datepart(dw, CAST(Floor(CAST( 
 Dateadd(dd, 
 - Datepart(dd, Getdate()) + 1, 
 Getdate()) AS DOUBLE PRECISION)) AS 
 DATETIME)) + 
 @@DATEFIRST - 2) % 7 + 1 
 WHEN 6 THEN 2 
 WHEN 7 THEN 1 
 ELSE 2 
 END - 
 CASE (Datepart(dw, CAST(Floor(CAST(Dateadd(dd, 
 - Datepart(dd, Dateadd(MONTH, 1, 
 Getdate())), Dateadd(MONTH, 1, Getdate())) AS 
 DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST 
 - 2) % 7 + 1 
 WHEN 6 THEN 1 
 WHEN 7 THEN 2 
 ELSE 0 
 END AS monthworkingdays, 
 ( Datediff(dd, @start, @end) + 1 ) - ( Datediff(dd, 
 Dateadd(dd, 6 - 
 ( Datepart(dw, @start) + 
 @@DATEFIRST - 2 ) % 7, 
 @start), 
 Dateadd(dd, -( 
 ( Datepart(dw, @end) + 
 @@DATEFIRST - 2 ) % 7 
 ), 
 @end)) - 1 ) / 7 * 2 - 
 CASE (Datepart(dw, @start) + @@DATEFIRST - 2) % 7 + 1 
 WHEN 6 THEN 2 
 WHEN 7 THEN 1 
 ELSE 2 
 END - CASE (Datepart(dw, @end) + @@DATEFIRST - 2) % 7 + 1 
 WHEN 6 THEN 1 
 WHEN 7 THEN 2 
 ELSE 0 
 END AS workingdays, 
 energysites.energyorg, 
 vplanjoin_invoiceorder.order_plan, 
 vplanjoin_invoiceorder.invoice_plan 
FROM vreportdatacurrentprior 
 INNER JOIN exchangerates 
 ON MONTH(vreportdatacurrentprior.transdate) = exchangerates.xratemonth 
 AND vreportdatacurrentprior.transyear = exchangerates.xrateyear 
 AND vreportdatacurrentprior.currencycode = exchangerates.currency 
 INNER JOIN energysites 
 ON vreportdatacurrentprior.site = energysites.sitenumber 
 LEFT OUTER JOIN vplanjoin_invoiceorder 
 ON vreportdatacurrentprior.rectype = vplanjoin_invoiceorder.rectype 
 AND MONTH(vreportdatacurrentprior.transdate) = 
 vplanjoin_invoiceorder.plan_month 
 AND vreportdatacurrentprior.transyear = 
 vplanjoin_invoiceorder.plan_year 
 AND vreportdatacurrentprior.subproduct = 
 vplanjoin_invoiceorder.subproduct 
WHERE ( vreportdatacurrentprior.transdate BETWEEN @start AND @end 
 OR vreportdatacurrentprior.transdate BETWEEN 
 Dateadd(yy, -1, @start) AND 
 Dateadd(yy, -1, @end) ) 
 AND ( vreportdatacurrentprior.businessentity = 'energy' ) 
 AND ( energysites.energyorg = @energyorg )
</code></pre>
</div>

<div>
Thank you @brandongalderisi what is the best solution or how do I troubleshoot this issue?
</div>

<div>
duh a very dim light goes on... &nbsp;For one thing i enter duplicates in my plan table...
</div>

<div>
Okay, just for clarification, is vplanjoin a table or a view and are you really working with a table or a view? 
 
When you look at planjoin, what columns define a unique row? &nbsp;If all of those columns cannot be used in the JOIN you are doing, then you will have duplicate rows . . . basically because you are specifying enough constraints to uniquely select a single row for the planjoin side of the JOIN.
</div>

<div>
thank you @8080 diver 
 
vplanjoin is a view, my goal is to pull in the plan numbers to compare to the actual orders and invoices. The plan column and a combination of the other columns &nbsp;create unique rows.. In the creation of the view I used an union so that I could create columns from recType for order and invoice.
</div>

<div>
<div class="content wysiwyg-content">
Hi Experts! 
 
what is the best way to add an additonal table to an exisiting union join? 

<pre><code class="code-10 nolinks" id="code-3270426">SELECT recType, recordID, transDate, transyear, qty, extendedStandardCost, currencyCode, businessEntity, product, site, lineValue AS 'OrderAmount', 
 CAST(0 AS NUMERIC(30, 2)) AS 'InvoiceAmount', subProduct, CASE WHEN TransYear = Year(getdate()) - 1 THEN LineValue ELSE CAST(0 AS Numeric(30,
 2)) END AS 'OrderAmount_LastYear', CAST(0 AS Numeric(30, 2)) AS 'InvoiceAmount_LastYear', CASE WHEN TransYear = Year(getdate()) 
 THEN LineValue ELSE CAST(0 AS Numeric(30, 2)) END AS 'OrderAmount_CurrentYear', CAST(0 AS Numeric(30, 2)) 
 AS 'InvoiceAmount_CurrentYear'
FROM dbo.vReportData
WHERE (recType = 'order') AND (businessEntity = 'Energy') 
UNION ALL
SELECT recType, recordID, transDate, transyear, qty, extendedStandardCost, currencyCode, businessEntity, product, site, 0 AS 'OrderAmount', 
 lineValue AS 'InvoiceAmount', subProduct, CAST(0 AS Numeric(30, 2)) AS 'OrderAmount_LastYear', CASE WHEN TransYear = Year(getdate()) 
 - 1 THEN LineValue ELSE CAST(0 AS Numeric(30, 2)) END AS 'InvoiceAmount_LastYear', CAST(0 AS Numeric(30, 2)) AS 'OrderAmount_CurrentYear', 
 CASE WHEN TransYear = Year(getdate()) THEN LineValue ELSE CAST(0 AS Numeric(30, 2)) END AS 'InvoiceAmount_CurrentYear'
FROM dbo.vReportData AS ReportData1
WHERE (recType = 'invoice') AND (businessEntity = 'Energy')
</code></pre>
</div>
</div>

Hi Experts!

what is the best way to add an additonal table to an exisiting union join?

union join

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.