Retrieving distinct values and a count on duplicates in SQL

Question

We have a table, lets call it "source-table", from where I retrieve these columns like this;

ANr, TNr, Type, IDate, EDate
1132173, 113615, Bogus, 2017-09-11 13:01:00, 2017-09-13 14:10:00
1132145, 184210, Triss, 2017-09-11 13:05:00, 2017-09-13 14:10:00
1131828, 259858, Bogus, 2017-09-11 13:11:00, 2017-09-13 14:10:00
1131844, 259858, Bogus, 2017-09-11 13:11:00, 2017-09-13 14:10:00

The above result is going to a new table called "export-table" and I then want to do a select from a third table, "info-table" but only with those just inserted in "export-table" and with a Count on how many duplicated entries if any (besides Anr which always is unique)

With the select mention above I want the following result:

Customernr, ANr, IDate, Type, Amount
703524, 1132173,2017-09-11 13:01:00, Bogus, 1
756899, 1132145,2017-09-11 13:05:00, Triss, 1
356658, 1131828,2017-09-11 13:11:00, Bogus, 2

Customernr comes from the "info-table", which has ANr as unique key.

As you can see the last two rows from "source-table" is identical besides Anr, but I need Anr later to get correct values from "info-table". It might work without Anr, if I can do a search on "info-table" with both Tnr and IDate, but I'll get duplicate entries there as well so not sure if that helps.

If someone wonder why the "export-table", then it is a kind of fail-safe so I wont export same stuff more than once.

I've been searching but since it is a bit complex question I haven't found a complete solution or any good hints.

You want Customernr in the result, but I can't find that info in your sample data. — jarlh, Sep 13 '17 at 12:46
Customernr comes from the "info-table" wich I didnt specify in example. I should add that if it can be solved in another way, then it might be enough with just Anr and EDate in the "export-table". — Tomas H, Sep 13 '17 at 13:19

score 0 · Accepted Answer · answered Sep 13 '17 at 12:52

how you did not put the customer, the best I can help you is

DECLARE @t table 
(
    ANr int
    ,TNr int 
    , Type varchar(10) 
    , IDate datetime 
    , EDate Datetime
)

insert @t (ANr, TNr, Type, IDate, EDate) values 
(1132173, 113615, 'Bogus', '2017-09-11 13:01:00', '2017-09-13 14:10:00')
,(1132145, 184210, 'Triss', '2017-09-11 13:05:00', '2017-09-13 14:10:00')
,(1131828, 259858, 'Bogus', '2017-09-11 13:11:00', '2017-09-13 14:10:00')
,(1131844, 259858, 'Bogus', '2017-09-11 13:11:00', '2017-09-13 14:10:00')


Select Distinct 
TNr
,IDate
,Type
,COUNT(1) OVER (PARTITION BY TNr, Type, IDATE)
from @t

Result

TNr         IDate                   Type       
----------- ----------------------- ---------- -----------
113615      2017-09-11 13:01:00.000 Bogus      1
184210      2017-09-11 13:05:00.000 Triss      1
259858      2017-09-11 13:11:00.000 Bogus      2

score 0 · Answer 2 · answered Sep 13 '17 at 12:53

You can query as below:

Select top (1) with ties ANr, Idate, [Type], Count(*) over(partition by TNr, [Type], Idate) from
    #sourcetable 
    order by row_number() over(partition by TNr, [Type], Idate order by Anr)

But not sure how you got Customernr

Output as below:

+---------+-------------------------+-------+--------+
|   ANr   |          Idate          | Type  | Amount |
+---------+-------------------------+-------+--------+
| 1132173 | 2017-09-11 13:01:00.000 | Bogus |      1 |
| 1132145 | 2017-09-11 13:05:00.000 | Triss |      1 |
| 1131828 | 2017-09-11 13:11:00.000 | Bogus |      2 |
+---------+-------------------------+-------+--------+

You can use sub query for getting row_number as below

Select * from (
    Select ANr, Idate, [Type], Amount = Count(*) over(partition by TNr, [Type], Idate) 
        ,RowN = row_number() over(partition by TNr, [Type], Idate order by Anr)
    from #sourcetable ) a
Where a.RowN = 1

score 0 · Answer 3 · answered Sep 13 '17 at 12:54

DECLARE @t TABLE(ANr int, TNr int, Type NVARCHAR(100), IDate DATETIME, EDate DATETIME);

INSERT INTO @t VALUES
(1132173, 113615, 'Bogus', '2017-09-11 13:01:00', '2017-09-13 14:10:00')
,(1132145, 184210, 'Triss', '2017-09-11 13:05:00', '2017-09-13 14:10:00')
,(1131828, 259858, 'Bogus', '2017-09-11 13:11:00', '2017-09-13 14:10:00')
,(1131844, 259858, 'Bogus', '2017-09-11 13:11:00', '2017-09-13 14:10:00');

SELECT MIN(ANr) ANr, TNr, IDate, EDate, COUNT(*) AS Anz
  FROM @t
  GROUP BY TNr, IDate, EDate

Retrieving distinct values and a count on duplicates in SQL

3 Answers3