T-SQL Query against previous queries results
I’m having a hard time searching the internet for this, so thought I would ask the T-SQL question here. I have a table called CUST. CUST contains customer entrydate, name, company, address, email, among other things. I wrote a simple query to find new customers based off entrydate;
Select * from CUST WHERE ENTRYDATE between '2012-08-01' and '2012-08-30'
This works great, except I found a problem. When our webstore creates new orders, if the customer details don’t match EXACTLY, a new customer is created. That being the case, I want to take my original results, and trim the results set if I have more than one occurrence of cust.firstname + cust.lastname + cust.company. I can write these in individual queries, just unsure how to do this in a single sql script.
I thought about doing a join on name back to the table, and while the join doesn’t error, I don’t know how to count the occurrences, I was thinking of counting customer numbers.
My join looks like this (stripped out the group by and column selection to make it easier to read;
from CUST Right Outer Join ( select * from CUST WHERE ENTRYDATE between '2012-08-01' and '2012-08-30' AND LTRIM(RTRIM(Firstname + Lastname + Company)) <> '' Group By * ) as newcs on LTRIM(RTRIM(CUST.Firstname + CUST.Lastname + CUST.Company)) = LTRIM(RTRIM(newcs.Firstname + newcs.Lastname + newcs.Company))
3 Solutions collect form web for “T-SQL Query against previous queries results”
Distinct records in the current time period that were not entered prior to this reporting period:
SELECT DISTINCT c1.* FROM @CUST c1 WHERE c1.ENTRYDATE BETWEEN '2012-08-01' AND '2012-08-30' AND NOT EXISTS (SELECT 1 FROM @CUST c2 WHERE c2.ENTRYDATE < '2012-08-01' AND LTRIM(RTRIM(c1.Firstname + c1.Lastname + c1.Company)) = LTRIM(RTRIM(c2.Firstname + c2.Lastname + c2.Company)) )
If you are looking to delete the duplicate records, you can use the following script:
DELETE FROM Customer WHERE PKCol IN ( SELECT PK_Column FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY FirstName, LastName, Company ORDER BY EntryDate) AS RN, PK_Column FROM Customers ) A WHERE A.RN > 1 )
This query results only newest customers with distinct (trimmed) FirstName+LastName+Company
SELECT FirstName,LastName,Company,EntryDate FROM( SELECT *, R=ROW_NUMBER() OVER (PARTITION BY ID ORDER BY EntryDate DESC) FROM( SELECT FirstName,LastName,Company , EntryDate, ID= LTRIM(RTRIM(FirstName)) + LTRIM(RTRIM(LastName)) + LTRIM(RTRIM(Company)) FROM CUST WHERE EntryDate BETWEEN '2012-08-01' and '2012-08-30' ) AS a ) AS b WHERE R=1
If you need to take oldest ones, remove the
DESC part after
ORDER BY Clause