Duplicate Records in SQl Server Table Different year

Question

I am attempted to write a query that find duplicates. The query would look at the Latest 2013 order dates then Match with Latest 2014 orders, in order to find records that exists in BOTH years. My code only gives me 2013 data and not for both 2013 and 2014. So I would like to show the duplicates for both years.

For example Below is sample data

Bill_Member_ID........Lname....................Order_Date  
123..........................Smith...............01/05/2013  
123..........................Smith...............02/15/2014  
123..........................Smith...............02/18/2014  
456..........................Jones...............01/07/2013  
789..........................Brown...............01/05/2013  
789..........................Brown...............02/17/2014  
789..........................Brown...............03/17/2014
992..........................White...............03/15/2013
992..........................White...............01/05/2014

So then I should get a return of

Bill_Member_ID........Lname....................Order_Date    
123..........................Smith...............01/05/2013  
123..........................Smith...............02/18/2014  
789..........................Brown...............01/05/2013  
789..........................Brown...............03/17/2014
992..........................White...............03/15/2013
992..........................White...............01/05/2014

Here is my Code

SELECT
  OMFMC.BILL_MASTER_CUSTOMER_ID,
  OMFMC.BILL_FIRST_NAME,
  OMFMC.BILL_LAST_NAME,
  OMFMC.BILL_LABEL_NAME,
  OMFMC.BILL_PRIMARY_EMAIL_ADDRESS,
  OMFMC.BILL_ADDRESS_1,
  OMFMC.BILL_ADDRESS_2,
  OMFMC.BILL_CITY,
  OMFMC.BILL_STATE,
  OMFMC.BILL_POSTAL_CODE,
  CMI.NATIONAL_LEVEL2,
  MAX(OMFMC.ORDER_DATE),
  OMFMC.ORDER_DATE,
  FT.PAYMENT_AMOUNT,
 SUM(FT.PAYMENT_AMOUNT)as SUM


FROM
  CUSTOMER  CUSTOMER_IN_TRIBUTE_TO  

RIGHT OUTER JOIN ORDER_FND_DETAIL OFD
    ON (CUSTOMER_IN_TRIBUTE_TO.MASTER_CUSTOMER_ID=OFD.IN_TRIBUTE_TO_MAST_CUST 
    and CUSTOMER_IN_TRIBUTE_TO.SUB_CUSTOMER_ID=OFD.IN_TRIBUTE_TO_SUB_CUST)


RIGHT OUTER JOIN ORDER_MBR_FND_MTG_CUS_INFO_VW OMFMC
    ON (OMFMC.ORDER_NO=OFD.ORDER_NO 
    and OMFMC.ORDER_LINE_NO = OFD.ORDER_LINE_NO  )


LEFT OUTER JOIN CUS_CURRENT_MEMBERSHIP_INFO CMI
    ON (CMI.MASTER_CUSTOMER_ID=OMFMC.BILL_MASTER_CUSTOMER_ID 
    and CMI.SUB_CUSTOMER_ID=OMFMC.BILL_SUB_CUSTOMER_ID)  


LEFT OUTER JOIN FAR_TXN FT
    ON (FT.ORDER_NO=OMFMC.ORDER_NO 
    and FT.ORDER_LINE_NO=OMFMC.ORDER_LINE_NO)  


WHERE

OMFMC.ORDER_STATUS_CODE='A' 
AND OMFMC.LINE_STATUS_CODE = 'A'        
AND OMFMC.ORDER_STATUS_CODE = 'A' 
AND OMFMC.LINE_STATUS_CODE = 'A'   
AND OMFMC.BILL_CUSTOMER_CLASS_CODE  Not IN  ( 'TEST_MBR','STAFF' ) 
AND FUND in ('FOSFN' , 'MFUND')
and(DATEPART(year, OMFMC.ORDER_DATE) ='2013')
and OMFMC.BILL_MASTER_CUSTOMER_ID In (

(select OMFMC.BILL_MASTER_CUSTOMER_ID
 From ORDER_MBR_FND_MTG_CUS_INFO_VW OMFMC
 where (DATEPART(year, OMFMC.ORDER_DATE) ='2014'))
 )

GROUP BY
  OMFMC.BILL_LABEL_NAME, 
  OMFMC.BILL_FIRST_NAME,
  OMFMC.BILL_LAST_NAME,
  OMFMC.ORDER_DATE, 
  OMFMC.CAMPAIGN, 
  OMFMC.FUND, 
  OMFMC.PRODUCT_CODE, 
  OMFMC.BILL_MASTER_CUSTOMER_ID, 
  OMFMC.BILL_COMPANY_NAME, 
  OMFMC.BILL_COUNTRY_DESCR, 
  OMFMC.BILL_LAST_FIRST_NAME, 
  OMFMC.ORDER_NO, 
  OMFMC.COMMENTS, 
  year(OMFMC.ORDER_DATE), 
  month(OMFMC.ORDER_DATE), 
  OMFMC.BILL_COUNTRY_CODE, 
  CMI.NATIONAL_LEVEL2, 
  CMI.NATIONAL_SINCE_DATE, 
  CMI.CYCLE_END_DATE, 
  OMFMC.BILL_FORMATTED_DETAIL + OMFMC.BILL_FORMATTED_ADDRESS, 
  OMFMC.BILL_PRIMARY_EMAIL_ADDRESS, 
  OMFMC.BILL_ADDRESS_1, 
  OMFMC.BILL_CITY, 
  OMFMC.BILL_STATE, 
  OMFMC.BILL_POSTAL_CODE, 
  OMFMC.BILL_ADDRESS_2, 
  OMFMC.BILL_ADDRESS_3, 
  OMFMC.BILL_ADDRESS_4, 
  OFD.IN_TRIBUTE_TO_DESCR, 
  CUSTOMER_IN_TRIBUTE_TO.LABEL_NAME, 
  OFD.TRIBUTE_TYPE_CODE, 
  OMFMC.MARKET_CODE, 
  OMFMC.BILL_JOB_TITLE,
  FT.PAYMENT_AMOUNT



HAVING SUM(FT.PAYMENT_AMOUNT)  <  0

Order By BILL_MASTER_CUSTOMER_ID

You say than you should get duplicate record then why in your desired output there are only 2 rows with Smith? Should not you return `Smith...............02/15/2014` as well? — Giorgi Nakeuri, Jan 13 '15 at 15:18
Because the smith record has two 2014 dates, I'm looking to fetch the latest date for 2014. Otherwise the MAX date — Ramatu Victorious Kuyateh, Jan 13 '15 at 15:22

score 2 · Answer 1 · answered Jan 13 '15 at 15:24

If you are doing this in SQL Server, then you can use window functions:

select s.*
from (select s.*,
             min(year(order_date) over (partition by Bill_Member_ID) as minyear,
             max(year(order_date) over (partition by Bill_Member_ID) as maxyear,
             row_number() over (partition by Bill_Member_ID, year(order_date) order by order_date desc) as seqnum
      from sample s
      where year(order_date) in (2013, 2014)
     ) s
where minyear <> maxyear and seqnum = 1;

The subquery gets the minimum and maximum year for each member, as well as assigning a sequential number to the records in each year.

The outer where validates that there are two years (it would be more explicit to write where minyear = 2013 and maxyear = 2014, if you like) and chooses one record from each year.

I am not sure how sample relates to the tables that you have. You can readily use a subquery or CTE to define it.

I wasn't initially thinking about it being limited to 2 years, this is a cleaner approach. — Hart CO, Jan 13 '15 at 15:29
Thank you so much for your response, although its clear as to how your obtaining the max and min dates, can you point me to where in the code it finds the duplicates? Thank you so much as I'm still a new programmer. :) — Ramatu Victorious Kuyateh, Jan 13 '15 at 18:26
@RamatuVictoriousKuyateh . . . The duplicates come from `minyear <> maxyear` -- there have to be records in both years for this filter to find any records. — Gordon Linoff, Jan 13 '15 at 19:53

Hart CO · Answer 2 · 2015-01-13T15:27:54.223

Ignoring your query and focusing on your sample set to simplify a bit, you can get your desired output using a couple steps with ROW_NUMBER() and a COUNT() with OVER():

;with cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY Bill_Member_ID,YEAR(Order_Date) ORDER BY Order_Date DESC) AS RN
              FROM YourTable
              WHERE YEAR(Order_Date) IN (2013,2014)
             )
     ,cte2 AS (SELECT *,COUNT() OVER(PARTITION BY Bill_Member_ID) AS Yr_CT
               FROM cte
               WHERE RN = 1
              )
SELECT Bill_Member_ID,Lname,Order_Date 
FROM cte2
WHERE Yr_CT > 1
ORDER BY Bill_Member_ID,Order_Date

The ROW_NUMBER() function adds a number to each record, starting at 1 per group defined in the PARTITION BY (optional) and ordered according to the ORDER BY (required).

Adding OVER() to COUNT() allows you to get a count by grouping without losing detail as you would with GROUP BY.

So the first cte adds a number useful for filtering to the latest Order_Date per year for each Bill_Memmber_ID, the 2nd adds a count of how many years each Bill_Member_ID has records in.

Update: I wasn't initially thinking about it just being limited to 2 years, and considered any multiple year situation as being the desired output. So Gordon's approach is simpler given the only 2 year requirement, if you need to consider where someone had orders in some other number of years you can use this answer.

Thank you for your comment, this is great in helping me get the counts for 2013 and 2014, but as a new programmer, can you point me to where in the code it finds the duplicates? — Ramatu Victorious Kuyateh, Jan 13 '15 at 18:24
@RamatuVictoriousKuyateh In this situation, the first cte is numbering "duplicates" from 1-n for each `year`/`bill_member_id`, if you `SELECT * FROM cte` you'll see the `RN` values for duplicates, and we use that value to filter out duplicates `WHERE RN = 1`. The subsequent `cte` takes the one record per `year`/`bill_member_id` and gets a count of how many years that `bill_member_id` has records for. — Hart CO, Jan 13 '15 at 18:57

score 0 · Answer 3 · answered Jan 13 '15 at 15:33

select s1.id, s1.name, s1.orderDate as [date 2013], s2.orderDate as [date 2014] 
from 
(  select id, name, max(orderDate) as [orderDate]
     from cusOrder 
    where DatePart(yy, orderDate) = '2013' 
    group by id, name) as s1 
join 
(  select id, name, max(orderDate) as [orderDate]
     from cusOrder 
    where DatePart(yy, orderDate) = '2014' 
    group by id, name) as s2  
on s1.id = s2.id

score 0 · Answer 4 · answered Feb 11 '15 at 05:22

0

You could also try temp tables or CTE comparisons.

answered Feb 11 '15 at 05:22

SRahmani

348
1
5
17

Duplicate Records in SQl Server Table Different year

4 Answers4