How to limit a full join by date-CodePudding

Our database is on SQL Server 2014. I had the exact same issue as SQL join table to itself to get data for previous year, and was so happy to find the answer. So I setup my query:

select distinct 
    coalesce (a.year, b.year 1) [Year]
  , coalesce(a.Month, b.Month) [Month]
  , coalesce(a.account, b.account) [Account]
  , case when a.sales > 0
        then a.sales
        else 0 end [Sales MTD]
  , case when b.sales > 0
        then b.sales
        else 0 end [Sales Previous Year]
  , (case when b.sales!= 0
        then cast((case when a.sales > 0
                then a.sales
                else 0 end-b.sales)/b.sales*100 as decimal(10,1))
        else 0 end) [% Difference]
  , sum(a.sales) over (Partition by a.account, a.year order by a.month) [Account Sales YTD]
from yourtable a
      full join yourtable b 
            on  a.account = b.account
            and a.month = b.month
            and a.year = b.year 1

The output is giving me results for months in 2022 that haven't happened yet (and starting 2023 too). https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=9a2c29f1af08f525a1acee16b0efa2fd

I tried adding and b.[year] 1<=year(getdate()) to my join filters, but that doesn't seem to affect output at all. I also tried writing a where clause, that ended up with a case that made my head spin.

How can I prevent future dates from showing up in the end result? Bonus question: Can I make my YTD calculation accept a 0 as a value for any lines added due to sales history?

CodePudding user response：

Add a WHERE clause

... 
where datefromparts(coalesce(a.year, b.year 1), coalesce(a.Month, b.Month), 1) <= cast(getdate() as date)

CodePudding user response：

if I've understood correctly, you need a left outer join, as you care when you have records in one table but not both.

CodePudding user response：

Narrow the list of relevant rows prior to doing the full join.

with A as (
    select *
    from tableA
    where ...
), B as (
    select *
    from tableB
    where ...
)
select * from A full outer join B on ...

You could also just do this inline:

select *
from
    (select * from tableA where ...) as A
    full outer join
    (select * from tableB where ...) as B

EDIT: If you "pre-filter" the data within relevant dates you don't need to do the awkward coalesced checks later:

with A as (
    select year, month, account, sales
    from yourtable
    -- if there's no future data then this whole cte may be unnecessary
    where year < year(getdate())
       or year = year(getdate()) and month <= month(getdate())
), B as (
    select year   1 as year, month, account, sales
    from yourtable 
    where year < year(getdate()) - 1
       or year = year(getdate()) - 1 and month <= month(getdate())
)
select
    coalesce(a.year, b.year) [Year]
  , coalesce(a.Month, b.Month) [Month]
  , coalesce(a.account, b.account) [Account]
  , sum(a.sales) over (Partition by a.account, a.year order by a.month) [Account Sales YTD]  
from A full outer join B
    on  a.account = b.account
    and a.month = b.month
    and a.year = b.year
order by 1, 2

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=26c30b190d475d8679f2253a3c428f1f