Not convinced my query is correct or is there a better way to do it?

Transact-SQL
1

Afternoon all,

I have a query here and to be perfectly honest it works (produces results) but i'm not sure it works properly...
Basically I have 2 tables, Customers which stores customer details & Customer_Contact which stores when customers are contacted with some notes.

CREATE TABLE [dbo].[Customers](
	[C_ID] [int] NOT NULL,
	[C_Name] [nvarchar](20) NOT NULL,
	[C_Postcode] [nvarchar](20) NOT NULL,
        [C_Status] [nvarchar](20) NOT NULL,
        [C_Branch] [nvarchar](20) NOT NULL,
        [C_Type] [nvarchar](20) NOT NULL,
	);
	
INSERT INTO Customers(C_ID, C_Name, C_Postcode, C_Status, C_Branch, C_Type)
	VALUES
	('1000', 'John', 'AA1 1AA', 'Live', 'London', 'Sales'),
	('1001', 'Jane', 'AA1 1AA', 'Live', 'London', 'Sales'),
	('1002', 'Bob', 'AA1 1AA', 'Live', 'London', 'Sales')
    ;

CREATE TABLE [dbo].[Customer_Contact](
	[CC_ID] [int] NOT NULL,
    [CC_CustID] [int] NOT NULL,
	[CC_Notes] [nvarchar](20) NOT NULL,
	[CC_Date] [datetime2](3) NOT NULL
	);
	
INSERT INTO Customer_Contact(CC_CustID, CC_Notes, CC_Date)
	VALUES
	('1000', 'Blar blar', '2023-02-01'),
	('1000', 'Blar blar', '2022-11-21'),
	('1000', 'Blar blar', '2022-11-18'),
	('1001', 'Blar blar', '2022-10-22'),
	('1001', 'Blar blar', '2022-10-15'),
	('1002', 'Blar blar', '2022-10-15')
    ;

I'm looking to get any customers that havent been contacted in the last month & output they're data and the last date they were contacted. The Query I have is:

SELECT C_ID, C_Name, C_Postcode, C_Status, C_Branch, C_Type, MIN(CONVERT(date,CC_Date)) AS C_DateContacted
FROM Customers
JOIN Customer_Contact On C_ID = CC_CustID
WHERE C_ID NOT IN 
    (
     SELECT CC_ID FROM Customer_Contact 
     WHERE CC_Date > DATEADD(Month, -1, CONVERT(date,getdate()))
	 )
     AND (C_Status = 'Live')
GROUP BY C_ID, C_Name, C_Postcode, C_Status, C_Branch, C_Type
ORDER BY C_DateContacted DESC

Can anyone see any glaring errors or even a better way to achieve it?

Many thanks

Dave

2

hi

your script is giving errors

please check it once .. please test it and post it

thank you

image
image1058×449 17.7 KB

3

Oops, there was an extra comma in there. I've edited it...

4 
SELECT 
      C_ID
      , CC_CustID 
	  , C_Name
	  , C_Postcode
	  , C_Status
	  , C_Branch
	  , C_Type
	  , max(CC_Date) as CC_Date
FROM 
    Customer_Contact a 
	    JOIN
	[Customers] b
	      ON a.C_ID = b.C_ID	      
GROUP BY 
	CC_CustID 
HAVING 
    max(CC_Date) < = cast( getdate()-31 as date)
5 

;WITH cte_last_cust_contacts AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY CC_CustID ORDER BY CC_Date DESC) AS row_num
    FROM dbo.Customer_Contact
)  
SELECT cl.CC_CustID, cl.CC_Date, cl.CC_Notes, c.*
FROM cte_last_cust_contacts cl
/*Edit: added c.status check condition based on original code*/
INNER JOIN dbo.Customers c ON c.C_ID = cl.CC_CustID AND c.status = 'Live'
/*Edit: moved CC_Date check to outside the cte*/
WHERE cl.row_num = 1 AND cl.CC_Date < DATEADD(MONTH, -1, CONVERT(date,getdate()))
)
6

If a customer has not been contacted - do you want that customer to show up without any contact information? Or do you only want customers who have been contacted at some point in time - but not contacted within the last month?

SELECT c.C_ID
     , c.C_Name
     , c.C_Postcode
     , c.C_Status
     , c.C_Branch
     , c.C_Type
     , lc.*
  FROM dbo.Customers                         c
 CROSS APPLY (SELECT TOP (1)
                     *
                FROM dbo.Customer_Contact   cc
               WHERE cc.CC_ID = c.C_ID
               ORDER BY
                     cc.CC_Date DESC)       lc
 WHERE c.C_Status = 'Live'
   AND lc.CC_Date < DATEADD(month, -1, CAST(getdate() AS date))

If you want all customers even if they don't have any contact information - then change it to an OUTER APPLY and change the where clause to:

 WHERE c.C_Status = 'Live'
   AND (lc.CC_Date IS NULL OR lc.CC_Date < DATEADD(month, -1, CAST(getdate() AS date)))

@ScottPletcher - I don't think your solution will work correctly. If a customer has contact information prior to a month ago and also within the last month then your CTE will return that customer because row_num will be 1 for the latest contact more than 1 month ago. I think you need to move the date check from the CTE to the outer query and also include the check for C_Status:

 WHERE c.C_Status = 'Active'
   AND cl.row_num = 1 
   AND cl.CC_Date < DATEADD(MONTH, -1, CONVERT(date,getdate()))
7

@harishgg1

Did you actually test your code? I'm comparing the GROUP BY list of columns to the SELECT list columns and, unless you've found some form of magic (which is always a possibility), I'm thinking it's going to give you an error about columns in the SELECT list that aren't in the GROUP BY.

8

Hi Jeff

Yes you are right

Its going to give error

here is the corrected code

SELECT 
      C_ID
      , CC_CustID 
	  , C_Name
	  , C_Postcode
	  , C_Status
	  , C_Branch
	  , C_Type
	  , max(CC_Date) as CC_Date
FROM 
    Customer_Contact a 
	    JOIN
	[Customers] b
	      ON a.C_ID = b.C_ID	      
GROUP BY 
	 C_ID
	  , CC_CustID 
	  , C_Name
	  , C_Postcode
	  , C_Status
	  , C_Branch
	  , C_Type
HAVING 
    max(CC_Date) < = cast( getdate()-31 as date)