Duplicate Records

• nscott 48570

  Grasshopper

  Points: 24

  More actions

  June 11, 2012 at 8:05 am

  #257779

  I am creating a report that lists Move Ins and Move Outs between specific dates. When testing my report, I found that the tenant_history table is returning duplicate results:

  select hmy, sevent, dtmoveout

  from tenant_history

  where htent = 55496

  and sevent = 'Move Out'

  group by hmy, sevent, dtmoveout

  results:

  178115Move Out2011-09-16 00:00:00.000

  178121Move Out2011-09-13 00:00:00.000

  The second move out result is the one I want listed on my report (hmy = 178121), how do I modify my query? I've tried max(hmy) and max(dtmoveout) but I still get the same results.

  Any and all help would be greatly appreciated!!!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 11, 2012 at 8:14 am

  #1499329

  nscott 48570 (6/11/2012)

  ---

  I am creating a report that lists Move Ins and Move Outs between specific dates. When testing my report, I found that the tenant_history table is returning duplicate results:

  select hmy, sevent, dtmoveout

  from tenant_history

  where htent = 55496

  and sevent = 'Move Out'

  group by hmy, sevent, dtmoveout

  results:

  178115Move Out2011-09-16 00:00:00.000

  178121Move Out2011-09-13 00:00:00.000

  The second move out result is the one I want listed on my report (hmy = 178121), how do I modify my query? I've tried max(hmy) and max(dtmoveout) but I still get the same results.

  Any and all help would be greatly appreciated!!!

  How are the two records above duplicates? The only thing I see being the same is the value of sevent and that they were both returned for the value of htent = 55496?

  What would help us is if you could post the DDL (CREATE TABLE statement) for the table, some sample data (a series of INSERT INTO statemnts) that represents the problem domain but isn't live data (make it up in other words), and the expected resutls based on the sample data.

• nscott 48570

  Grasshopper

  Points: 24

  More actions

  June 11, 2012 at 8:44 am

  #1499343

  Hi Lynn,

  When I run the following query:

  select hmy, dtdate, htent, sevent, dtoccurred, dtmovein, dtmoveout

  from tenant_history where htent = 55496

  I get the following results:

  1763402011-08-26 00:00:00.00055496Notice Given2011-08-26 14:32:53.0002006-03-01 00:00:00.0002011-09-26 00:00:00.000

  1763752011-08-29 00:00:00.00055496Adjust Moveout Dates2011-08-29 11:57:53.0002006-03-01 00:00:00.0002011-09-16 00:00:00.000

  1781152011-09-16 00:00:00.00055496Move Out2011-09-19 15:47:42.0002006-03-01 00:00:00.0002011-09-16 00:00:00.000

  1781162011-09-16 00:00:00.00055496Cancel Move Out2011-09-19 16:08:28.0002006-03-01 00:00:00.0002011-09-16 00:00:00.000

  1781172011-09-19 00:00:00.00055496Cancel Notice2011-09-19 16:08:34.0002006-03-01 00:00:00.000NULL

  1781182011-09-01 00:00:00.00055496Skip2011-09-19 16:08:53.0002006-03-01 00:00:00.0002011-09-13 00:00:00.000

  1781212011-09-13 00:00:00.00055496Move Out2011-09-19 16:10:18.0002006-03-01 00:00:00.0002011-09-13 00:00:00.000

  1781302011-09-13 00:00:00.00055496Deposit Change2011-09-19 16:26:53.9202006-03-01 00:00:00.0002011-09-13 00:00:00.000

  1781312011-09-13 00:00:00.00055496Deposit Accounting2011-09-19 16:26:54.0002006-03-01 00:00:00.0002011-09-13 00:00:00.000

  Every tenant has a running tenant history, this tenant had a move out processed on 09/19/11 (dtoccurred) for a move out date (dtmoveout) of 09/16/11 (hmy 178115). This must have been the wrong move out date b/c the move out was cancelled, then processed again on 09/19/11 for a move out date of 09/13/11 (hmy 178121). The tenant history table keeps the original move out record even though the second move out record is the correct record. I would like my report to pull hmy 178121; the max function doesn't seem to be the correct function to use.

  Does this help?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 11, 2012 at 8:55 am

  #1499347

  sample data extracted for useability:

  With tenant_history(hmy, dtdate, htent, sevent, dtoccurred, dtmovein, dtmoveout )

  AS

  (

  SELECT '176340','2011-08-26 00:00:00.000','55496','Notice Given ','2011-08-26 14:32:53.000','2006-03-01 00:00:00.000','2011-09-26 00:00:00.000' UNION ALL

  SELECT '176375','2011-08-29 00:00:00.000','55496','Adjust Moveout Dates','2011-08-29 11:57:53.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178115','2011-09-16 00:00:00.000','55496','Move Out ','2011-09-19 15:47:42.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178116','2011-09-16 00:00:00.000','55496','Cancel Move Out ','2011-09-19 16:08:28.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178117','2011-09-19 00:00:00.000','55496','Cancel Notice ','2011-09-19 16:08:34.000','2006-03-01 00:00:00.000','NULL' UNION ALL

  SELECT '178118','2011-09-01 00:00:00.000','55496','Skip ','2011-09-19 16:08:53.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178121','2011-09-13 00:00:00.000','55496','Move Out ','2011-09-19 16:10:18.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178130','2011-09-13 00:00:00.000','55496','Deposit Change ','2011-09-19 16:26:53.920','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178131','2011-09-13 00:00:00.000','55496','Deposit Accounting ','2011-09-19 16:26:54.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000'

  )

  select hmy, dtdate, htent, sevent, dtoccurred, dtmovein, dtmoveout

  from tenant_history where htent = 55496

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 11, 2012 at 8:58 am

  #1499348

  Possibly, but you didn't really give us what I requested. Please take the time to read the first article I reference below in my signature block. It walks you through what you need to post and how to post it so that you get the best answers possible quickly.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 11, 2012 at 9:08 am

  #1499354

  Using the formatted data from Lowell's post (thank you, Lowell), here is one way to accomplish your task:

  With tenant_history(hmy, dtdate, htent, sevent, dtoccurred, dtmovein, dtmoveout )

  AS

  (

  SELECT '176340','2011-08-26 00:00:00.000','55496','Notice Given ','2011-08-26 14:32:53.000','2006-03-01 00:00:00.000','2011-09-26 00:00:00.000' UNION ALL

  SELECT '176375','2011-08-29 00:00:00.000','55496','Adjust Moveout Dates','2011-08-29 11:57:53.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178115','2011-09-16 00:00:00.000','55496','Move Out ','2011-09-19 15:47:42.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178116','2011-09-16 00:00:00.000','55496','Cancel Move Out ','2011-09-19 16:08:28.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178117','2011-09-19 00:00:00.000','55496','Cancel Notice ','2011-09-19 16:08:34.000','2006-03-01 00:00:00.000','NULL' UNION ALL

  SELECT '178118','2011-09-01 00:00:00.000','55496','Skip ','2011-09-19 16:08:53.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178121','2011-09-13 00:00:00.000','55496','Move Out ','2011-09-19 16:10:18.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178130','2011-09-13 00:00:00.000','55496','Deposit Change ','2011-09-19 16:26:53.920','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178131','2011-09-13 00:00:00.000','55496','Deposit Accounting ','2011-09-19 16:26:54.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000'

  ), BaseData AS (

  select

  hmy,

  dtdate,

  htent,

  sevent,

  dtoccurred,

  dtmovein,

  dtmoveout,

  ROW_NUMBER() OVER (PARTITION BY htent, sevent ORDER BY dtoccurred DESC) rn

  from

  tenant_history

  where

  htent = 55496

  )

  SELECT

  hmy,

  sevent,

  dtmoveout

  FROM

  BaseData

  WHERE

  htent = 55496

  AND sevent = 'Move Out'

  AND rn = 1

  ORDER BY

  htent asc,

  hmy asc;

• nhimabindhu

  Ten Centuries

  Points: 1242

  More actions

  June 11, 2012 at 11:45 pm

  #1499590

  HI, by using the max function also we can populate the required data ......

  With tenant_history(hmy, dtdate, htent, sevent, dtoccurred, dtmovein, dtmoveout )

  AS

  (

  SELECT '176340','2011-08-26 00:00:00.000','55496','Notice Given ','2011-08-26 14:32:53.000','2006-03-01 00:00:00.000','2011-09-26 00:00:00.000' UNION ALL

  SELECT '176375','2011-08-29 00:00:00.000','55496','Adjust Moveout Dates','2011-08-29 11:57:53.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178115','2011-09-16 00:00:00.000','55496','Move Out ','2011-09-19 15:47:42.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178116','2011-09-16 00:00:00.000','55496','Cancel Move Out ','2011-09-19 16:08:28.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178117','2011-09-19 00:00:00.000','55496','Cancel Notice ','2011-09-19 16:08:34.000','2006-03-01 00:00:00.000','NULL' UNION ALL

  SELECT '178118','2011-09-01 00:00:00.000','55496','Skip ','2011-09-19 16:08:53.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178121','2011-09-13 00:00:00.000','55496','Move Out ','2011-09-19 16:10:18.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178130','2011-09-13 00:00:00.000','55496','Deposit Change ','2011-09-19 16:26:53.920','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178131','2011-09-13 00:00:00.000','55496','Deposit Accounting ','2011-09-19 16:26:54.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000'

  ), data as (

  select MAX(dtdate) as dtdate,MAX(hmy) as hmy,htent,sevent from tenant_history

  where htent = 55496 and sevent = 'Move Out'

  group by htent,sevent

  )

  select hmy,sevent,htent from data

  🙂

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 12, 2012 at 3:40 am

  #1499671

  Lynn Pettis (6/11/2012)

  ---

  Using the formatted data from Lowell's post (thank you, Lowell), here is one way to accomplish your task:

  With tenant_history(hmy, dtdate, htent, sevent, dtoccurred, dtmovein, dtmoveout )

  AS

  (

  SELECT '176340','2011-08-26 00:00:00.000','55496','Notice Given ','2011-08-26 14:32:53.000','2006-03-01 00:00:00.000','2011-09-26 00:00:00.000' UNION ALL

  SELECT '176375','2011-08-29 00:00:00.000','55496','Adjust Moveout Dates','2011-08-29 11:57:53.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178115','2011-09-16 00:00:00.000','55496','Move Out ','2011-09-19 15:47:42.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178116','2011-09-16 00:00:00.000','55496','Cancel Move Out ','2011-09-19 16:08:28.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178117','2011-09-19 00:00:00.000','55496','Cancel Notice ','2011-09-19 16:08:34.000','2006-03-01 00:00:00.000','NULL' UNION ALL

  SELECT '178118','2011-09-01 00:00:00.000','55496','Skip ','2011-09-19 16:08:53.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178121','2011-09-13 00:00:00.000','55496','Move Out ','2011-09-19 16:10:18.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178130','2011-09-13 00:00:00.000','55496','Deposit Change ','2011-09-19 16:26:53.920','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178131','2011-09-13 00:00:00.000','55496','Deposit Accounting ','2011-09-19 16:26:54.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000'

  ), BaseData AS (

  select

  hmy,

  dtdate,

  htent,

  sevent,

  dtoccurred,

  dtmovein,

  dtmoveout,

  ROW_NUMBER() OVER (PARTITION BY htent, sevent ORDER BY dtoccurred DESC) rn

  from

  tenant_history

  where

  htent = 55496

  )

  SELECT

  hmy,

  sevent,

  dtmoveout

  FROM

  BaseData

  WHERE

  htent = 55496

  AND sevent = 'Move Out'

  AND rn = 1

  ORDER BY

  htent asc,

  hmy asc;

  Here's another way which explicitly excludes cancelled moves:

  With tenant_history(hmy, dtdate, htent, sevent, dtoccurred, dtmovein, dtmoveout )

  AS

  (

  SELECT '176340','2011-08-26 00:00:00.000','55496','Notice Given ','2011-08-26 14:32:53.000','2006-03-01 00:00:00.000','2011-09-26 00:00:00.000' UNION ALL

  SELECT '176375','2011-08-29 00:00:00.000','55496','Adjust Moveout Dates','2011-08-29 11:57:53.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178115','2011-09-16 00:00:00.000','55496','Move Out ','2011-09-19 15:47:42.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178116','2011-09-16 00:00:00.000','55496','Cancel Move Out ','2011-09-19 16:08:28.000','2006-03-01 00:00:00.000','2011-09-16 00:00:00.000' UNION ALL

  SELECT '178117','2011-09-19 00:00:00.000','55496','Cancel Notice ','2011-09-19 16:08:34.000','2006-03-01 00:00:00.000','NULL' UNION ALL

  SELECT '178118','2011-09-01 00:00:00.000','55496','Skip ','2011-09-19 16:08:53.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178121','2011-09-13 00:00:00.000','55496','Move Out ','2011-09-19 16:10:18.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178130','2011-09-13 00:00:00.000','55496','Deposit Change ','2011-09-19 16:26:53.920','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000' UNION ALL

  SELECT '178131','2011-09-13 00:00:00.000','55496','Deposit Accounting ','2011-09-19 16:26:54.000','2006-03-01 00:00:00.000','2011-09-13 00:00:00.000'

  )

  -- Exclude any cancelled moves

  SELECT a.hmy, a.dtdate, a.htent, a.sevent, a.dtoccurred, a.dtmovein, a.dtmoveout, b.*

  FROM tenant_history a

  LEFT JOIN tenant_history b

  ON b.htent = a.htent AND b.sevent = 'Cancel Move Out '

  AND b.dtmoveout = a.dtdate

  WHERE a.htent = 55496

  AND a.sevent = 'Move Out '

  AND b.htent IS NULL

  ORDER BY a.dtdate DESC

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden