May 25, 2010 at 11:57 am
Hi everyone;
I need your help! I'm trying to create a report that is going to list out the customer ID of all those clients that meet a very specific parameter...
The query I have (so far) is:
SELECT customerID
FROM contactcategory
WHERE categoryID IN ('21','22','24') and (dbo.dateonly(contactOn) between DATEADD(MONTH, -6, dbo.dateonly(GETDATE())) AND dbo.dateonly(GETDATE()))
So, in the end, what I'm hoping to achieve is a list of customers that has only one of those categories within the date range. An additional complexity is that ID's 21 and 22 are grouped, meaning that if a customer has either one of those attached to it, it will only count as one...
I think I can do a better job identifying my dilemma through an illustration so here goes;
Customer A has at least 1 contactcategoryID 21 and at least 1 contactcategoryID 24 in the date range given, this customer will NOT be listed in the results
Customer B has at least 1 contactcategoryID 22 and at least 1 contactcategoryID 24 in the date range given, this customer also will NOT be listed in the results
Customer C has at least 1 contactcategoryID 21 and contactcategoryID 22 in the date range given, this customer WILL be listed in the results
Customer D has at least 1 contactcategoryID 21 in the date range given, this customer WILL be listed in the results
Customer E has at least 1 contactcategoryID 22 in the date range given, this customer WILL be listed in the results
Customer F has at least 1 contactcategoryID 24 in the date range given, this customer WILL be listed in the results
I hope that there is someone out there that will be able to help me with this (and that it makes sense :-)), thank you for your time.
May 25, 2010 at 12:13 pm
iklektic (5/25/2010)
Hi everyone;I need your help! I'm trying to create a report that is going to list out the customer ID of all those clients that meet a very specific parameter...
The query I have (so far) is:
SELECT customerID
FROM contactcategory
WHERE categoryID IN ('21','22','24') and (dbo.dateonly(contactOn) between DATEADD(MONTH, -6, dbo.dateonly(GETDATE())) AND dbo.dateonly(GETDATE()))
So, in the end, what I'm hoping to achieve is a list of customers that has only one of those categories within the date range. An additional complexity is that ID's 21 and 22 are grouped, meaning that if a customer has either one of those attached to it, it will only count as one...
I think I can do a better job identifying my dilemma through an illustration so here goes;
Customer A has at least 1 contactcategoryID 21 and at least 1 contactcategoryID 24 in the date range given, this customer will NOT be listed in the results
Customer B has at least 1 contactcategoryID 22 and at least 1 contactcategoryID 24 in the date range given, this customer also will NOT be listed in the results
Customer C has at least 1 contactcategoryID 21 and contactcategoryID 22 in the date range given, this customer WILL be listed in the results
Customer D has at least 1 contactcategoryID 21 in the date range given, this customer WILL be listed in the results
Customer E has at least 1 contactcategoryID 22 in the date range given, this customer WILL be listed in the results
Customer F has at least 1 contactcategoryID 24 in the date range given, this customer WILL be listed in the results
I hope that there is someone out there that will be able to help me with this (and that it makes sense :-)), thank you for your time.
Let me give it a go:
-- Use CTE so I don't have to put the date criteria in several times
WITH indaterange AS (
SELECT customerID, categoryID from contactcategory WHERE (dbo.dateonly(contactOn) between DATEADD(MONTH, -6, dbo.dateonly(GETDATE())) AND dbo.dateonly(GETDATE()))
)
-- Main query
SELECT
customerID
FROM
(
-- Inner query counts category occurances
SELECT
customerID,
(SELECT COUNT(*) FROM indaterange G1 WHERE G1.customerID = IDR.customerID AND categoryID in ('21', '22')) AS Group1,
(SELECT COUNT(*) FROM indaterange G2 WHERE G2.customerID = IDR.customerID AND categoryID = 24) AS Group2
FROM indaterange IDR
) A
-- Require a positive number in Group1 or Group2, but not both
WHERE (Group1 > 0 AND Group2 = 0) OR (Group1 = 0 AND Group2 > 0)
Should get the job done. There may be a slightly more efficent way, but based on your very specific criteria, it would be hard to arrive upon.
--J
May 25, 2010 at 12:26 pm
Ok, so I'm guessing that "CTE" represents the string below (and CTE means Concatenate?):
WITH indaterange AS )
SELECT customerID, categoryID from contactcategory WHERE (dbo.dateonly(contactOn) between DATEADD(MONTH, -6, dbo.dateonly(GETDATE())) AND dbo.dateonly(GETDATE()))
)
Is that correct?
(Thank you for your response, I am going to play around with this to see if this works.)
May 25, 2010 at 12:33 pm
CTE stands for "Common table expression". It's the equivlent of creating a table variable and then using it in the following query, except it all happens in a single query.
If your curious, you can google "sql server cte". There's lots of good information out there, and CTEs are very useful. Since learning how to use them, I've found tons of uses where I would previously have to dump data into a table variable.
Or, you can just use the query as is.
--J
May 25, 2010 at 12:42 pm
So the CTE doesn't need to have the entire query on there correct? Only the table(s) that are needed to establish the date range?
The query is somewhat lengthy. In my naivety I figured the answer would lie in the WHERE clause and so I didn't send the entire query, just a snippit... If I send the query in it's entirety could you apply this logic to it so I can make sense of it and learn from it?
May 25, 2010 at 12:48 pm
The CTE is part of the query, and always belongs at the beginning (no matter how big the query).
It is the logical equivilent of creating a table variable:
DECLARE @indaterange TABLE (
customerID INT,
categoryID INT
)
INSERT INTO @indaterange
SELECT customerID, categoryID from contactcategory WHERE (dbo.dateonly(contactOn) between DATEADD(MONTH, -6, dbo.dateonly(GETDATE())) AND dbo.dateonly(GETDATE()))
Except if you used this method, you would reference the table as @indaterange rather than indaterange.
To simplify it, think of it this way: You're creating a temporary table in memory. The syntax is "WITH <temp_table_name> AS ( <select query> )", which you immediately follow with a query. In the immediately following query, you can reference <temp_table_name> as though it were a real table.
Hope that helps.
--J
May 25, 2010 at 2:47 pm
Extremely useful tool! Thank you!!
The complete code I have for this query so far is:
DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate = DATEADD(MONTH, -6, GETDATE()), @EndDate = GETDATE()
SELECT MAX(Customer.CustomerID) AS CustomerID, MAX(Customer.Name) AS Name, MAX(Customer.Number) AS Number, Staff.Username, ContactCategory.Text, CustomerContactItem.CreatedOn, MAX(ContactItemCustomerView.AffiliateFullName)
AS AffiliateFullName, Staff.StaffID, ContactCategory.ContactCategoryID, CustomerContactItem.ContactOn
FROM Customer INNER JOIN
Staff ON Customer.SalesRepStaffID = Staff.StaffID INNER JOIN
ContactItemCustomerView ON Customer.CustomerID = ContactItemCustomerView.CustomerID INNER JOIN
Affiliate ON ContactItemCustomerView.AffiliateID = Affiliate.AffiliateID FULL OUTER JOIN
ContactCategory INNER JOIN
CustomerContactItem ON ContactCategory.ContactCategoryID = CustomerContactItem.CategoryID ON
ContactItemCustomerView.CustomerContactItemID = CustomerContactItem.CustomerContactItemID AND
Staff.StaffID = CustomerContactItem.SalesRepStaffID
WHERE (ContactCategory.ContactCategoryID IN ('21','22','24')) AND (dbo.dateonly(CustomerContactItem.ContactOn) BETWEEN dbo.dateonly(@StartDate) AND
dbo.dateonly(@EndDate)) AND (Staff.StaffID = '183')
GROUP BY ContactItemCustomerView.CustomerContactItemID, Staff.Username, ContactCategory.Text, ContactCategory.Description,
CAST(CustomerContactItem.Notes AS varchar(MAX)), CustomerContactItem.CreatedOn, Staff.StaffID, ContactCategory.ContactCategoryID,
CustomerContactItem.ContactOn, Affiliate.AffiliateID, Affiliate.HowDidWeFind
ORDER BY Staff.StaffID, CustomerID
Now, I need to figure out how to apply the WITH statement to this (I would replace the DECLARATION up top, but with what I'm not sure). If you can help me that would be terrific! Thank you again!
May 25, 2010 at 3:04 pm
iklektic (5/25/2010)
Extremely useful tool! Thank you!!The complete code I have for this query so far is:
DECLARE @StartDate datetime, @EndDate datetimeSELECT @StartDate = DATEADD(MONTH, -6, GETDATE()), @EndDate = GETDATE()
SELECT MAX(Customer.CustomerID) AS CustomerID, MAX(Customer.Name) AS Name, MAX(Customer.Number) AS Number, Staff.Username, ContactCategory.Text, CustomerContactItem.CreatedOn, MAX(ContactItemCustomerView.AffiliateFullName)
AS AffiliateFullName, Staff.StaffID, ContactCategory.ContactCategoryID, CustomerContactItem.ContactOn
FROM Customer INNER JOIN
Staff ON Customer.SalesRepStaffID = Staff.StaffID INNER JOIN
ContactItemCustomerView ON Customer.CustomerID = ContactItemCustomerView.CustomerID INNER JOIN
Affiliate ON ContactItemCustomerView.AffiliateID = Affiliate.AffiliateID FULL OUTER JOIN
ContactCategory INNER JOIN
CustomerContactItem ON ContactCategory.ContactCategoryID = CustomerContactItem.CategoryID ON
ContactItemCustomerView.CustomerContactItemID = CustomerContactItem.CustomerContactItemID AND
Staff.StaffID = CustomerContactItem.SalesRepStaffID
WHERE (ContactCategory.ContactCategoryID IN ('21','22','24')) AND (dbo.dateonly(CustomerContactItem.ContactOn) BETWEEN dbo.dateonly(@StartDate) AND
dbo.dateonly(@EndDate)) AND (Staff.StaffID = '183')
GROUP BY ContactItemCustomerView.CustomerContactItemID, Staff.Username, ContactCategory.Text, ContactCategory.Description,
CAST(CustomerContactItem.Notes AS varchar(MAX)), CustomerContactItem.CreatedOn, Staff.StaffID, ContactCategory.ContactCategoryID,
CustomerContactItem.ContactOn, Affiliate.AffiliateID, Affiliate.HowDidWeFind
ORDER BY Staff.StaffID, CustomerID
Now, I need to figure out how to apply the WITH statement to this (I would replace the DECLARATION up top, but with what I'm not sure). If you can help me that would be terrific! Thank you again!
That is quite the beast, but I've seen much worse. What you'd end up doing is putting most of the query into the CTE:
DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate = DATEADD(MONTH, -6, GETDATE()), @EndDate = GETDATE();
WITH indaterange AS (
SELECT
MAX(Customer.CustomerID) AS CustomerID, MAX(Customer.Name) AS Name, MAX(Customer.Number) AS Number, Staff.Username, ContactCategory.Text,
CustomerContactItem.CreatedOn, MAX(ContactItemCustomerView.AffiliateFullName) AS AffiliateFullName, Staff.StaffID, ContactCategory.ContactCategoryID,
CustomerContactItem.ContactOn
FROM Customer INNER JOIN
Staff ON Customer.SalesRepStaffID = Staff.StaffID INNER JOIN
ContactItemCustomerView ON Customer.CustomerID = ContactItemCustomerView.CustomerID INNER JOIN
Affiliate ON ContactItemCustomerView.AffiliateID = Affiliate.AffiliateID FULL OUTER JOIN
ContactCategory INNER JOIN
CustomerContactItem ON ContactCategory.ContactCategoryID = CustomerContactItem.CategoryID ON
ContactItemCustomerView.CustomerContactItemID = CustomerContactItem.CustomerContactItemID AND
Staff.StaffID = CustomerContactItem.SalesRepStaffID
WHERE (dbo.dateonly(CustomerContactItem.ContactOn) BETWEEN dbo.dateonly(@StartDate) AND
dbo.dateonly(@EndDate)) AND (Staff.StaffID = '183')
GROUP BY ContactItemCustomerView.CustomerContactItemID, Staff.Username, ContactCategory.Text, ContactCategory.Description,
CAST(CustomerContactItem.Notes AS varchar(MAX)), CustomerContactItem.CreatedOn, Staff.StaffID, ContactCategory.ContactCategoryID,
CustomerContactItem.ContactOn, Affiliate.AffiliateID, Affiliate.HowDidWeFind
)
SELECT
*
FROM
(SELECT IDR.*,
(SELECT COUNT(*) FROM indaterange G1 where G1.CustomerID = IDR.CustomerID AND ContactCategoryID IN ('21', '22')) AS Group1,
(SELECT COUNT(*) FROM indaterange G2 where G2.CustomerID = IDR.CustomerID AND ContactCategoryID = '24')) AS Group2
FROM indaterange IDR) A
WHERE (Group1 > 0 AND Group2 = 0) OR (Group1 = 0 AND Group2 > 0)
This assumes that CustomerID is what unifies the groups, which may not be true. You may need to add a compare for each of your group by expressions in the WHERE statement after G1 and G2.
Think of the indaterange CTE as kind of an inline view that we reference several times through the main query.
Notice that SQL Server also requires a semicolon (;) before the WITH statement.
Hope this works for you!
--J
May 25, 2010 at 3:28 pm
Thank you so much for your help! I got some good results from this query. I really appreciate your help and explanations. I have learned a lot!
May 26, 2010 at 5:28 am
Great. I'm glad I could help.
--J
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply