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.

  • Delete All / Bulk Insert
  • Trying to get the sum of distinct values for distinct files for distinct software versions
  • How to get date range start date , to date from one date in SQL Server
  • c# sql and loop while
  • jQuery Autocomplete remote datasource and dynamic rows
  • Sparx Enterprise Architect for SQL Server modeling?
  • 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))

    Any suggestions?

    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:

    FROM @CUST c1
    WHERE c1.ENTRYDATE BETWEEN '2012-08-01' AND '2012-08-30'
        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
        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 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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.