June 9, 2017 at 4:44 am
Hi,
Sorry in advance if I am posting in the wrong section. I have compiled a query where I like to know Total number of customers , New customer and active customers within 6 months. When I try to run it it only shows left total number of customer and null the other two columns. I have tried right join but result is similar.
Could you please advise where I am making a mistake?
WITH TotalNoOfCompanies AS
(SELECT Count(CustomerAccountNumber) Account
FROM SLCustomerAccount),
ActiveCustomer As
(SELECT Count(CustomerAccountNumber) Account ,COUNT(CustomerAccountName) Name
FROM SLCustomerAccount
Where DATEDIFF(MM, DateOfLastTransaction , GETDATE()) < 6),
NewCompanies As
(SELECT Count(CustomerAccountNumber) Account ,COUNT(CustomerAccountName) Name
FROM SLCustomerAccount
WHERE DATEDIFF(MM, AccountOpened , GETDATE()) < 6)
Select T.Account,
(A.Account ) ,
(N.Account) From TotalNoOfCompanies T
Left Outer Join NewCompanies N ON N.Account = T.Account
Left Outer Join ActiveCustomer A on A.Account = T.Account
June 9, 2017 at 7:23 am
it appears you are "joining" on the actual counts...which is not what you want.
maybe something along these lines....
SELECT COUNT(CustomerAccountNumber) AS total,
SUM(CASE
WHEN DATEDIFF(MM, DateOfLastTransaction, GETDATE()) < 6
THEN 1
ELSE 0
END) active,
SUM(CASE
WHEN DATEDIFF(MM, AccountOpened, GETDATE()) < 6
THEN 1
ELSE 0
END) new
FROM SLCustomerAccount;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 9, 2017 at 7:24 am
Keep it simple:
SELECT
TotalNoOfCompanies = COUNT(*),
ActiveCustomer = SUM(CASE WHEN DATEDIFF(MM, DateOfLastTransaction, GETDATE()) < 6 THEN 1 ELSE 0 END),
NewCompanies = SUM(CASE WHEN DATEDIFF(MM, AccountOpened, GETDATE()) < 6 THEN 1 ELSE 0 END)
FROM SLCustomerAccount
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 9, 2017 at 7:34 am
The query forms suggested by Chris and J are the way to go for this.
Just to explain the results you were seeing, the join criteria don't make any sense.
The "Account" column coming from each CTE is NOT the account number (and the other column is not the AccountName). It's the name of the column in the result set that holds the result of the COUNT aggregate, which will just be some integer (as a side note, the same applies to the second column in the second and third CTEs; also, since both are just the result of a COUNT, they're going to return the same value unless there are rows in the original table that have a NULL for Name but not for Number, or vice versa).
You're basically trying to join the CTEs on the condition that they all return the same number of accounts, which doesn't make much sense. Further, since its highly unlikely that the COUNT in each CTE will be the same, you're going to get NULLs from CTEs on the inner side of the join, since the join condition is false.
Cheers!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply