Hello,

Following is the table and data inserted

CREATE TABLE [dbo].[test](
[vndid] [int] NULL,
[vndname] [varchar](50) NULL,
[invdate] [datetime] NULL,
[invamt] [numeric](10, 2) NULL
) ON [PRIMARY]

GO
insert into test(vndid,vndname,invdate,invamt)
values ('1','walmart','08-29-2008','525.0'),
('2','walmart','08-29-2008','540.0'),
('3','cubs','09-15-2009','600'),
('4','cubs','09-15-2009','700'),
('5', 'target','09-25-2010','800'),
('6','walgreens','05-24-2011','755.0'),
('7','macy','04-06-2006','625.0'),
('8','macy','04-06-2006','630.0')

I need to check for amounts on same day to same vendor with a difference of few dollars only between 5 and 30

this is what I have been trying

select vndname,invdate,COUNT(*) from test where exists ( select (a.invamt-b.invamt)as value ,a.vndid,a.vndname
from test a join test b on a.vndname=b.vndname where
(a.invamt-b.invamt) between 5 and 30 )
group by vndname,invdate having COUNT(*)>1


I tried with an exists condition; but it also shows records which occurs on the same day and same vendor but the difference is more then 30$(where as I want amounts with the diff between 5 and 30)

Please let me know where am I going wrong.

Thank you

You were nearly there.

You had to compare different records (different vndid) but with the same vndname AND (date part of the) invdate.
By not using ABS (a.invamt - b.invamt), but (a.invamt - b.invamt) between 5 and 30, you filter out doubles: record 1 - record 2 = 5, record 2 - record 1 = -5,

Strictly speaking, a.vndid <> b.vndid is not needed. In this case (a.invamt - b.invamt) will be 0 and the WHERE clause (a.invamt - b.invamt) between 5 and 30 will filter them out.

But it is much clearer to the reader that you are comparing different records from the same vendor with the same date.

thats nice explanation Wim Great

Thank you Wim!!!!