Subtract records from same column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subtract records from same column

Author

Topic

tammy2512
Starting Member

8 Posts

Posted - 2010-09-01 : 18:56:15

Hello,

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,

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-01 : 20:31:06

Based on your sample data, having only 2 for each group, if so, using max, and min ... Run this to see if it works for you.

declare @temp TABLE(
[vndid] [int] NULL,
[vndname] [varchar](50) NULL,
[invdate] [datetime] NULL,
[invamt] [numeric](10, 2) NULL
)

insert into @temp(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')
select * from @temp

select * from (select vndname, (max(invamt) - min(invamt)) as dif from @temp group by vndname, invdate) as temp
where dif between 5 and 30 order by temp.vndname

-- result
vndname dif
macy 5.00
walmart 15.00

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-09-01 : 20:49:21

What if more than two transaction in same day?

declare @test TABLE (
[vndid] [int] NULL,
[vndname] [varchar](50) NULL,
[invdate] [datetime] NULL,
[invamt] [numeric](10, 2) NULL
)

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'),
-- what if?
('9','macy','04-06-2006','635.0')

select	* 
from	@test a 
join	@test b on
	a.vndname = b.vndname and
	cast(a.invdate as date) = cast(b.invdate as date) and
	a.invamt < b.invamt
where	(b.invamt-a.invamt) between 5 and 30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources