SQL Duplicates Problem 2

[CJP12711]

I have a data set that looks like this:

bill# seq# line # reason
123 1 1 18
123 1 1 R1
456 1 1 18
789 1 1 R1
789 1 2 18
789 1 2 18
789 2 1 18

Each bill and seq# is unique and is considered a separate bill. Each bill can have multiple lines. I want to find a list of all duplicates, based on the reason of R1. Each bill can have multiple reasons. If a bill has a reason of R1 for all lines, then it is a true duplicate (ie bill#123). If a bill has a reason of R1 for at least 1 line, but not all, it's a partial duplicate (ie bill# 789, sequence 1). If a bill does not have R1 as a reason on any lines, it is not a duplicate (ie #456). I'm not sure how to code this to determine the 3 different categories. Im' using SQL Server 2000 - Query Analyzer. Any help is greatly appreciated!
CJ

 

[nice95gle]

Is this what you are looking for?

declare @temp table(bill# int, seq# int,  line# int,reason varchar(50))

insert into @temp values(123,1,1,'18')
insert into @temp values(123,1,1,'R1')
insert into @temp values(456,1,1,'18')
insert into @temp values(789,1,1,'R1')
insert into @temp values(789,1,2,'18')
insert into @temp values(789,1,2,'18')
insert into @temp values(789,2,1,'18')

select  a.bill#,a.seq#,a.line#,a.reason, 
case 
when count(*) > 1 then 'Full' 
Else 'Partial' 
end as Dups
from @temp a
inner join @temp b
	on a.bill#=b.bill#
	and a.seq#=b.seq#
	and a.line#=b.line#
	and a.reason = 'R1'
group by a.bill#,a.seq#,a.line#,a.reason

Well Done is better than well said
- Ben Franklin

 

[CJP12711]

Thanks for the quick response! I'm not sure that this is working correctly. It is calling the bill a full fuplicate if there are more than 1 R1 reasons on the bill, is that correct? There could be 20 R1 reasons, but if 1 single line doesn't have it, then it won't work - it's a partial. Am I reading this code correctly?

Thank you!
CJ

 

[nice95gle]

CJP, please correct if I'm wrong.
My take on your logic is as followed:

If you have a R1 in a bill that has the same bill#, seq# and line# on more that 1 line this is considered a full Duplicate.

EX:
123,1,1,18
123,1,1,R1

This would be a full dup; correct?

But the following would be a partial Dup.

123,1,2,18
123,1,1,R1

Please advice.


Well Done is better than well said
- Ben Franklin

 

[CJP12711]

Hello! No - that is not correct.

If you have a R1 in a bill that has the same bill#, seq# and line# on more that 1 line this is could be a full Duplicate. It would require having an R1 for every single line on that bill and seq #.

EX:
123,1,1,18
123,1,1,R1
123,1,2,R1

This would be a full dup because it has an R1 associated with both lines on the bill.

But the following would be a partial Dup. - Yes - this is a partial dup, because only 1 of the lines has an R1 associated with it.

123,1,2,18
123,1,1,R1

I hope that makes sense!
Thanks!
CJ

 

[maswien]

I think you messed it up.

How come the following two lines have the same bill#, seq# and line#?

123,1,1,R1
123,1,2,R1

 

[maswien]

try this:

select bill# , seq#,
case when (sum( case when reason = 'R1' then 1 else 0 end ) >=1
and (sum( case when reason = 'R1' then 1 else 0 end ) = count(*) then 'full'
case when (sum( case when reason = 'R1' then 1 else 0 end ) >=1
and (sum( case when reason = 'R1' then 1 else 0 end ) < count(*) then 'partiel'
else 'not' end
from mytable
group by bill# , seq#

 

[nice95gle]

CJP, sorry I took so long to get back to you my day just went crazy.

But I did understand what you wanted now and this should do it.

Get back to me with any questions.

BTW I added some data to test different scenarios and maswien your code wouldn't run, take a closer look at it.

[COLOR=blue]declare[/color] @temp [COLOR=blue]table[/color](bill# [COLOR=blue]int[/color], seq# [COLOR=blue]int[/color],  line# [COLOR=blue]int[/color],reason [COLOR=blue]varchar[/color](50))

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @temp [COLOR=blue]values[/color](123,1,1,[COLOR=red]'18'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @temp [COLOR=blue]values[/color](123,1,1,[COLOR=red]'R1'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @temp [COLOR=blue]values[/color](123,1,2,[COLOR=red]'R1'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @temp [COLOR=blue]values[/color](123,1,3,[COLOR=red]'20'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @temp [COLOR=blue]values[/color](456,1,1,[COLOR=red]'18'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @temp [COLOR=blue]values[/color](789,1,1,[COLOR=red]'R1'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @temp [COLOR=blue]values[/color](789,1,2,[COLOR=red]'R1'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @temp [COLOR=blue]values[/color](780,1,1,[COLOR=red]'R1'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @temp [COLOR=blue]values[/color](780,1,2,[COLOR=red]'19'[/color])


[COLOR=blue]select[/color] a.bill#,a.seq#,
	[COLOR=blue]case[/color] 
		[COLOR=blue]when[/color] a.CntBill <> b.cntReason or (a.CntBill + b.cntReason)=2 [COLOR=blue]Then[/color] [COLOR=red]'Partial'[/color]
			[COLOR=blue]else[/color] [COLOR=red]'Full'[/color] 
	[COLOR=blue]end[/color] [COLOR=blue]as[/color] Dups
[COLOR=blue]From[/color]
	([COLOR=blue]select[/color] bill#,seq#,[COLOR=#FF00FF]count[/color]([COLOR=#FF00FF]distinct[/color] bill# + seq# + line#)[COLOR=blue]as[/color] CntBill [COLOR=blue]from[/color] @temp
		[COLOR=blue]group[/color] [COLOR=blue]by[/color] bill#, seq#)a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
	([COLOR=blue]select[/color] bill#,[COLOR=#FF00FF]count[/color]([COLOR=#FF00FF]distinct[/color] [COLOR=#FF00FF]cast[/color](bill# [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](20)) + [COLOR=#FF00FF]cast[/color](line# [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](20))+ reason)[COLOR=blue]as[/color] cntReason [COLOR=blue]from[/color] @temp
		[COLOR=blue]where[/color] reason=[COLOR=red]'R1'[/color]
		[COLOR=blue]group[/color] [COLOR=blue]by[/color] bill#)b
		[COLOR=blue]on[/color] a.bill#=b.bill#

Well Done is better than well said
- Ben Franklin

 

[CJP12711]

I think this should do it! I'll test it out in the morning on my test server. Thanks so much for your help!!!

 

[nice95gle]

Thanks CJL,

I forgot to include seq# in my second query. This is just in case you have a the same bill# with a different seq#

ex:
123, 1
and
123, 2

declare @temp table(bill# int, seq# int,  line# int,reason varchar(50))

insert into @temp values(123,1,1,'18')
insert into @temp values(123,1,1,'R1')
insert into @temp values(123,1,2,'R1')
insert into @temp values(123,1,3,'20')
insert into @temp values(456,1,1,'18')
insert into @temp values(789,1,1,'R1')
insert into @temp values(789,1,2,'R1')
insert into @temp values(780,1,1,'R1')
insert into @temp values(780,1,2,'19')


select a.bill#,a.seq#,
    case 
        when a.CntBill <> b.cntReason or (a.CntBill + b.cntReason)=2 Then 'Partial'
            else 'Full' 
    end as Dups
From
    (select bill#,seq#,count(distinct bill# + seq# + line#)as CntBill from @temp
        group by bill#, seq#)a
inner join
    (select bill#,[COLOR=red]seq#[/color],count(distinct cast(bill# as varchar(20)) + cast(line# as varchar(20))+ reason)as cntReason from @temp
        where reason='R1'
        group by bill#,[COLOR=red]seq#[/color])b
        on a.bill#=b.bill#
		[COLOR=red]and a.seq#=b.seq#[/color]

Well Done is better than well said
- Ben Franklin

 

[maswien]

CJL,

Did you try my SQL?

 

[CJP12711]

Hi Maswien, yes - I did try it. Both of the scripts look like they would work for me. Thanks to both of you for your help - it's much appreciated!!!

CJ

 

[maswien]

sorry nice95gle, I didn't test my code, I tried it this morning, it should be:

select bill#, seq#, 
 case when sum( case when reason = 'R1' then 1 else 0 end ) >=1 
   and sum( case when reason = 'R1' then 1 else 0 end ) = count(*) then 'full'
    when  sum( case when reason = 'R1' then 1 else 0 end ) >=1 
      and sum (case when reason = 'R1' then 1 else 0 end ) < count(*) then 'partiel'
  else 'not' end
from mytab
group by bill#, seq#