0

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
  • Why do you OR the conditions, of course you need to AND ZT1.[SaleDate] >= '20170601' AND ZT1.[SaleDate] <= '20170625' or simplified AND ZT1.[SaleDate] BETWEEN '20170601' AND '20170625' – dnoeth Jun 26 '17 at 6:36
2

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

  • Specifically, the original terms ZT1.[SaleDate] >= '20170601' AND ZT1.[SaleDate] <= '20170625' fail, when ZT1 doesn't return a row so [SaleDate] is NULL. So an ... OR ZT1.[SaleDate] IS NULL will let data pass. Otherwise, to get deep, technically the test isn't "False" but "Unknown", because there's a NULL. docs.microsoft.com/en-us/sql/t-sql/language-elements/… mixes up the words "Null" and "Unknown" ... main points, "Not (This is Unknown)" evaluates to "Unknown", unlike false/true, and "(Unknown) OR (True)" evaluates to "True", – Robert Carnegie Jun 26 '17 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.