SQL Query with counts only returning equivalent counts

Question

I have a query that consists of 1 table and 2 sub queries. The table being a listing of all customers, 1 sub query is a listing all of the quotes given over a period of time for customers and the other sub query is a listing of all of the orders booked for a customer over the same period of time. What I am trying to do is return a result set that is a customer, the number of quotes given, and the number of orders booked over a given period of time. However what I am returning is only a listening of customers over the period of time that have an equivalent quote and order count. I feel like I am missing something obvious within the context of the query but I am unable to figure it out. Any help would be appreciated. Thank you.

Result Set should look like this

Customer-------Quotes-------Orders Placed

aaa----------------4----------------4

bbb----------------9----------------18

ccc----------------18----------------9

select 
    [Customer2].[Name] as [Customer2_Name],
    (count( Quotes.UD03_Key3 )) as [Calculated_CustomerQuotes],
    (count( Customer_Bookings.OrderHed_OrderNum )) as [Calculated_CustomerBookings]
from Erp.Customer as Customer2
left join  (select 
    [UD03].[Key3] as [UD03_Key3],
    [UD03].[Key4] as [UD03_Key4],
    [UD03].[Key1] as [UD03_Key1],
    [UD03].[Date02] as [UD03_Date02]
from Ice.UD03 as UD03
inner join Ice.UD02 as UD02 on 
    UD03.Company = UD02.Company
And
    CAST(CAST(UD03.Number09 AS INT) AS VARCHAR(30)) = UD02.Key1

left outer join Erp.Customer as Customer on 
    UD03.Company = Customer.Company
And
    UD03.Key1 = Customer.Name

left outer join Erp.SalesTer as SalesTer on 
    Customer.Company = SalesTer.Company
And
    Customer.TerritoryID = SalesTer.TerritoryID

left outer join Erp.CustGrup as CustGrup on 
    Customer.Company = CustGrup.Company
And
    Customer.GroupCode = CustGrup.GroupCode

 where (UD03.Key3 <> '0'))  as Quotes on 
    Customer2.Name = Quotes.UD03_Key1

left join  (select 
    [Customer1].[Name] as [Customer1_Name],
    [OrderHed].[OrderNum] as [OrderHed_OrderNum],
    [OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
    [OrderHed].[OrderDate] as [OrderHed_OrderDate]
from Erp.OrderHed as OrderHed
inner join Erp.Customer as Customer1 on 
    OrderHed.Company = Customer1.Company
And
    OrderHed.BTCustNum = Customer1.CustNum

inner join Erp.OrderDtl as OrderDtl on 
    OrderHed.Company = OrderDtl.Company
And
    OrderHed.OrderNum = OrderDtl.OrderNum)  as Customer_Bookings on 
    Customer2.Name = Customer_Bookings.Customer1_Name

 where Quotes.UD03_Date02 >= '5/15/2018'  and Quotes.UD03_Date02 <= '5/15/2018'  and Customer_Bookings.OrderHed_OrderDate >='5/15/2018'  and Customer_Bookings.OrderHed_OrderDate <= '5/15/2018'

group by [Customer2].[Name]

It isn't clear what you are trying to do here. Try starting here. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, May 15 '18 at 20:10

score 1 · Answer 1 · answered May 15 '18 at 20:24

You have several problems going on here. The first problem is your code is so poorly formatted it is user hostile to look at. Then you have left joins being logically treated an inner joins because of the where clause. You also have date literal strings in language specific format. This should always be the ANSI format YYYYMMDD. But in your case your two predicates are contradicting each other. You have where UD03_Date02 is simultaneously greater than and less than the same date. Thankfully you have =. But if your column is a datetime you have prevented any rows from being returned again (the first being your where clause). You have this same incorrect date logic and join in the second subquery as well.

Here is what your query might look like with some formatting so you can see what is going on. Please note I fixed the logical join issue. You still have the date problems because I don't know what you are trying to accomplish there.

select 
    [Customer2].[Name] as [Customer2_Name],
    count(Quotes.UD03_Key3) as [Calculated_CustomerQuotes],
    count(Customer_Bookings.OrderHed_OrderNum) as [Calculated_CustomerBookings]
from Erp.Customer as Customer2
left join  
(
    select 
        [UD03].[Key3] as [UD03_Key3],
        [UD03].[Key4] as [UD03_Key4],
        [UD03].[Key1] as [UD03_Key1],
        [UD03].[Date02] as [UD03_Date02]
    from Ice.UD03 as UD03
    inner join Ice.UD02 as UD02 on UD03.Company = UD02.Company
                        And CAST(CAST(UD03.Number09 AS INT) AS VARCHAR(30)) = UD02.Key1
    left outer join Erp.Customer as Customer on UD03.Company = Customer.Company
                        And UD03.Key1 = Customer.Name
    left outer join Erp.SalesTer as SalesTer on Customer.Company = SalesTer.Company
                        And Customer.TerritoryID = SalesTer.TerritoryID
    left outer join Erp.CustGrup as CustGrup on Customer.Company = CustGrup.Company
                        And Customer.GroupCode = CustGrup.GroupCode
     where UD03.Key3 <> '0'
)  as Quotes on Customer2.Name = Quotes.UD03_Key1
    and Quotes.UD03_Date02 >= '20180515'  
    and Quotes.UD03_Date02 <= '20180515' 
left join  
(
    select 
        [Customer1].[Name] as [Customer1_Name],
        [OrderHed].[OrderNum] as [OrderHed_OrderNum],
        [OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
        [OrderHed].[OrderDate] as [OrderHed_OrderDate]
    from Erp.OrderHed as OrderHed
    inner join Erp.Customer as Customer1 on OrderHed.Company = Customer1.Company
                        And OrderHed.BTCustNum = Customer1.CustNum
    inner join Erp.OrderDtl as OrderDtl on OrderHed.Company = OrderDtl.Company
                        And OrderHed.OrderNum = OrderDtl.OrderNum
)  as Customer_Bookings on Customer2.Name = Customer_Bookings.Customer1_Name
    and Customer_Bookings.OrderHed_OrderDate >= '20180515'  
    and Customer_Bookings.OrderHed_OrderDate <= '20180515'
group by [Customer2].[Name]

score 0 · Answer 2 · answered May 15 '18 at 20:00

0

COUNT() will just give you the number of records. You'd expect this two result columns to be equal. Try structuring it like this:

SUM(CASE WHEN Quote.UD03_Key1 IS NOT NULL THEN 1 ELSE 0 END) AS QuoteCount, 
SUM(CASE WHEN Customer_Bookings.Customer1_Name IS NOT NULL THEN 1 ELSE 0 END) AS custBookingCount

answered May 15 '18 at 20:00

Error_2646

2,555
1
10
22

When specifying a column it returns the number of rows where the specified column is not NULL. Your code here will return the same number. – Sean Lange May 15 '18 at 20:08
Thank you for the suggestion. Unfortunately after making the changes I am still getting equivalent results. – Tom May 15 '18 at 20:09
1

@SeanLange Ahh, nevermind. I didn't see that the WHERE clause had filters on Quote and Customer_Bookings. The problem isn't with the SUM(CASE WHEN ...) approach, it's that with that WHERE clause it's functionally an inner join. – Error_2646 May 15 '18 at 20:16
1

@Tom Try moving the date restriction from the WHERE clause to the JOIN condition. See https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause If you put a condition in the WHERE clause of an outer table, it becomes logically an inner join. – Error_2646 May 15 '18 at 20:18
Thank you @Error_2646 moving the where condition worked perfectly. – Tom May 15 '18 at 20:24
Did it? I suspect you have other issues going on because of the way you are handling your dates. – Sean Lange May 15 '18 at 20:25

SQL Query with counts only returning equivalent counts

2 Answers2