September 1, 2010 at 5:00 pm
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
September 1, 2010 at 5:22 pm
How about:
select a.vndid, a.vndname, a.invdate, a.invamt, b.invamt
from #test a inner join #test b
on a.vndname = b.vndname and
a.invdate = b.invdate and
(a.invamt - b.invamt) between 5.0 and 30.0
September 1, 2010 at 9:35 pm
Hi there,
I would just like to ask one thing.. What would happen if there's additional record like this one:
insert into test values (9,'macy','04-06-2006','660.0')
a. The difference between invamt of vndid 7 and 8 is 5.0
b. The difference between invamt of vndid 8 and 9 is 30.0
c. The difference between invamt of vndid 7 and 9 is 35.0
Would macy still be displayed?
If not (because of option c), you may try this one..
;WITH cte AS
(
SELECT vndid,vndname,invdate,invamt
, MIN(invamt) OVER(PARTITION BY vndname,invdate) AS min_invamt
, MAX(invamt) OVER(PARTITION BY vndname,invdate) AS max_invamt
, COUNT(invamt) OVER(PARTITION BY vndname,invdate) AS count_invamt
FROM test
)
SELECT DISTINCT vndname,invdate,count_invamt
FROM cte
WHERE max_invamt-min_invamt BETWEEN 5.0 AND 30.0
I hope this helps.. 🙂
September 1, 2010 at 10:08 pm
Another option using EXISTS:
SELECT *
FROM dbo.Test T1
WHERE EXISTS
(
SELECT *
FROM dbo.Test T2
WHERE T2.vndname = T1.vndname
AND T2.invdate = T1.invdate
AND ABS(T2.invamt - T1.invamt) BETWEEN 5.0 AND 30.0
);
If you need a count, include COUNT_BIG(*) OVER (PARTITION BY T1.vndname, T1.invdate) as an extra column in the final SELECT. The ABS function can be removed if you just want to see one of the records involved.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 1, 2010 at 11:12 pm
Hello shield_21
Thank you for your reply and time; but it is only showing the output for walmart and doesnt show macys in the output after inserting the row as you suggested.
September 2, 2010 at 1:41 am
Hi there,
It was not displayed because the difference became 35 i.e. 660 (max) - 625 (min).. If you would still like to display macy, here's the modification:
;WITH cte AS
(
SELECT vndname,invdate,invamt,COUNT(invamt) OVER(PARTITION BY vndname,invdate) AS value
FROM test
)
SELECT DISTINCT c.vndname,c.invdate,c.value
FROM cte c
JOIN test t ON t.vndname=c.vndname AND t.invdate=c.invdate
WHERE t.invamt-c.invamt BETWEEN 5.0 AND 30.0
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply