SQL Server 2008 R2 – Full Outer Join with Where Clause

join;sql serversql-server-2008-r2t-sql

I am in need of a way to return all records from both tables BUT have a where condition on ZT1 I have tried both queries below, however Query 1 ignores any row from @TTBL that does not exist in ZT1, while Query 2 ignores the WHERE condition I want to put on the table ZT1 (bc it was changed to an or)

How should my query be written in order to accomplish my desired result of all data from @TTBL AND only data from ZT1 that fall between the date criteria

    Query 1
SELECT
    Employee = COALESCE(ZT1.Employee,Z.Name),
    COUNT(ZT1.sales) AS Salecount,
    SUM(ISNULL(ZT1.[SaleTotal],0)) AS SaleTotal,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)/NULLIF(ZT1.[SaleTotal],0)) AS ActualSale,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)) AS ProfitMargin,
    ISNULL(Z.SaleTotal0,0) AS SaleTotal0,
    ISNULL(Z.SaleTotal1,0) AS SaleTotal1
FROM dbo.holdings AS ZT1
FULL OUTER JOIN @TTBL AS Z ON Z.Name = ZT1.Employee
WHERE ZT1.[SaleDate] >= '20170601'
AND ZT1.[SaleDate] <= '20170625'
GROUP BY ZT1.Employee,Z.SaleTotal0,Z.SaleTotal1,Z.Name

--Query 2
SELECT
    Employee = COALESCE(ZT1.Employee,Z.Name),
    COUNT(ZT1.sales) AS Salecount,
    SUM(ISNULL(ZT1.[SaleTotal],0)) AS SaleTotal,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)/NULLIF(ZT1.[SaleTotal],0)) AS ActualSale,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)) AS ProfitMargin,
    ISNULL(Z.SaleTotal0,0) AS SaleTotal0,
    ISNULL(Z.SaleTotal1,0) AS SaleTotal1
FROM dbo.holdings AS ZT1
FULL OUTER JOIN @TTBL AS Z ON Z.Name = ZT1.Employee
OR ZT1.[SaleDate] >= '20170601'
AND ZT1.[SaleDate] <= '20170625'
GROUP BY ZT1.Employee,Z.SaleTotal0,Z.SaleTotal1,Z.Name

Best Answer

Give this query a shot. I suspect that the "WHERE" clause is effectively turning your full outer join into a right join. When a record doesn't exist in ZT1, your where clauses are obviously going to evaluate false. By enclosing the AND clause in parenthesis for sanity's sake, and adding some sort of OR clause (I assumed that you should have a field named SaleDate in @TTBL, and that you only want to fetch records where the SaleDate is in that range one way or another), you should hopefully see this problem go away. Let me know how it goes.

SELECT Employee = COALESCE(ZT1.Employee,Z.Name), COUNT(ZT1.sales) AS Salecount, SUM(ISNULL(ZT1.[SaleTotal],0)) AS SaleTotal, SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)/NULLIF(ZT1.[SaleTotal],0)) AS ActualSale, SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)) AS ProfitMargin, ISNULL(Z.SaleTotal0,0) AS SaleTotal0, ISNULL(Z.SaleTotal1,0) AS SaleTotal1 FROM dbo.holdings AS ZT1 FULL OUTER JOIN @TTBL AS Z ON Z.Name = ZT1.Employee WHERE (ZT1.[SaleDate] >= '20170601' AND ZT1.[SaleDate] <= '20170625') OR (Z.[SaleDate] >= '20170601' AND Z.[SaleDate] <= '20170625') GROUP BY ZT1.Employee,Z.SaleTotal0,Z.SaleTotal1,Z.Name

Related Solutions

SQL Server 2008 – Troubleshooting Full Outer Join Issues

You should do an isnull on both policynumbers so that you can group properly.

Since it's an outer-join, there's the possibility of one side of the join being NULL while still having data.

select sum(premium) Prem_Sum, sum(Loss) Loss_Sum, isnull(t1.policynumber, t2.policynumber)
from t1 full outer join t2 on t1.policynumber = t2.policynumber
group by isnull(t1.policynumber, t2.policynumber)

Sql-server – Full outer join problems

The problem is in your WHERE clause. This piece of code in your WHERE clause for example.

and osnArtikel.SpletObjava='D'

is going to restrict your output whenever osnArtikel.SpletObjava is NULL. Including whenever there wasn't a match on it. (your OUTER results)

One method to fix it is to move your restrictions into the ON clause. Not generally something you want to do since the ON clause is a JOIN but with OUTER JOINs it's not unreasonable.

select stranka.SifraStranke,
--  stranka.DomacaStran,
--  strFaktura.SifraGrucePopusti,
--  popust.OdstotekPopusta,
--  popust.SifraGruceArtikla,
--  artProdaja.SifraArtikla,
    osnArtikel.SifraArtikla,
    cene.ProdajnaCena * ((100-popust.OdstotekPopusta)/100)

from STRStrankeSplosno stranka      -- customer basic data table
left join 
    STRStrankeFaktura strFaktura    -- customer financial data table
    on stranka.SifraStranke=strFaktura.SifraStranke
    and strFaktura.StevilkaSifranta=2
    and strFaktura.SifraGrucePopusti<>''
left join
    ARTPopustiStranke popust        -- customer discount data table for each discount group
    on strFaktura.SifraGrucePopusti=popust.SifraGruceStranke
    and popust.StevilkaSifranta=2
left join
    ARTArtikliProdaja artProdaja    -- item discount group data table
    on popust.SifraGruceArtikla=artProdaja.SifraGrucePopust
    and artProdaja.StevilkaSifranta=2
full outer join
    ARTArtikliOsnovniP osnArtikel   -- item basic data table
    on osnArtikel.SifraArtikla=artProdaja.SifraArtikla
    and osnArtikel.SpletObjava='D'

cross apply (
    select top 1 cene.ProdajnaCena
    from ARTArtikliCeneCP cene      -- item prices data table
    where cene.SifraArtikla=osnArtikel.SifraArtikla
    and cene.SifraCenovnegaPodrocja='MC'
    order by cene.DatumZacetkaVeljavnosti desc
    ) cene
where stranka.DomacaStran<>''
and stranka.StevilkaSifranta=2
order by stranka.SifraStranke, osnArtikel.SifraArtikla

Another method is to include checks for the column being NULL in your WHERE clause. This may or may not be the way you want to go because if the column you are checking is also nullable you could end up with some unintended results. The best method is to check if a non-null column (primary key column for example) is nullable.

select stranka.SifraStranke,
--  stranka.DomacaStran,
--  strFaktura.SifraGrucePopusti,
--  popust.OdstotekPopusta,
--  popust.SifraGruceArtikla,
--  artProdaja.SifraArtikla,
    osnArtikel.SifraArtikla,
    cene.ProdajnaCena * ((100-popust.OdstotekPopusta)/100)

from STRStrankeSplosno stranka      -- customer basic data table
left join 
    STRStrankeFaktura strFaktura    -- customer financial data table
    on stranka.SifraStranke=strFaktura.SifraStranke
left join
    ARTPopustiStranke popust        -- customer discount data table for each discount group
    on strFaktura.SifraGrucePopusti=popust.SifraGruceStranke
left join
    ARTArtikliProdaja artProdaja    -- item discount group data table
    on popust.SifraGruceArtikla=artProdaja.SifraGrucePopust
full outer join
    ARTArtikliOsnovniP osnArtikel   -- item basic data table
    on osnArtikel.SifraArtikla=artProdaja.SifraArtikla

cross apply (
    select top 1 cene.ProdajnaCena
    from ARTArtikliCeneCP cene      -- item prices data table
    where cene.SifraArtikla=osnArtikel.SifraArtikla
    and cene.SifraCenovnegaPodrocja='MC'
    order by cene.DatumZacetkaVeljavnosti desc
    ) cene
where stranka.DomacaStran<>''
and stranka.StevilkaSifranta=2
and (osnArtikel.PrimaryKeyColumn IS NULL 
        OR osnArtikel.SpletObjava='D' )
and (strFaktura.PrimaryKeyColumn IS NULL
    OR (strFaktura.StevilkaSifranta=2
        and strFaktura.SifraGrucePopusti<>'')
    )
and (popust.PrimaryKeyColumn IS NULL 
    OR popust.StevilkaSifranta=2)
and (artProdaja.PrimaryKeyColumn IS NULL 
    OR artProdaja.StevilkaSifranta=2)
order by stranka.SifraStranke, osnArtikel.SifraArtikla

Best Answer

Related Solutions

SQL Server 2008 – Troubleshooting Full Outer Join Issues

Sql-server – Full outer join problems

Related Question