Compare two lists in the same table

[ADACProgramming]

I have a table where I have created two inventory lists. I need to compare these two lists to create a report. There will eventoually be many reports in the table.
List 1
ID ItemNum ReportNum
1 123 1
2 123 1
3 123 1
4 456 1
6 789 1

List 2
ID Item Number ReportNum
7 123 1
8 456 1
9 222 1

Results 1 Items in List 1 not in list 2
123 (2) twice
789 (1)

Results 2 Items in list 2 not in list 1
222 (1)

any Ideas on how to accomplish this.

Thanks Dwayne

 

[ADACProgramming]

Just as a side point I'm using crystal reports. Is there an easyer way to do it from there?

Dwayne

 

[maswien]

If the result you want is like:

Results 1:
123 (2)  
789  (1)

Results 2: 
222 (1)

You can achieve that in T-SQL:

create two table with value:

create table List1 (ID int,ItemNum int,ReportNum int)

insert into list1 values(1,          123,         1)
insert into list1 values(2  ,        123,         1)
insert into list1 values(3   ,       123     ,    1)
insert into list1 values(4    ,      456     ,    1)
insert into list1 values(6     ,     789     ,    1)


create table List2 (ID int,ItemNum int, ReportNum int)
insert into list2 values(7     ,      123      ,   1) 
insert into list2 values(8     ,      456      ,   1)
insert into list2 values(9     ,      222      ,   1)

for the first result:

 select t0.itemNum, 
  case when t1.cnt is not null then t0.cnt - t1.cnt 
  else t0.cnt end
 from
  (select itemNum, count(*) as cnt 
     from list1 group by itemNum ) t0
  left outer join
  (select itemNum, count(*) as cnt 
     from list2 group by itemNum ) t1
  on t0.itemNum = t1.itemNum
 where 
 (case when t1.cnt is not null 
       then t0.cnt - t1.cnt else t0.cnt end) > 0

for the second result:

select t1.itemNum, 
 case when t0.cnt is not null then t1.cnt - t0.cnt 
  else t1.cnt end
 from
  ( select itemNum, count(*) as cnt 
    from list1 group by itemNum ) t0
  right outer join
  (select itemNum, count(*) as cnt 
     from list2 group by itemNum ) t1
  on t1.itemNum = t0.itemNum
 where (case when t0.cnt is not null 
   then t1.cnt - t0.cnt else t1.cnt end) > 0

 

[vongrunt]

Wanna do it all at once, this task (inventory diff list?) is perfect for full outer join - and to some extent derived tables. Example:

select X.ReportNum, X.ItemNum, X.cnt0, X.cnt1, 
	case when X.cnt0 > X.cnt1 then X.cnt0-X.cnt1 else 0 end as ldiff,
	case when X.cnt0 < X.cnt1 then X.cnt1-X.cnt0 else 0 end as rdiff
from
(	select isnull(T0.ReportNum, T1.ReportNum) as ReportNum,
		isnull(T0.ItemNum, T1.ItemNum) as ItemNum,
		isnull(T0.cnt, 0) as cnt0,
		isnull(T1.cnt, 0) as cnt1
	from
	(	select ReportNum, ItemNum, count(*) as cnt
		from List1
		group by ReportNum, ItemNum
	) T0
	full outer join 
	(	select ReportNum, ItemNum, count(*) as cnt
		from List2
		group by ReportNum, ItemNum
	) T1
	on T0.ReportNum = T1.ReportNum and T0.ItemNum = T1.ItemNum
) X
where X.cnt0 <> X.cnt1 
--	and X.ReportNum = 1
order by X.ReportNum, X.ItemNum

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[ADACProgramming]

maswien, vongrunt
Thanks for the input. You've both written some stuff haven't seen before. I'm going to take a shot at vongrunt's code first, at least after I've poured over it a bit so I can understand it.