1

I have a table with a definition like this:

CREATE TABLE [dbo].[Contact]
(
    ClientNumber [numeric] (20, 0) NULL,
    ContactDate [Date] NULL,
    Name [text] NULL,
    AdditionalInformation [text] NULL
)

This table is filled with information for a single month. Each client may have multiple entries for a single day. Meaning there might be entries like this:

42  2015-12-09  Felix       called today
42  2015-12-09  Fexclicx    ...

My goal is to get all client results from the table with the condition that there should be only one entry a day per client number. I do not care which of the (here) two entries is displayed, so something like top 1.

So far I tried to achieve this with a distinct:

select distinct ClientNumber, ContactDate, * from Contact

Unfortunately this does not limit the results to show a client entry for a specific date only once. They still all do appear. Also the problem is that there are no id's in this table so I cannot work with that.

How could I achieve getting all entries but only one entry for one client per day?

  • Is Client Number unique, in your sample data the same client number is assigned to two different Names. – Pixelated Mar 11 '16 at 15:41
1

To create sample schema and data

 IF OBJECT_ID('tempdb..#Contact', 'U') IS NOT NULL
        DROP TABLE #Contact ;

    CREATE 
    TABLE   #Contact
            (
            ClientNumber [numeric] (20, 0) NULL,
            ContactDate [Date] NULL,
            Name [text] NULL,
            AdditionalInformation [text] NULL
            );

    INSERT
    INTO    #Contact
            (
            ClientNumber
            ,ContactDate
            ,Name
            ,AdditionalInformation
            )

    VALUES  (111,'2016-03-11','A','A'),       
            (111,'2016-03-11','B','B'),
            (112,'2016-03-11','C','C'),       
            (112,'2016-03-12','A','A'),
            (112,'2016-03-13','A','A'),       
            (112,'2016-03-13','B','B'),
            (112,'2016-03-14','A','A'),
            (113,'2016-03-14','B','B'),
            (113,'2016-03-14','C','C');

Actual query

;WITH   CTERowNumber
AS      (
        SELECT  ROW_NUMBER() OVER (PARTITION BY ClientNumber, ContactDate ORDER BY ClientNumber DESC) AS RowNumber
               ,ClientNumber
               ,ContactDate
               ,Name
               ,AdditionalInformation
        FROM    #Contact
        )

SELECT  c.RowNumber
       ,c.ClientNumber
       ,c.ContactDate
       ,c.Name
       ,c.AdditionalInformation
FROM    CTERowNumber c
WHERE   c.RowNumber = 1 ;

To return

RowNumber   ClientNumber    ContactDate Name    AdditionalInformation
1           111             2016-03-11  A       A
1           112             2016-03-11  C       C
1           112             2016-03-12  A       A
1           112             2016-03-13  A       A
1           112             2016-03-14  A       A
1           113             2016-03-14  B       B

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.