question

PriyaJha-3992 avatar image
0 Votes"
PriyaJha-3992 asked PriyaJha-3992 commented

Handling missing PK-FK relations and get exact duplicate rows in output

Hi all,
I have a scenario wherein i have following 2 tables with the below provided data:

 Create table #Test
  (
  TRTime datetime,
  ID Int,
  Charge int
  )
       
  insert into #Test values('2021-06-30',1,5)
  insert into #Test values('2021-07-15',1,5)
  insert into #Test values('2021-07-15',1,5)
  insert into #Test values('2021-07-15',1,7)
  insert into #Test values('2021-07-31',1,6)
  insert into #Test values('2021-07-15',2,8)
  insert into #Test values('2021-07-31',2,6)
       
       
  create table #Status
  (
  Id int,
  Status varchar(5),
  RowStartDt datetime,
  RowEndDt datetime,
  RowIsCurrent int
  )
  insert into #Status values(1,'F','2021-01-01','2021-07-14',0)
  insert into #Status values(1,'P','2021-07-14','2021-07-15',0)
  insert into #Status values(1,'Z','2021-07-15','2021-07-15',0)
  insert into #Status values(1,'Z','2021-07-15','2021-07-16',0)
  insert into #Status values(1,'A','2021-07-16',Null,1)
  insert into #Status values(2,'Z','2021-01-01',Null,1)
   insert into #Status values(1,'Z','2021-07-15','2021-07-15',0)
  insert into #Status values(1,'X','2021-07-15','2021-07-16',0)

And this is my expected output:
128728-trperiodoutput.png

I followed this thread which i think had similar scenario
https://docs.microsoft.com/en-us/answers/questions/525262/handling-missing-pk-fk-relations-in-historical-dat.html
and tried reproducing the expected output with below query:

  Select * from 
   (
   Select *,ROW_NUMBER() OVER(PARTITION BY id,TRTime,Charge ORDER BY diff) rr  from 
   (Select t.*,s.Status,s.RowStartDt,ABS(DATEDIFF(day,t.TRTime,s.RowStartDt)) diff
   from #Test t
   Left join #Status s on t.id=s.id and t.TRTime BETWEEN RowStartDt AND ISNULL(RowEndDt,'9999-12-31'))  a) b
        
   where rr=1 and status='z'

But with this query, i am only getting one row for exact duplicate TRTime,ID and Charge.

I want a query wherein even if TRTime,ID and Charge are exact duplicates they should be retained in the output.




sql-server-generalsql-server-transact-sql
trperiodoutput.png (7.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NandanHegde-7720 avatar image
1 Vote"
NandanHegde-7720 answered PriyaJha-3992 commented

Hey,
The issue in the above scenario is the columns in the partition query are not unique as there are as is duplicate rows.
So you need to create create a column that would be unique per rows, so would suggest to use ROW_Number()

  with cte as
  (
  Select ROW_NUMBER() OVER(ORDER BY ID ASC) AS num_row,TRTime,ID,Charge from #test
  )
  Select * from 
   (
   Select *,ROW_NUMBER() OVER(PARTITION BY num_row ORDER BY diff) rr  from 
   (Select t.*,s.Status,s.RowStartDt,ABS(DATEDIFF(day,t.TRTime,s.RowStartDt)) diff
   from CTE t
   Left join #Status s on t.id=s.id and t.TRTime BETWEEN RowStartDt AND ISNULL(RowEndDt,'9999-12-31'))  a) b
    
          
   where rr=1 and status='z'


@pituach : Any thoughts for an improved query? This should work out but not sure it is a good performance aspect

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hey ,
Did any of the suggestions answer your queries?
If yes, can you please accept that as the answer as that would benefit other community members as well

0 Votes 0 ·

HI @NandanHegde-7720,

Thanks for providing your input.

0 Votes 0 ·
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered PriyaJha-3992 commented
 Select t.TRTime, t.ID, t.Charge
 From #Test t
 Where Exists (Select * From #Status s 
   Where t.ID = s.Id
      And s.Status = 'z' 
      And t.TRTime Between s.RowStartDt AND ISNULL(s.RowEndDt,'9999-12-31'))

returns the result you want.
Tom

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @TomCooper-6989 for your input.

What if we require some columns from joined table, For E.g. RowIsCurrent column from #status table.

How to handle this scenario?

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered PriyaJha-3992 commented

Hi @PriyaJha-3992

Welcome to Microsoft Q&A!

Please also refer below method which is similar to the one Tom provided.

 select * from #Test a
 where a.ID IN (SELECT ID FROM #Status b where b.Id=a.id and a.TRTime between RowStartDt and  ISNULL(RowEndDt,'9999-12-31') and status='Z')

Output:

 TRTime    ID    Charge
 2021-07-15 00:00:00.000    1    5
 2021-07-15 00:00:00.000    1    5
 2021-07-15 00:00:00.000    1    7
 2021-07-15 00:00:00.000    2    8
 2021-07-31 00:00:00.000    2    6

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @MelissaMa-msft for your input.

What if we require some columns from joined table, For E.g. RowIsCurrent column from #status table.

How to handle such scenario?

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered PriyaJha-3992 commented

Hi @PriyaJha-3992

Please refer below:

  ;with cte as (
   select * from #Test a
  where a.ID IN (SELECT ID FROM #Status b where b.Id=a.id and a.TRTime between RowStartDt and  ISNULL(RowEndDt,'9999-12-31') and status='Z'))
  select a.*,b.RowIsCurrent from cte a
  cross apply (select top 1 RowIsCurrent from #Status where id=a.id and status='Z' ) b

Output:

 TRTime    ID    Charge    RowIsCurrent
 2021-07-15 00:00:00.000    1    5    0
 2021-07-15 00:00:00.000    1    5    0
 2021-07-15 00:00:00.000    1    7    0
 2021-07-15 00:00:00.000    2    8    1
 2021-07-31 00:00:00.000    2    6    1

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @PriyaJha-3992

Could you please validate above and provide any update?

If it is still not working, please provide your expected output.

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·

Hi @MelissaMa-msft,

Thanks for providing your input.

0 Votes 0 ·