Filtering Duplicate Records

[earljgray]

I appreciate the fact that 'Duplicate Records' is a hairy ol' chestnut, but the issue we're working on can't be resolved with a canned code snippet - unfortunately.

Before looking at the table, here are the business rules:
1. A unit can be entered into an inventory table, A stock number and upload date are entered.
2. If the unit is not sold in an appropriate length of time, it is removed from sale, but the inventory record (appropriately flagged) stays in the table.
3. The unit may be be 're-uploaded' to the table. The unit'sserial number stays the same; the stock number and upload date are different. There are now two units in the table with the same serial number; different stock numbers and upload dates.
4. We invoice on the basis of the unit received (interestingly, we bill the customer for the units we inventory - but that's another story).
5. We invoice monthly. Here's the rule:
-- a. If the unit is uploaded the month of invoicing, and there are no prior uploads of that unit, the unit is billed.
-- b. If the unit is uploaded the month of invoicing, and there was a prior upload of the unit, the unit is NOT billed. It is assumed it had been invoiced previously.
-- c. If the unit is uploaded twice during the month of invoicing, and there was no prior upload of the unit, the unit is billed - but only once.
-- d. If the unit is uploaded twice during the month of invoicing, and there was a prior upload of the unit, the unit is NOT billed.

Those are the essentials. The problem comes in when you try and identify ONLY those units that were uploaded during the month and were never previously uploaded AND, should there be multiple uploads duiring the month of that unit only 1 record seeps through for billing purposes. We've been successful in getting the records drilled down to the point where all we're left with is records initially uploaded in the month, but then again uploaded during that same month. They come through multiple times. So, instead of:

Upload Date Serial Number
----------- -------------
9/1/2006 1233
9/2/2006 3333
9/3/2006 4444

we're getting something like this:

Upload Date Serial Number
----------- -------------
9/1/2006 1233
9/2/2006 3333
9/3/2006 4444
9/9/2006 3333
9/20/2006 3333
9/28/2006 4444

Note that only Serial No. 1233 was uploaded once during the month. We should be invoicing on 3 units - not 6.

Here's the current code: (Oracle 9.2)
SELECT a.product_id,
a.location_id,
a.stock_number,
a.upload_date,
a.serial_number
FROM prod.inv_products a , prod.inv_products b
where a.client_id = 2
and a.upload_date between to_date('09/01/2006','MM/DD/YYYY') and to_date('10/01/2006','MM/DD/YYYY')
and a.serial_number = b.serial_number
and a.upload_date = b.upload_date
and (a.location_id = 31543 or a.stock_number like '801242%')
and a.serial_number not in
(Select distinct
b.serial_number
FROM prod.inv_products b --, prod.inv_products b
where
a.upload_date between to_date('03/01/2006','MM/DD/YYYY') and to_date('09/01/2006','MM/DD/YYYY')
)
AND NOT EXISTS
(SELECT NULL
FROM inv_products q
WHERE client_id = 2
and q.upload_date between to_date('03/01/2006','MM/DD/YYYY') and to_date('09/01/2006','MM/DD/YYYY')
and a.serial_number = q.serial_number
)
AND EXISTS
(SELECT NULL
FROM inv_products r
WHERE client_id = 2
and a.upload_date between to_date('09/01/2006','MM/DD/YYYY') and to_date('10/01/2006','MM/DD/YYYY')
and a.serial_number = r.serial_number

)
AND a.serial_number in
(SELECT serial_number
FROM inv_products
WHERE
upload_date between to_date('09/01/2006','MM/DD/YYYY') and to_date('10/01/2006','MM/DD/YYYY')
and a.serial_number = inv_products.serial_number
GROUP BY
serial_number
HAserial_numberG
a.serial_number = min(serial_number)
)

The reason there's two date ranges
('09/01/2006','MM/DD/YYYY') and to_date '10/01/2006','MM/DD/YYYY')

and

('03/01/2006','MM/DD/YYYY') and to_date('09/01/2006','MM/DD/YYYY')

is we're arbitrarily setting a 6 month prior window to determine if the unit had been uploaded previously (hence the 3/01/06 time line). The 9-10/01/2006 time is for in-month uploads for invoicing purposes.

I realize it's a long post - but I would certainly appreciate any help you can offer......

Thanks!






Crystal Reports Design/training/Consultation
ecsdata@ecsdata.com

 

[Dagon]

I can't really work out what your SQL is trying to do, but it sounds as if you want something like:

SELECT product_id, 
       location_id, 
       stock_number, 
       upload_date, 
       serial_number
FROM
(SELECT  a.product_id, 
        a.location_id, 
        a.stock_number, 
        a.upload_date, 
        a.serial_number,
        row_number() over (partition by serial_number order by upload_date desc) as rn
  FROM prod.inv_products a 
  where a.client_id = 2 
   and a.upload_date between to_date('09/01/2006','MM/DD/YYYY') and to_date('10/01/2006','MM/DD/YYYY') 
   and (a.location_id = 31543 or a.stock_number like '801242%')
and not exists
(select 1 from prod.inv_products b
where a.serial_number = b.serial_number
and b.upload_date  between to_date('03/01/2006','MM/DD/YYYY') and to_date('09/01/2006','MM/DD/YYYY')))
where rn=1

 

[taupirho]

to get to this

Upload Date Serial Number
----------- -------------
9/1/2006 1233
9/2/2006 3333
9/3/2006 4444

from this

Upload Date Serial Number
----------- -------------
9/1/2006 1233
9/2/2006 3333
9/3/2006 4444
9/9/2006 3333
9/20/2006 3333
9/28/2006 4444

try this

select distinct first_value(dte),serial
over (partition by(to_char(dte,'yyyymm') || serial) order by dte)
from mytable

 

[earljgray]

Dagon:

Tried your model and it worked beautifully!

I appreciate the community's assistance!!

Thanks to all -

Crystal Reports Design/training/Consultation
ecsdata@ecsdata.com