Avatar of SPLady
SPLady asked on

T-Sql Join

Hi Experts,
I have a report and I need to join  a table (planjoin) the relationship will be on the current month, current year, rectype and subproduct. however, the report also has date parameters. how do I join the table (planjoin) on the month and year (vreportData) without surpressing the prior years data?
--declare @start datetime
--declare @end datetime
--declare @energyorg int

SELECT vreportdatacurrentprior.extendedstandardcost,
       vreportdatacurrentprior.businessentity,
       vreportdatacurrentprior.product,
       vreportdatacurrentprior.site,
       vreportdatacurrentprior.subproduct,
       SUM(vreportdatacurrentprior.orderamount_lastyear)      AS [Prior Bookings],
       SUM(vreportdatacurrentprior.invoiceamount_lastyear)    AS [Prior Sales] ,
       SUM(vreportdatacurrentprior.orderamount_currentyear)   AS [Current Bookings],
       SUM(vreportdatacurrentprior.invoiceamount_currentyear) AS [Current Sales],
       exchangerates.exchangerate,
       energysites.energyorg,
       vreportdatacurrentprior.qty,
       energysites.sitenumber,
       MWD.Days AS monthworkingdays,
       WD.Days AS workingdays,
       vreportdatacurrentprior.transyear
FROM   vreportdatacurrentprior
CROSS JOIN (select count(*) days
                       from master..spt_values
                       where type='P'  -- series from 0-1024
                        and number between 0 and 60  -- max of 30 days from start
                        and month(number+GETDATE()-30) = month(GETDATE())  -- within start month
                        and (datepart(dw,GETDATE()-30 + number)+@@datefirst-2)%7+1 < 6) MWD
CROSS JOIN (select count(*) days
                       from master..spt_values
                       where type='P'  -- series from 0-1024
                        and number between 0 and @end-@start  -- range from start to end
                        and (datepart(dw,@start + number)+@@datefirst-2)%7+1 < 6) WD
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
INNER JOIN planjoin ON vreportdatacurrentprior.subproduct = planjoin.subproduct
WHERE  ( energysites.energyorg = @energyorg )
AND ( vreportdatacurrentprior.transdate BETWEEN @start AND @end
          OR vreportdatacurrentprior.transdate BETWEEN
             Dateadd(yy, -1, @start) AND Dateadd(yy, -1, @end) )
       AND ( vreportdatacurrentprior.businessentity = 'energy' )
GROUP  BY vreportdatacurrentprior.extendedstandardcost,
          vreportdatacurrentprior.businessentity,
          vreportdatacurrentprior.product,
          vreportdatacurrentprior.site,
          vreportdatacurrentprior.subproduct,
          vreportdatacurrentprior.transyear,
          exchangerates.exchangerate,
          energysites.energyorg,
          vreportdatacurrentprior.qty,
          energysites.sitenumber,
          vreportdatacurrentprior.transdate
ORDER  BY vreportdatacurrentprior.transdate DESC

Open in new window

Microsoft SQL Server 2005

Avatar of undefined
Last Comment
SPLady

8/22/2022 - Mon
mustaccio

If I understand your question correctly, you will need to use an outer join.
ASKER
SPLady

thank you @mustaccio- Why would I use an outer join? I am sort of a newbie  :)
ASKER CERTIFIED SOLUTION
mustaccio

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
SPLady

Makes Perfect Sense... Thank you @mustaccio
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23