CODEWITHSUNDEEP
sql-servert-sql
SideFX
SideFX

Reputation: 839

Select first group of 10 for each day (removing duplicate records)

I have a table called BillingInformation with the following columns: id, clientId, type, order, value, timestamp

id is an artificial key For each clientId on a particular date there will be 10 types e.g.

clientId   type              order    timestamp
1          Outstanding AR    0        2012-09-24 10:45:28.557
1          Est. Days In AR   1        2012-09-24 10:45:28.580
1          ...               2        2012-09-24 10.45.28.603
1          ...               3        2012-09-24 10.45.28.620  
1          ...               4        2012-09-24 10.45.28.630  
1          ...               5        2012-09-24 10.45.28.653
1          ...               6        2012-09-24 10.45.28.697
1          ...               7        2012-09-24 10.45.28.717
1          ...               8        2012-09-24 10.45.28.727
1          ...               9        2012-09-24 10.45.28.727
2          Outstanding AR    0        ...
2          Est. Days In AR   1        ...
(cont. 8 more rows for client 2)

My issue is that I have some clientIds where the 10 entries are in the database more than once and I just want to select just 10 (where order = 0 through 9) per client.

Here is my situation now:

clientId   type              order    timestamp
1          Outstanding AR    0        2012-09-24 10:45:28.557
1          Est. Days In AR   1        2012-09-24 10:45:28.580
1          ...               2        2012-09-24 10.45.28.603
1          ...               3        2012-09-24 10.45.28.620  
1          ...               4        2012-09-24 10.45.28.630  
1          ...               5        2012-09-24 10.45.28.653
1          ...               6        2012-09-24 10.45.28.697
1          ...               7        2012-09-24 10.45.28.717
1          ...               8        2012-09-24 10.45.28.727
1          ...               9        2012-09-24 10.45.28.727
1          Outstanding AR    0        2012-09-24 10:45:28.557
1          Est. Days In AR   1        2012-09-24 10:45:28.580
1          ...               2        2012-09-24 10.45.28.603
1          ...               3        2012-09-24 10.45.28.620  
1          ...               4        2012-09-24 10.45.28.630  
1          ...               5        2012-09-24 10.45.28.653
1          ...               6        2012-09-24 10.45.28.697
1          ...               7        2012-09-24 10.45.28.717
1          ...               8        2012-09-24 10.45.28.727
1          ...               9        2012-09-24 10.45.28.727
2          Outstanding AR    0        ...
2          Est. Days In AR   1        ...

(cont. 8 more rows for client 2) (cont. 10 rows for client 3) (and so on...)

No matter how many times the records 0 through 9 are repeated for client 1 for the day of 2012-09-14 (disregarding hours minutes seconds and milliseconds), I just want to select just one group of 10 along with the other records. So if any client has duplicated sets of 10 for the same month day and year, I just want one group of 10 for that month day and year.

Can anyone help me with this?

Upvotes: 0

Views: 583

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

select id, clientId, type, [order], value, timestamp
from
(
  select *, row_number() over (partition by clientId, [order],
                                            datediff(d,0,timestamp)
                               order by timestamp desc) rn
  from BillingInformation
) X
where rn=1

Upvotes: 1

GilM
GilM

Reputation: 3771

How about something like:

WITH CTE AS (
SELECT id, ROW_NUMBER() OVER (PARTITION BY ClientId, CAST([timestamp] AS date), [order] ORDER BY [timestamp]) as rn
FROM BillingInformation
)
SELECT b.*
FROM BillingInformation b
JOIN CTE c on c.id = b.id and c.rn=1

Upvotes: 1

Related Questions