asked on

union join

Hi Experts!

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

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')

Open in new window

SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

8080_Diver

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SPLady

ASKER

Thanks for your fast response!

@brandongalderisi 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 :(

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 )

Open in new window

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SPLady

ASKER

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

SPLady

ASKER

duh a very dim light goes on... For one thing i enter duplicates in my plan table...

8080_Diver

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? 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.

SPLady

ASKER

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 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.