How do I get duplicated ips when there is more than 1 reservation that is not duplicated-CodePudding

I am attempting to get duplicated ip_number records when there are different reservation_id.

DECLARE @Reservations TABLE (ip_number INT, reservation_id VARCHAR(16), name VARCHAR(16),quote_date DATETIME, arrival_date DATETIME, deposit_amount DECIMAL(16,2)

INSERT INTO @Reservations (ip_number, reservation_id, name, quote_date, arrival_date, deposit_amount)
VALUES 
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132518,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','64.20'),
  (50053177,21132518,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','64.20'),
  (50053177,21132714,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','32.10'),
  (50053161,21131464,'Amy','2022-12-27 00:00:00.000','2022-12-28 07:00:00.000','52.31'),
  (50053151,21131445,'Chung','2022-12-27 00:00:00.000','2022-12-28 07:00:00.000','119.04'),
  (50053151,21131445,'Chung','2022-12-27 00:00:00.000','2022-12-28 07:00:00.000','119.04'),
  (50053151,21131445,'Chung','2022-12-27 00:00:00.000','2022-12-28 07:00:00.000','119.04'),
  (50039951,21125684,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88'),
  (50039951,21125683,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88'),
  (50039951,21125683,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88'),
  (50039951,21125682,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88'),
  (50039951,21125682,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88');

I am currently trying to use ROW_NUMBER():

SELECT ROW_NUMBER() OVER (ORDER BY rth.ip_number DESC) AS row, 
       reservation_id,
       name,
       ip_number,
       quote_date,
       arrival_date,
       deposit_amount
FROM r_order_reservation
WHERE quote_date > '2022-12-01';

This returns the following:

row reservation_id  name        quote_date              arrival_date             deposit_amount
1801    21132003    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  288.90
1802    21132003    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  288.90
1803    21132003    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  288.90
1804    21132003    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  288.90
1805    21132003    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  288.90
1806    21132003    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  288.90
1807    21132518    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  64.20
1808    21132518    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  64.20
1809    21132714    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  32.10
1810    21131464    Amy         2022-12-27 00:00:00.000 2022-12-28 07:00:00.000  52.31
1811    21131445    Chung       2022-12-27 00:00:00.000 2022-12-28 07:00:00.000  119.04
1812    21131445    Chung       2022-12-27 00:00:00.000 2022-12-28 07:00:00.000  119.04
1813    21131445    Chung       2022-12-27 00:00:00.000 2022-12-28 07:00:00.000  119.04
1814    21125684    Jennifer    2022-12-27 00:00:00.000 2022-12-31 07:00:00.000  103.88
1815    21125683    Jennifer    2022-12-27 00:00:00.000 2022-12-30 07:00:00.000  103.88
1816    21125683    Jennifer    2022-12-27 00:00:00.000 2022-12-30 07:00:00.000  103.88
1817    21125682    Jennifer    2022-12-27 00:00:00.000 2022-12-29 07:00:00.000  103.88
1818    21125682    Jennifer    2022-12-27 00:00:00.000 2022-12-29 07:00:00.000  103.88

Christine has 3 reservation_id 21132003,21132518 and 21132714, but its showing the duplicated records for reservation_id 21132003 and 21132518.

Amy has only 1 reservation_id 21131464.

Chung has multiple reservation_id 21131445 that are the same, but do not include any other reservation_id.

Jennifer has 3 different reservation_id 21125684,21125683 and 21125682 but has a duplicated reservation_id of 21125682.

I am trying to pull in records that have different reservation_id but have the same name,quote_date,arrival_date and deposit_amountwhen there is more than 1 reservation_id made by the same ip_number on a given quote_date.

Expected Results:

row reservation_id  name        quote_date              arrival_date             deposit_amount
1801    21132003    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  288.90
1802    21132518    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  64.20
1803    21132714    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  32.10
1804    21125684    Jennifer    2022-12-27 00:00:00.000 2022-12-31 07:00:00.000  103.88
1815    21125683    Jennifer    2022-12-27 00:00:00.000 2022-12-30 07:00:00.000  103.88
1817    21125682    Jennifer    2022-12-27 00:00:00.000 2022-12-29 07:00:00.000  103.88

CodePudding user response：

Is that what you expect to be like ?

DECLARE @Reservations TABLE (ip_number INT, reservation_id VARCHAR(16), name VARCHAR(16),quote_date DATETIME, arrival_date DATETIME, deposit_amount DECIMAL(16,2))

INSERT INTO @Reservations (ip_number, reservation_id, name, quote_date, arrival_date, deposit_amount)
VALUES 
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132518,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','64.20'),
  (50053177,21132518,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','64.20'),
  (50053177,21132714,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','32.10'),
  (50053161,21131464,'Amy','2022-12-27 00:00:00.000','2022-12-28 07:00:00.000','52.31'),
  (50053151,21131445,'Chung','2022-12-27 00:00:00.000','2022-12-28 07:00:00.000','119.04'),
  (50053151,21131445,'Chung','2022-12-27 00:00:00.000','2022-12-28 07:00:00.000','119.04'),
  (50053151,21131445,'Chung','2022-12-27 00:00:00.000','2022-12-28 07:00:00.000','119.04'),
  (50039951,21125684,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88'),
  (50039951,21125683,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88'),
  (50039951,21125683,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88'),
  (50039951,21125682,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88'),
  (50039951,21125682,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88');

WITH TMP AS (
    SELECT ROW_NUMBER() OVER (ORDER BY ip_number DESC) AS row,
        reservation_id,
        name,
        ip_number,
        quote_date,
        arrival_date,
        deposit_amount
    FROM @Reservations
    WHERE quote_date > '2022-12-01'
    GROUP BY reservation_id, name, ip_number, quote_date, arrival_date, deposit_amount
)
SELECT *
FROM TMP T1
WHERE EXISTS (
    SELECT 1
    FROM TMP T2
    WHERE T1.name = T2.name
        AND T1.ip_number = T2.ip_number
        AND T1.quote_date = T2.quote_date
        AND T1.arrival_date = T2.arrival_date
    GROUP BY T2.name, T2.ip_number, T2.quote_date, T2.arrival_date
    HAVING COUNT(*) > 1
)

CodePudding user response：

I'm not sure how a row_number helps you de-dupe here or if this data is your actual table or the result of a (potentially flawed?) query - but perhaps this is what you are after?

First identify the those groups with more than one reservation_id and then join this with the data set and selecting distinct rows:

with d as (
    select name,quote_date,arrival_date, Count(distinct reservation_id) dupe
    from Reservations 
    group by name,quote_date,arrival_date
)
select distinct *
from Reservations r
where exists (
    select *
    from d
    where d.name = r.name
    and d.quote_date = r.quote_date
    and d.arrival_date = r.arrival_date
    and d.dupe > 1
);

Demo FIDDLE